M Inbox (9,699) - dahal48@gmail.cc X PDE Business analytics data analysis a X + X C @ File | C:/DBA/Business%20analytics%20%20data%20analysis%20and%20decison%20making%20(S.%20Christian%20Albright%2.. . . 822 of 914 Q 1. Inputs. Enter the given inputs in the blue cells. Development Model 2. Unit sales. Generate first-year sales in cell B12 with the formula = Risk Triang(E5,F5,G5) Then generate the reduced sales in later years by entering the formula =B12*(1 -RiskTriang($E$6,$F$6,$G$6)) in cell C12 and copying it across row 12. Note that each sales figure is a random fraction of the previous sales figure. Figure 16.15 GF Auto Simulation Model B C D E F G 1 New car simulation 2 3 Inputs Parameters of triangular distributions 4 Fixed development cost $ 600,000,000 Min Most likely Max 5 Year 1 contribution $4,000 Year 1 sales 3000 55000 65000 6 Annual decrease in contribution 4% Annual decay rate 5% 8% 10% 7 Tax rate 21% 8 Discount rate 7% 9 + 10 Simulation 11 End of year 1 2 3 4 5 12 Unit sales 56529 51303 46936 43585 41017 13 Unit contribution $4,000 $3,840 $3,686 $3,539 $3,397 14 Revenue minus variable cost $226,116,773 $197,004,495 $173,026,079 $154,246,551 $139,350,833 15 Depreciation $ 120,000,000 $120,000,000 $120,000,000 $120,000,000 $120,000,000 16 Before tax profit $ 106,116,773 $77,004,495 $53,026,079 $34,246,551 $19,350,833 17 After tax profit $83,832,251 $60,833,551 $41,890,602 $27,054,776 $15,287,158 18 Cash flow $203,832,251 $180,833,551 $161,890,602 $147,054,776 $135,287,158 19 20 NPV of cash flows $89,240,670 68F Partly sunny Q Search zoom W 2:56 PM 5 12/8/2022