Multiples and Transactions, Dividend Discount Model
Order ID |
53563633773 |
Type |
Essay |
Writer Level |
Masters |
Style |
APA |
Sources/References |
4 |
Perfect Number of Pages to Order |
5-10 Pages |
Description/Paper Instructions
FINACE Excel Problem Set
Multiples and Transactions, Dividend Discount Model
Each week contain a Problem Set with questions designed to measure mastery of the content of the module. Each Problem Set has two (2) documents: Problem Set (questions) and Excel Template. Excel is the industry standard for finance work and is required to complete and submit each Problem Set. The provided Excel Template may be used or you may create an original spreadsheet. If you choose to use the Excel template provided, download it for better usability. Every question should have its own tab. Show all your formulas as this allows the instructor to better assess how to help you and award partial credit as deems appropriate.
Week 1 – Introductions, Financial Statements, and Ratios
This problem set uses Alaska Air Group, Inc. as the basis for ALL questions. To start, go to the airline’s investor relations page and get its latest annual report (10-K).
1. Using the firm’s selected financial data:
a. Make a table showing the income statement (in million $). Note that any expenses that do not have their own specific line listed should be grouped under “All other expenses”. The net income lines must tie using formulas.
b. Use the table created to create a common size income statement (as a % of operating revenues for that year).
c. Show year-on-year (YoY, in %) growth
d. What happened to the profit margins?
e. What are the actual tax rates?
2. Using the operating stats, what is the YoY growth rate for: a. Yield?
b. Operating expense per available seat mile (CASM)? c. Revenue passenger miles (RPMs)?
d. Fuel price ($/gallon)?
e. Considering the above, what factors, other than the pandemic, do you think are affecting margins the most? Be specific.
3. Using the firm’s balance sheets, show:
a. Balance sheets as shown (in million $); show only net property & equipment lines.
b. Common size balance sheets.
c. Are there any outliers in the balance sheets that you think merit mentioning?
d. For these years, what is the net working capital position? Current ratio? Quick ratio? Total debt ratio? Return on assets (ROA)? Return on equity (ROE)? What is the latest p/e ratio (use Yahoo Finance)?
4. Using the Statement of Cash Flows, which items stand out to you and why?
5. Go to the carrier snapshot section in the Bureau of Transportation Statistics of the U.S. Department of Transportation: https://www.transtats.bts.gov/carriers.asp?20=E
a. What are the top three markets by share? b. What happened to passenger yields?
c. What happened to costs per ASM?
d. What do you think this means for the bottom line? e. What are the top three city pairs?
Week 2 – Discounted Cash Flow Valuations and Net Present Value
Remember that when you use the perpetuity formula, the formula automatically present values the perpetuity one period, so if you use the perpetuity formula in year 15, the result is as of the beginning of year 15!
1. You are offered three annuities (these make equal payments over a specific period). Using an annual 5.5% discount rate, calculate each annuity’s price:
2. You purchase a new machine for your firm. It costs $84,000 and will expand your cash flow by $13,500/year in year 1 growing by 2.5% per year after that. The system will work for 30 years before you have to replace it. What are the NPV (at a 5.5% discount rate) and IRR?
The vendor offers you another machine costing $99,000 and lasting 35 years, with the same yearly cash flow and growth rate. What are the NPV and the IRR for it? Should you get it?
3. Read: https://www.espn.com/mlb/story/_/id/16650867/why-mets-pay-bobby-bonilla-119-million-today-every-july-1-2035
a. Use July 1, 2011, to calculate the future value of the $5.9M owed on July 1, 2000.
b. Use July 1, 2011, to calculate the present value of the 25 yearly payments of
$1,193,248.20. The first payment is made on July 1, 2011
c. Should Bobby take it? Why?
4. You are looking to lease a car. The dealer offers you the following: Car price = $35,000
Monthly lease payments = $499
Down payment = $3,000
Lease term = 36 months
Purchase price at end of lease = $18,000
What is the implicit interest rate on the lease?
5. The Airbus A220 has the following R&D costs (all negative cash flows):
Each plane will be sold for 48M – 15% down and the rest due on delivery one year later. The cost to produce each plane is 38M – these costs are recognized on delivery. The Sales and Marketing Department says that you will sell 30 planes (year 5) and sale will grow by 5 planes per year before they plateau at 60 planes. The last sale is made in year 16, when the A220 is replaced by a new model.
What are the NPV (as of the beginning of year 1) and the IRR of the plane using a 9% discount rate?
Week 3 – Risk Analysis, Real Options, and Capital Budgeting
1. Real Options
A. JTM Airlines is looking at buying more gates at their home airport. JTM’s discount rate is 5.5% and the risk free rate is 2.0%. What is the NPV of the gate purchases if it bought them today? Use the data in the Excel template provided.
B. After you run the numbers for part A, you remember back to the concept of real options, which means that JTM can make investment decisions as time passes:
1. Present valuing the purchase price of the gates (that is, the years 1 and 2 Capital Expenditures) separately using the risk-free rate. Once JTM decides to go ahead with the purchase, there is no risk to that expenditure.
2. Present valuing the Net Cash Flow excluding those purchase prices. This calculation will include Cap. Ex. for years 3-15 as they are part of the normal operation of the gates and are unrelated to the purchase price.
3. Use the Black-Scholes Option Pricing formula to come up with option’s price assuming a 2-year maturity and a 15% price volatility for gate prices.
4. Compare the price of the call option with the NPV in the No Real Options scenario. Is the option worth it?
2. Decision Tree
JTM really liked your work on the option pricing of the gates, so they ask you to look at their 3- phase expansion at their home airport. The three phases are:
A. Upon purchase of the new gates, start a marketing program to promote JTM’s routes to the East Coast, West Coast, and the Caribbean. If all goes well and the market is receptive, they will go on to phase 2.
B. Phase 2 has JTM invest in new routes to the destinations listed. If at any time, JTM finds that this is not going to work, they will pull the plug on everything.
C. Phase 3 has JTM start the new routes to the destinations listed. If things don’t go well on any of the three destinations, they will pull the plug on everything.
After much work with other departments, you generate enough data to calculate the NPV of the 3-phase expansion. Before you have a chance to save all your work, there is a power spike and you lose part of your work. You have to complete it for a presentation. Please use the Excel template provided to complete this.
Week 4 – Interest Rates and Bond Valuation
1. What is the annual yield of a 6-year, 6.4% semi-annual coupon-paying bond priced today at $1,190? Par is $1,000.
2. What is the annual yield of a 9-year, 4.1% annual coupon-paying bond priced today at $1,088? Par is $1,000.
3. Show the cash flows and prices for the following four bonds, each with a par value of $1,000 and paying interest semi-annually:
Which of the four bonds would you prefer to hold and why? (Answer in the box provided.)
4. Consider a semi-annual bond with an annual coupon=6.33%, maturity=10 years, par value = $1,000, and a market price today = $1,063:
a. What is its yield to maturity (YTM)?
b. Suppose the bond can be called at $950 at the end of year8, what is
its yield to call?
5. You have two bonds with the following characteristics:
a. What are the bond durations?
b. If rates rise to 4.5%,whatarethenewpricesforeachbond?
Week 5 – Valuing an Airline for Acquisition
JTM Airlines, a privately held firm, is looking to buy additional gates at its home airport for $1,500,000. It has money in the bank, but that money may not be spent as it is used to pay salaries, suppliers, and equipment. It asked its bank for a loan, but the bank refused unless the project had a return higher than JTM’s weighed average cost of capital. Separately, PAN Airways’s CEO approached JTM’s CEO to sell the airline. As a result of all this, JTM has contracted you to:
1. Calculate JTM’s weighed average cost of capital (WACC) based on two airlines trading in the capital markets – PDM and GAL. Since JTM does not trade, it has no beta, so you need to use these two firms as proxies. JTM’s CFO kindly gave you the necessary information on PDM and GAL for you to do this.
2. Aside from the purchase price, the gates will require a working capital infusion of $1,500,000 at purchase. JTM estimates the gates will generate cash flows of $295,500 in year 1, inflating at 4%/year over the next 12 years. After that, the gates will revert back to the airport operator. Half the working capital is recovered at the end. Calculate the NPV and IRR of the gates.
3. You were given PAN’s 2020 income statement (IS) and balance sheet (BS), along with forecasts of the revenue growth. Forecast the IS and BS for the next 5 years.
4. The price discussed by the two CEOs is $6.5M. You must value PAN Airways using free cash flows to see if this price is fair or not.
Week 6 – Multiples and Transactions, Dividend Discount Model
1. JTM Airlines is looking to buy Jaguar Airlines. Your boss, the CFO, wants a quick and dirty valuation of Jaguar. You choose to look at past transactions in the airline industry to get some numbers and put them in an Excel spreadsheet. For Jaguar, you find out the firm’s key financial values and put them in the spreadsheet. To remind yourself that they are inputs, you should color them red.
Using EPS, Book Value (BV), Sales, EBITDA, Premium and Synergy over stock price, what should be Jaguar’s prices per share?
2. Your boss is piling on the work and has asked you to value three more potential acquisitions. They are of Northern, Eastern and Central, fixed base operators serving areas where JTM is looking to expand (the names give away the regions of the country). You don’t have cash flow data for these firms as they are privately held, but you talked to the owners and they gave you dividend information for the firms, which you entered into your spreadsheet. You remember back to your corporate finance class that you can use the Dividend Discount Model (DDM) to come up with a quick and dirty valuation.
You know that JTM’s WACC is 6.8% and will use this as the applicable discount rate.
Using the DDM Model, what are the values per share of each of these three firms?
Week 7 – Long Term Debt
1. A firm issues a $10 million debt obligation that pays 7.3% per year over four years. How much will it have to pay in four years?
2. Suppose that a life insurance company has guaranteed a payment of $14 million to a pension fund 4.5 years from now. If the life insurance company receives a premium of $10.4 million from the pension fund and can invest the entire premium for 4.5 years at an annual interest rate of 6.25%, will it have sufficient funds from this investment to meet the $14 million obligation?
3. A. A firm is borrowing $5,000,000 from its bank at an annual interest rate of 5.7% for the first six years and 7.2% for four years after that. How much will it pay at the end?
B. Suppose the firm in 3A can take another bank’s quote of $5,000,000 for 10 years at an annual rate of 6.1% compounded semiannually. How much would it pay at maturity? Is this investment alternative more attractive than the one in 3A?
4. Suppose a firm issues a $10 million debenture maturing in 8 years and with an annual rate of 7%. Interest is paid annually at the end of the year. How much will the firm have paid out in total as of year 8, when it pays the interest plus principal?
5. A firm’s head of HR knows that the following pension payments must be made in years 1- 4. The head of HR needs to go to management to request a lump sum that will satisfy this liability stream. Assuming the lump sum can be invested today at an interest rate of 4.7%, how much must be invested today to satisfy this liability stream?
Week 8 – Short Term Debt
1. You are in a team compiling a report to send to your firm’s bank. You are supposed to take the firm’s last three months and provide the following for each month:
Days receivable Days inventory Operating cycle Days payable Cash cycle
You have all the financials in the spreadsheet so all you need to do is make the calculations.
2. ABC Maintenance Service
A. ABC FBO sells maintenance services to various private jet operators.
For these, it demands payment within 30 days. It is considering changing this policy to 0.66%/7, net 30. What is the implicit annual rate in the new policy? Use a notional purchase of $10,000.
B. ABC’s maintenance service business grosses some $22M per year before discounts and its average days receivable is 30. If 15% of its clients opt to follow the new policy, what will be the change in receivables? If ABC’s WACC is 7.0%, what are the projected savings of the new policy? If its gross margin is 22%, by how much will gross dollar revenues have to rise to offset the loss from discounts? In percent?
RUBRIC
QUALITY OF RESPONSE |
NO RESPONSE |
POOR / UNSATISFACTORY |
SATISFACTORY |
GOOD |
EXCELLENT |
Content (worth a maximum of 50% of the total points) |
Zero points: Student failed to submit the final paper. |
20 points out of 50: The essay illustrates poor understanding of the relevant material by failing to address or incorrectly addressing the relevant content; failing to identify or inaccurately explaining/defining key concepts/ideas; ignoring or incorrectly explaining key points/claims and the reasoning behind them; and/or incorrectly or inappropriately using terminology; and elements of the response are lacking. |
30 points out of 50: The essay illustrates a rudimentary understanding of the relevant material by mentioning but not full explaining the relevant content; identifying some of the key concepts/ideas though failing to fully or accurately explain many of them; using terminology, though sometimes inaccurately or inappropriately; and/or incorporating some key claims/points but failing to explain the reasoning behind them or doing so inaccurately. Elements of the required response may also be lacking. |
40 points out of 50: The essay illustrates solid understanding of the relevant material by correctly addressing most of the relevant content; identifying and explaining most of the key concepts/ideas; using correct terminology; explaining the reasoning behind most of the key points/claims; and/or where necessary or useful, substantiating some points with accurate examples. The answer is complete. |
50 points: The essay illustrates exemplary understanding of the relevant material by thoroughly and correctly addressing the relevant content; identifying and explaining all of the key concepts/ideas; using correct terminology explaining the reasoning behind key points/claims and substantiating, as necessary/useful, points with several accurate and illuminating examples. No aspects of the required answer are missing. |
Use of Sources (worth a maximum of 20% of the total points). |
Zero points: Student failed to include citations and/or references. Or the student failed to submit a final paper. |
5 out 20 points: Sources are seldom cited to support statements and/or format of citations are not recognizable as APA 6th Edition format. There are major errors in the formation of the references and citations. And/or there is a major reliance on highly questionable. The Student fails to provide an adequate synthesis of research collected for the paper. |
10 out 20 points: References to scholarly sources are occasionally given; many statements seem unsubstantiated. Frequent errors in APA 6th Edition format, leaving the reader confused about the source of the information. There are significant errors of the formation in the references and citations. And/or there is a significant use of highly questionable sources. |
15 out 20 points: Credible Scholarly sources are used effectively support claims and are, for the most part, clear and fairly represented. APA 6th Edition is used with only a few minor errors. There are minor errors in reference and/or citations. And/or there is some use of questionable sources. |
20 points: Credible scholarly sources are used to give compelling evidence to support claims and are clearly and fairly represented. APA 6th Edition format is used accurately and consistently. The student uses above the maximum required references in the development of the assignment. |
Grammar (worth maximum of 20% of total points) |
Zero points: Student failed to submit the final paper. |
5 points out of 20: The paper does not communicate ideas/points clearly due to inappropriate use of terminology and vague language; thoughts and sentences are disjointed or incomprehensible; organization lacking; and/or numerous grammatical, spelling/punctuation errors |
10 points out 20: The paper is often unclear and difficult to follow due to some inappropriate terminology and/or vague language; ideas may be fragmented, wandering and/or repetitive; poor organization; and/or some grammatical, spelling, punctuation errors |
15 points out of 20: The paper is mostly clear as a result of appropriate use of terminology and minimal vagueness; no tangents and no repetition; fairly good organization; almost perfect grammar, spelling, punctuation, and word usage. |
20 points: The paper is clear, concise, and a pleasure to read as a result of appropriate and precise use of terminology; total coherence of thoughts and presentation and logical organization; and the essay is error free. |
Structure of the Paper (worth 10% of total points) |
Zero points: Student failed to submit the final paper. |
3 points out of 10: Student needs to develop better formatting skills. The paper omits significant structural elements required for and APA 6th edition paper. Formatting of the paper has major flaws. The paper does not conform to APA 6th edition requirements whatsoever. |
5 points out of 10: Appearance of final paper demonstrates the student’s limited ability to format the paper. There are significant errors in formatting and/or the total omission of major components of an APA 6th edition paper. They can include the omission of the cover page, abstract, and page numbers. Additionally the page has major formatting issues with spacing or paragraph formation. Font size might not conform to size requirements. The student also significantly writes too large or too short of and paper |
7 points out of 10: Research paper presents an above-average use of formatting skills. The paper has slight errors within the paper. This can include small errors or omissions with the cover page, abstract, page number, and headers. There could be also slight formatting issues with the document spacing or the font Additionally the paper might slightly exceed or undershoot the specific number of required written pages for the assignment. |
10 points: Student provides a high-caliber, formatted paper. This includes an APA 6th edition cover page, abstract, page number, headers and is double spaced in 12’ Times Roman Font. Additionally, the paper conforms to the specific number of required written pages and neither goes over or under the specified length of the paper. |
|
|
GET THIS PROJECT NOW BY CLICKING ON THIS LINK TO PLACE THE ORDER
Also, you can place the order at www.collegepaper.us/orders/ordernow / www.phdwriters.us/orders/ordernow
|
Do You Have Any Other Essay/Assignment/Class Project/Homework Related to this? Click Here Now [CLICK ME]and Have It Done by Our PhD Qualified Writers!! |
|
|
PLACE THE ORDER WITH US TODAY AND GET A PERFECT SCORE!!!