Question 3. What rate of increase is needed from year 1 throughyear 8 to have a year 8 gross margin with a present value that is equal to the present value of the year 1 gross margin? Hint: Use goal seek.
Answer:
ISDS 710 Excel Model 6 - Time Value of Money (Net Present Value or NPV) (1) - Excel File Home Insert Page Layout Formulas Data Review View Smart View Tell me what you want to do... do Cut Calibri - 11 - A" A" Wrap Text General EX _ AutoSum Be Copy Fill Paste BI U - - DA- Conditional Format as Cell Format Painter = Merge & Center . $ - % " Insert Delete Format Formatting ~ Table - Styles Clear - Clipboard Font Alignment Number Styles Cells Editi Gross ma... X V B D G H M O P Q R Inputs Calculating NPV at Acron Development cost (millions) 3 Gross margin year 1 (millions) Acron is a large drug company. At the current time, the beginning of year 0, Acron is trying to decide whether one 4 Peak year for gross margin of its new drugs, Niagra, is worth pursuing. Niagra is in the final stages of development and will be ready to enter 5 Rate of increase (through peak year the market one year from now. 6 Rate of decrease (after peak year) 7 Discount rate The final cost of development, to be incurred at the beginning of year 1, is $15 million. Acron estimates that the demand for Niagra will gradually grow and then decline over its useful lifetime of 20 years. Specifically, the company exprects its gross margin (revenue minus cost) to be $1.5 million in year 1, then to increase at an annual rate of 6% through year 8, and finally to decrease at an annual rate of 5% through year 20. Cash flows Acron wants to develop a spreadsheet model of its 20-year cash flows, assuming its cash flows, other than the End of year Gross margin initial development cost, are incurred at the end of the respective years. Using an annual discount rate of 7.5% for the purpose of calculating NPV, the drug company wants to answer the following questions: 1. Is the drug worth pursuing, or should Acron abandon it now and not incur the $15 million development cost? 2. How do changes in the model inputs change the answer to question 1? Decision Involving the Time Value of Money In many business situations, cash flows are received at different points in time, and a company must determine a course of action that maximizes the "value" of cash flows. Some examples: Should a company buy a more expensive machine that lasts for 10 years or a less expensive machine that lasts for 5 years? - What level of plant capacity is best for the next 20 years? 15 Money earned in the future is less valuable than money earned today, for the simple reason that money 16 earned today can be invested to earn returns. Similarly, the costs incurred in the future are less "costly" than costs incurred today. This is why you typically don't simply sum up revenues and costs in a multi- period mandel. Instead wou discount future revenues and costs for a fair comparison with revenues and Sheet1 + Ready O Type here to search 55 F Mostly clear DELLISDS 710 Excel Model 6 - Time Value of Money (Net Present Value or NPV) (1) - Excel File Home Insert Page Layout Formulas Data Review View Smart View ? Tell me what you want to do.. do Cut Calibri 11 - A" A" Fe Wrap Text General AutoSum IN Be Copy Fill - Paste Format Painter B I U - 2- A - Merge & Center - Conditional Format as Cell Insert Delete Format Sort & Formatting ~ Table - Styles Clear Filter Clipboard L Font Alignment Number Styles Cells Editing Gross ma... V B D LL H N o P Q 27 Money earned in the future is less valuable than money earned today, for the simple reason that money 28 earned today can be invested to earn returns. Similarly, the costs incurred in the future are less "costly" 29 than costs incurred today. This is why you typically don't simply sum up revenues and costs in a multi- 30 period model. Instead you discount future revenues and costs for a fair comparison with revenues and costs incurred today. The resulting sum of discounted cash flows is the net present value (NPV). Projects with positive NPVs typically increase the value of the company, whereas projects with negative NPVs decrease the value of the company. 5 Sensitivity to peak year (peak sales) If money can be invested at a 5% (r= 0.05; the rate / is called the discount rate) annual interest rate, then $1 received now is essentially equivalent to $1.05 a year from now. $1.00 = $1.05 a year from now = $1.00-(1 +r) Dividing both sides by (1 + r). $1.00/(1 + r) = $1.00 a year from now The value of 1/(1 + r) is called the discount factor, and it is always less than 1. In this example, for r= 0.05, it is evaluates to $0.952 and represents the present value of $1.00 received a year from now. The idea is that if you had $0.952 now, you could invest it at 5% and have it grow to $1.00 in a year. If money can be invested at an annual rate r compunded each year, then $1 received t years from now has Sensistivity to rate of increase in early years and peak year the same value as 1/(1 + r) dollars received today. If you multiply a cash flow received t years from now by 1/(1 + r) to obtain its present value, the total (sum) of these present values over all years is called the net 0.05 0.06 0.07 0.08 0.09 0.1 present value ( NPV) of cash flows. Sheet1 Ready O Type here to search O H 55.F Mostly clear DELL
