Order ID:89JHGSJE83839  Style:APA/MLA/Harvard/Chicago  Pages:510 
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 (10K).
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 yearonyear (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/whymetspaybobbybonilla119milliontodayeveryjuly12035
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 riskfree 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 315 as they are part of the normal operation of the gates and are unrelated to the purchase price.
3. Use the BlackScholes Option Pricing formula to come up with option’s price assuming a 2year 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 3phase 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 6year, 6.4% semiannual couponpaying bond priced today at $1,190? Par is $1,000.
2. What is the annual yield of a 9year, 4.1% annual couponpaying 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 semiannually:
Which of the four bonds would you prefer to hold and why? (Answer in the box provided.)
4. Consider a semiannual 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 

Excellent Quality 95100%

Introduction
4541 points The background and significance of the problem and a clear statement of the research purpose is provided. The search history is mentioned. 
Literature Support 9184 points The background and significance of the problem and a clear statement of the research purpose is provided. The search history is mentioned. 
Methodology 5853 points Content is wellorganized with headings for each slide and bulleted lists to group related material as needed. Use of font, color, graphics, effects, etc. to enhance readability and presentation content is excellent. Length requirements of 10 slides/pages or less is met. 

Average Score 5085% 
4038 points More depth/detail for the background and significance is needed, or the research detail is not clear. No search history information is provided. 
8376 points Review of relevant theoretical literature is evident, but there is little integration of studies into concepts related to problem. Review is partially focused and organized. Supporting and opposing research are included. Summary of information presented is included. Conclusion may not contain a biblical integration. 
5249 points Content is somewhat organized, but no structure is apparent. The use of font, color, graphics, effects, etc. is occasionally detracting to the presentation content. Length requirements may not be met. 

Poor Quality 045% 
371 points The background and/or significance are missing. No search history information is provided. 
751 points Review of relevant theoretical literature is evident, but there is no integration of studies into concepts related to problem. Review is partially focused and organized. Supporting and opposing research are not included in the summary of information presented. Conclusion does not contain a biblical integration. 
481 points There is no clear or logical organizational structure. No logical sequence is apparent. The use of font, color, graphics, effects etc. is often detracting to the presentation content. Length requirements may not be met 

You Can Also Place the Order at www.collegepaper.us/orders/ordernow or www.crucialessay.com/orders/ordernow 
Multiples and Transactions, Dividend Discount Model