please read the problem, and answer the questions by using the color code
the red outline is the problem
the green is the questiobs
MutoSave 72 OC... ISDS 710 Excel Model 6 - Time Value of Money (Net Present Value or NPV) (1) e Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments 2 AutoSum Calibri (Body 11 V Wrap Text General v , 27-O I PA [ Pasko = = = = = Meg A Critev $%% Conditional Format CAN Fumiting it Tath Siyias InserDelete Fomat cor v Sort Fler Find Balct Sensitivity Andyze Data Create and Share Adobe PDF X24 A C D R w 12 EG 4 PER TE Erlin He Loron la pengar hefur orbis tristice thornstar NADI.U slasukan teoret , CERE IWA 3 dal site at det kan 1. Willaminagpakitlesin W 10 Dr. WattpraxxX15: 11 tot 12 Cal Grip TaaldsTET, I tru: L$15 ribu extracts raight to SLS in terrasaranaratamaran TIL Extrapher..wanada Www.xpayee receptes, we wir Kohn, teethril ESTATI. I attend of their dagar ialdaseline TEXT the sport TUTTI I DATUITE 2. tuent per the waren trerit 55. Free 2. Mana tamaddru denguet Wquxr1 12 Dr. Watst of roser to the Strayer trascharest strax 12 13 IG 18 6 19 7 11 1 ! 10 2x 11 12 Dabarthing IT WARM In rared with wife muste arta TUPITER wowote TV STRET Switch porturi per laatory WERE BUSCA Warcher la sua nas Tod istituinsleirit TEC berturs malawarkan. U was round relatarina rus PENITUSV W Perlandsfera parurther and carrators! D 23 13 26 12 19 16 17 1 cbxecute PRVN: 141 19 MPV Prepararea avalcareiros de Serey peasyuar peace : Homercio de Arranged ner: 2116 a parlare. '' Det dit... DEL 1.0 TIPAT PER Thawke kulit Will the end. sikit baina 51 ar norite Pres. Dere. The centa: Tunc SCF2U Dina wa ligur.com Hanwan berkuranprediche, il tuo rent vax LNB of skur roshadda rutas cache et aura Tab 34.in war 0. ESISTES 10 Sro de DES DOS :.DT 00 0.00 21 24 PO EL 11 60 1.1 Sheet1 Ready 3 Ne- 62' Q Calculatine NPV at Acron Modeling Assignm Acron is a large drug company. At the current time, the beginning of year 0, Acron is trying to decide whether one of its new drugs, Nagra, is worth pursuing Niagra is in the final stages of development and will be ready to enter the market one year from now Question 1. Which Answer: 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. Question 2. What Answer: Acron wants to develop a spreadsheet model of its 20-year cash flows, assuming its cash flows, other than the initial development cost, are incurred at the end of the respective years. Using an annual discount rate of 75% for the purpose of calculating NPV, the drug company wants to answer the following questions: Question 3. What present value of tt Answer: 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? Money earned in the future is less valuable than money earned today, for the simple reason that money earned today can be invested to earn retums. 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 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 volue (NPV). Projects with positive NPVs typically increase the value of the company, whereas projects with negative NPVS decrease the value of the company of money can be invested at a 5% (r=0.05, the rater is called the discount rate) annual interest rate, then I received now is essentially equivalent to $1.05 a year from now. $1.00 - $1.05 a year from now = $1.00*(1+1) Dividing both sides by (1 $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, forr-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 of money can be invested at an annual rater compunded each year, then I received tyears from now has the same value as 1/11 . dollars received today. If you multiply a cash flow received t years from now by 1/1. to obtain 01 its present value, the total (sum) of these present values over all years is called the net present value (NPV) of cash 0.09 Modeling Assignment 6 Question 1. Which has a greater present value: the gross margin for year 1 or the gross margin for year 8 (the peak year for gross margin)? Answer . Question 2. What is the present value of the gross margin at year 16? Answer: Question 3. What rate of increase is needed from year I through year 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: must determine a Questions sive machine that MutoSave 72 OC... ISDS 710 Excel Model 6 - Time Value of Money (Net Present Value or NPV) (1) e Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments 2 AutoSum Calibri (Body 11 V Wrap Text General v , 27-O I PA [ Pasko = = = = = Meg A Critev $%% Conditional Format CAN Fumiting it Tath Siyias InserDelete Fomat cor v Sort Fler Find Balct Sensitivity Andyze Data Create and Share Adobe PDF X24 A C D R w 12 EG 4 PER TE Erlin He Loron la pengar hefur orbis tristice thornstar NADI.U slasukan teoret , CERE IWA 3 dal site at det kan 1. Willaminagpakitlesin W 10 Dr. WattpraxxX15: 11 tot 12 Cal Grip TaaldsTET, I tru: L$15 ribu extracts raight to SLS in terrasaranaratamaran TIL Extrapher..wanada Www.xpayee receptes, we wir Kohn, teethril ESTATI. I attend of their dagar ialdaseline TEXT the sport TUTTI I DATUITE 2. tuent per the waren trerit 55. Free 2. Mana tamaddru denguet Wquxr1 12 Dr. Watst of roser to the Strayer trascharest strax 12 13 IG 18 6 19 7 11 1 ! 10 2x 11 12 Dabarthing IT WARM In rared with wife muste arta TUPITER wowote TV STRET Switch porturi per laatory WERE BUSCA Warcher la sua nas Tod istituinsleirit TEC berturs malawarkan. U was round relatarina rus PENITUSV W Perlandsfera parurther and carrators! D 23 13 26 12 19 16 17 1 cbxecute PRVN: 141 19 MPV Prepararea avalcareiros de Serey peasyuar peace : Homercio de Arranged ner: 2116 a parlare. '' Det dit... DEL 1.0 TIPAT PER Thawke kulit Will the end. sikit baina 51 ar norite Pres. Dere. The centa: Tunc SCF2U Dina wa ligur.com Hanwan berkuranprediche, il tuo rent vax LNB of skur roshadda rutas cache et aura Tab 34.in war 0. ESISTES 10 Sro de DES DOS :.DT 00 0.00 21 24 PO EL 11 60 1.1 Sheet1 Ready 3 Ne- 62' Q Calculatine NPV at Acron Modeling Assignm Acron is a large drug company. At the current time, the beginning of year 0, Acron is trying to decide whether one of its new drugs, Nagra, is worth pursuing Niagra is in the final stages of development and will be ready to enter the market one year from now Question 1. Which Answer: 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. Question 2. What Answer: Acron wants to develop a spreadsheet model of its 20-year cash flows, assuming its cash flows, other than the initial development cost, are incurred at the end of the respective years. Using an annual discount rate of 75% for the purpose of calculating NPV, the drug company wants to answer the following questions: Question 3. What present value of tt Answer: 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? Money earned in the future is less valuable than money earned today, for the simple reason that money earned today can be invested to earn retums. 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 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 volue (NPV). Projects with positive NPVs typically increase the value of the company, whereas projects with negative NPVS decrease the value of the company of money can be invested at a 5% (r=0.05, the rater is called the discount rate) annual interest rate, then I received now is essentially equivalent to $1.05 a year from now. $1.00 - $1.05 a year from now = $1.00*(1+1) Dividing both sides by (1 $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, forr-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 of money can be invested at an annual rater compunded each year, then I received tyears from now has the same value as 1/11 . dollars received today. If you multiply a cash flow received t years from now by 1/1. to obtain 01 its present value, the total (sum) of these present values over all years is called the net present value (NPV) of cash 0.09 Modeling Assignment 6 Question 1. Which has a greater present value: the gross margin for year 1 or the gross margin for year 8 (the peak year for gross margin)? Answer . Question 2. What is the present value of the gross margin at year 16? Answer: Question 3. What rate of increase is needed from year I through year 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: must determine a Questions sive machine that
