financial modeling

Business Finance

George Washington University

Question Description

Problems are in the Excel. Total 8 questions

1. After taking many finance classes and landing a very good job, you made a decision to be a home owner. The question you have is how much you can afford to borrow for a home right away if you can make annual payments of $52,900 per year for 30 years at 3%? What about for a monthly payment of $4,490? Which option would you choose: the annual or the monthly payments? Why? Show your formulas in the orange cells next to the calculation.

......

Unformatted Attachment Preview

Question 2: (3 points) After taking many finance classes and landing a very good job, you made a decision to be a home owner. The question you h home right away if you can make annual payments of $52,900 per year for 30 years at 3%? What about for a monthly paym choose: the annual or the monthly payments? Why? Show your formulas in the orange cells next to the calculation. You also made a decision to purchase a car. What would your monthly payments be for a $40,000 car loan over five years a e a home owner. The question you have is how much you can afford to borrow for a 3%? What about for a monthly payment of $4,490? Which option would you cells next to the calculation. a $40,000 car loan over five years at 1.99%? A 1 Discount rate 2 Net present value 3 4 Date 5 2-May-19 6 14-Feb-20 7 14-Feb-21 8 14-Feb-22 9 14-Feb-23 10 14-Feb-24 11 14-Feb-25 B 15.00% Cash flow -600 100 400 200 800 800 -1,800 C D E F G H I J K L Question 2: (5 points) Consider a project that costs $600 today. The project is expected to generate the following cash flows as listed in the table Calculate the IRR and NPV. Assuming a discount rate of 15%, would you invest in this project ? Why? Justify your answers. Build a chart of the NPV as a function of different discount rates Show the formulas of the calculations in the adjacent cell M A B C D E F G H I J K L M N O 1 Question 3: (5 points) 2 3 Using Thomson Reuters' Eikon or Bloomberg, explain in words the process of finding the five yr US Treasury Note issue maturing in October 2020 and show the SCREENSHOTS of all steps involved ( Thomson Reuters'Eikon/Security/…). 4 Attach the screenshots of the data below. 5 1. What are the symbol and CUSIP? 2. What is the maturity and Yield to Maturity at Issuance 6 7 8 9 3. What is the Issue Price? Convert the issue price in a price with 4 decimals showing your calculations. 10 11 4. 12 13 14 What is the issue amount and Coupon interest rate? 5. Attach a screenshot from Thomson Reuters' Eikon or Bloomberg of the daily price chart of this Treasury Bond issue starting from the issue date to the present. 15 6. What are the Macaulay Duration and Modified Duration on the trade date May 1, 2020 and explain their interpretation? 7. Have these bonds been stripped? How do you know that? 16 17 18 19 20 8. What is the clean price in decimal points on trade date May 1, 2020? 9. How about the dirty price in decimal points? What is it and How is the dirty price calculated? 21 22 23 24 10. Show your calculations for how much would you have to pay for 5,000 face value of this note on May 1 , 2020? And What kind of bond type is it? P Q4 A B C 1 Question 4: (5 points) 2 Given the settlement date of May 4, 2020, use the info from previous problem to calculate on this spreadsheet (not a 3 and show the formulas in the orange cells. 4 Settlement date 5 Maturity date 6 YTM 7 Coupon 8 Face value 9 Frequency 10 100 2 11 Modified Duration 12 13 What is the interpretation of the modified duration? 14 15 16 17 18 Page 6 Q4 D E F G H 1 2020, use the2info from previous problem to calculate on this spreadsheet (not a separate sheet) the bond price, duration, convexity i 3 4 Bond Price 5 6 Convexity 7 8 9 Use Excel's Data Table to run a sensitivity analysis of the duration as a function of the coupon rate 10 (coupon rate=0%,1%,2%,3%,4%,5%,6%,7%,9%,13%,15%,17%)and comment on the effect of coupon on the duration. 11 12 Build a chart of the duration as a function of the coupon rate. odified duration? 13 What is the interpretation of the c 14 15 16 17 18 Page 7 Q4 I J K L M N O P Q 1 2 nd price, duration, convexity in the specified cells (F4, F7, F12 for the US Treasury Note from the Question 3 3 4 Formula 5 6 Formula 7 8 9 t of coupon on 10the duration. 11 12 at is the interpretation 13 of the convexity? 14 15 16 17 18 Page 8 A B C D E F G H I J K L M N O P Q R S NAME: 2 5. The current (year 0) level of sales is $2,000. The firm is expecting its sales to grow at a rate of 15% per year, and it anticipates the financial statements relations given in the table. 3. Using the cash & mkt. securities as the Plug ( Cash & Mkt Securities = Total Liabilities and Equity - Current Assets - Net Fixed Assets) predict the financial statements for the next 5 years 3 1 (year 2020 through 2024). If the firm does not have enough internally generated funds, then only in that situation, it can borrrow money. 4 5 Given a WACC of 10% and a Long Term growth rate in FCFs of 4.5% , determine the enterprise value and equity value of the firm. (5 points) 6 7 Show the formulas the section in orange referencing the cells on column G (G26 through G 70) and column B (B71 through B 75) 8 Make the calculations in the turquoise cells. 9 Using the Excel data table, run 2 sensitivity analyses to show the effect on equity value of a change in SGA and the effect on equity value of a simultaneous change in COGS and Sales growth rate. 10 Build a chart of the Equity value as a function of the SG&A. 11 12 13 14 15 16 17 18 19 20 21 22 Sales growth Current assets/Sales Current liabilities/Sales Net fixed assets/Sales Costs of goods sold/Sales Sales, general and administrative expenses Depreciation rate Interest rate on debt Interest paid on cash & marketable securities Tax rate Dividend payout ratio 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Year Income statement Sales Costs of goods sold SG&A Interest payments on debt Interest earned on cash & marketable securities Depreciation Profit before tax Taxes Profit after tax Dividends Retained earnings 38 39 40 41 42 43 44 45 46 47 48 49 50 51 15% 15% 8% 70% 65% 110 10% 5.00% 3.00% 21% 30% 2019 2,000 (1,300) (51) 2 (100) 551 (116) 436 (131) 305 Balance sheet Cash and marketable securities Current assets Fixed assets At cost Depreciation Net fixed assets Total assets 1,700 (300) 1,400 1,780 Current liabilities Debt Stock Accumulated retained earnings Total liabilities and equity 160 1,020 450 150 1,780 80 300 The firm neither repays any existing debt nor borrows any more money over the 5 yr horizon of the pro formas Debt is assumed unchanged. Stock does not change either ( no new stock issuances and no stock repurchases). However, if the firm does not have enough cash it can increase its debt Cash and Mkt Securities is the PLUG. The average balances of cash and marketable securities are assumed to earn 3% interest Depreciation = Depreciation rate * Average fixed assets at cost over the year Interest gain on cash & equivalents = Interest rate on cash & equivalents * Average amount over the year Interest payments on debt = Interest rate on debt * Average debt over the year It is important to use the appropriate the formulas. 2020 2,300 (1,495) 2021 2,645 2022 3,042 2023 3,498 2024 4,023 T A B C D E F G 2019 2020 2021 2022 2023 2024 H 52 53 54 Year 55 Free cash flow calculation 56 Profit after tax 57 58 59 60 61 62 63 Add back depreciation Subtract increase in current assets Add back increase in current liabilities Subtract increase in fixed assets at cost Add back after-tax interest on debt Subtract after-tax interest on cash & mkt. securities Free cash flow 0 0 0 0 0 64 65 66 Valuing the firm 67 Weighted average cost of capital 68 Long term FCF Growth Rate 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 10% 4.5% 2019 Year FCF Terminal value Total 2020 2021 NPV of Total FCFs Add in initial (year 0) cash and mkt. securities Enterprise value Subtract out value of firm's debt today Equity value Data table: Value as function of SG&A 0 50 100 150 200 250 300 350 400 450 500 550 600 Sales Groth Rate Data table: Equity Value as function of COGS and sales growth COGS 25.00% 35.00% 45.00% 55.00% 65.00% 75.00% 85.00% 95.00% 2022 2023 2024 I J K L M N O P Q R S T U 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 V Question 6: (5 points) The CEO of ICG Inc. announced that the company will pay an annual dividend of $1.00 per year, one year from today. It is estimated that during the following six years, the dividend will grow at an annual rate of 7% After that, the growth rate will be equal to 4% per year and continue at that rate indefinitely. Calculate the intrinsic value of Would you invest in this stock? Why? ely. Calculate the intrinsic value of the ICG's stock if the required rate of return is 6.7%. A 1 2 3 4 5 B C D E F G H I J K L M N O P Q R S Question 7: (5 points) A steel company just paid $25 million for a feasibility study. If the company goes ahead with the project, it must immediately spend another $110 million now, and then spend $75 million in one year. In two years it will receive $180 million, and in three years it will receive $50 million. If the cost of capital for the project is 11 percent, what are the project’s NPV and IRR? Would you invest in this project? Why? T Question 8: (4 points) ICG is thinking of launching a new product. The objective in ICG is to examine the robustness of decisions based on Net Prese The management team is trying to decide whether or not to launch a new product which is expected to have a market for the i) The expected market size is 8,000,000 units in the first year with 2% growth per year thereafter. ii) A 15% market share is anticipated in the first year, growing linearly by 0.3% per year afterwards. iii) In the first year, the price is likely to be $7 growing by 6% annually thereafter. iv) The expected variable cost per unit is $5 increasing by 3% per year. Fixed costs are expected to start at $2,000,000 v) An initial investment of $2,500,000 is planned. A discount rate of 15% is usually taken in calculating the Net Presen These figures represent the `single figure estimates' for the product's future prospects in a growing market. This combination It can be assumed that the estimates for initial market size and market share are firm, but there is uncertainty about market s Introducing uncertainty about Market Growth & Market Share Increase Firstly, market growth is most likely to be a 2% increase, but could range from a 10% decrease to an 8% increase following a r Secondly, market share increase can be assumed to be uniformly distributed between 0.2% (shrinkage) and 0.8% (in contras Armed with these assessed distributions, Choose the following Simulation Settings: Iterations: 5,000 Simulations: 1 Sampling Type: Hypercube Simulation not Running, Distributions return=Static Values then When RiskStatic is not defined use Expected Values Generator: Marsenne Twister Initial seed: 1 All use same seed. Collect distribution samples: All Smart sensitivity analysis: Enabled First when you open this spreadsheet, save it under your Last Name-Question8.xls then start solving the problem 1. In this workbook please generate and attach the following @Risk Excel Reports: Quick Report, Input and Output Results 2. Show the Cumulative Ascending Cumulative - Line. What is the probability that the project will make its hurdle rate? 3. Show the Output Histogram for NPV and IRR What is the probability of IRR being less than 15%. 4. What are the key drivers of the project's success and what is their interpretation (Screenshots)? What @Risk function d 5. Show the sensitivity analysis screenshot and interpret the R squared. 6. Would you lunch this product or not? Why? g market. This combination of estimates is referred to as the base case. To measure the feasability of this project, the management firm is uncertainty about market share increase and market growth. Optimistic, pessimistic and base case (most likely values) values for these pa n 8% increase following a roughly triangular distribution. (A triangular distribution is one for which the probability density function takes a kage) and 0.8% (in contrast to the single figure 0.3% assumed before). This means that growth is equally likely to take any value in the ab ct, the management firm is using the NPV and IRR methodologies. A simple spreadsheet model can be quickly built, (see spreadsheet) with values) values for these parameters are set out in cells E17..G18 (see spreadsheet). These can be used to determine the worst and best v mine the worst and best values for the NPV of the proposal (see cells E19..G19). However, it is extremely unlikely that all the worse (or be ely that all the worse (or best) circumstances will occur together. ICG SPREADSHEET FOR FIVE YEARS (cashflow in thousands) 1 2 3 MARKET SIZE 8,000 8,160 8,323 PRICE 7.0 VARIABLE COST 5.0 SALES (MS) 1,200 NET REVENUE 2,400 FIXED COSTS -2000 CASHFLOW -2,500.00 400 ASSUMPTIONS Discount Rate Prod Cost Price Market Share MS Incr MktGrowth 15% 5 7 15% 0.3% 102.0% 4 8,490 5 8,659 RESULTS NPV IRR BaseCase Pessimistic Most Likely Optimistic MS Incr -0.2% 0.3% 0.8% MktGrowth 90.0% 102.0% 108.0% NPV IRR ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags