Pick at least two variables in the spreadsheet to flex by 5% up and down (for variables that cover more than one year please remember to change all years). Move these variables up 5% and recorded the new net present value. Take the difference between the new net present value and the original net present value and divide by the original net present value to get the percentage change in net present value. Record what you changed and the percentage change in net present value.
28% 2 Year 0 1 2 3 4 5 6 7 8 3 Acq stage Assumptions: 4 Research and development S (4,600,000.00) $4.6M in product research and development (R&D team of 20 $130,000 salary, plus $2M in resources) https://www.salary.com/research/salary/employer/chipotle-mexican-grill-inc/research-a 5 Initial promotion, updates to menu S (31,460,000.00) $1,000 promotion package cost x 12 mo x 2,580 stores = $30,960,000 and $500,000 in updates to menu and digital media 6 New supply network $ (3,000,000.00) $3M in additional supply and packaging 7 Employee training $ (309,600.00) 6 employees per store x $20 per hour x 1 hrs x 2,580 stores ($309,600) 8 Product sampling $ (2,000,000.00) $2M in promotional product sampling (77 of $10 kits for 2580 stores) 9 Additional investment in WC-7% 7% 7% 7% 7% 7% 7% 7% 7% 7% 10 WC balance $ 3,910,458.30 $ 4,184,190 $ 4,477,084 $ 4,790,480 $ 5,125,813 S 5,484,620 S 5,868,543 $ 6,279,342 $ 6,718,895 11 Incremental investment in working capital $ (3,910,458.30) $ (273,732.08) $ (292,893.33) $ (313,395.86) $ (335,333.57) $ (358,806.92) $ (383,923.40) S (410,798.04) $ (451,203.00) 12 13 Operating stage Assumptions: 14 Additional sales of new product $ 55,863,690.00 0.02 0.02 0.02 0.02 0.01 0.01 0.01 15 Lost sales of existing product $ (5,000,000.00 $ (5,000,000.00) $ (5,000,000.00) $ (5,000,000.00) S (5,000,000.00) S (5,000,000.00) S (5,000,000.00) $ (5,000,000.00) 16 Variable cost of new product (% of sales) 60% 60% 60% 60% 60% 60% 60% 60% 17 Variable cost of old menu product (%) 60% 60% 60% 60% 60% 60% 60% 60% 18 Additional fixed cost of new product $ (500,000.00) $ (500,000.00) $ (500,000.00) $ (500,000.00) S (500,000.00) S (250,000.00) $ (250,000.00) $ (250,000.00) 19 Reduction in fixed cost of old product $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 20 Tax rate 28% 28% 28% 28% 28% 28% 28% 21 22 Disposition / Continuing stage 23 5 times the last forecasted year's net operating stage cash flow 24 25 Year 0 1 2 3 4 5 6 7 8 26 Acquisition stage cash flow: 27 Research and development $ (4,600,000.00) 28 Initial promotion $ (31,460,000.00) 29 Expenses related to the new supply network S (3,000,000.00) 30 Training S (309,600.00) 31 Vendor testing $ (2,000,000.00) 32 Additional investment in WC $ (3,910,458.30) $ (273,732.08) $ (292,893.33) $ (313,395.86) $ (335,333.57) $ (358,806.92) S (383,923.40) $ (410,798.04) $ (451,203.00) 33 Total acquisition stage cash flow $ (45,280,058.30) $ (273,732.08) $ (292,893.33) $ (313,395.86) $ (335,333.57) $ (358,806.92) $ (383,923.40) S (410,798.04) $ (451,203.00) 34 35 Operating stage cash flow: 36 Additional sales of new product S 55,863,690.00 $ 56,980,963.80 $ 58,120,583.08 $ 59,282,994.74 $ 60,468,654.63 $ 61,073,341.18 S 61,684,074.59 S 62,300,915.34 37 Lost sales of existing product $ (5,000,000.00) S (5,000,000.00) $ (5,000,000.00) $ (5,000,000.00) S (5,000,000.00 $ (5,000,000.00 $ (5,000,000.00 $ (5,000,000.00) 38 Variable cost of new product % of sales) $ (33,518,214.00) $ (34,188,578.28) $ (34,872,349.85) $ (35,569,796.84) S (36,281,192.78) $ (36,644,004.71) $ (37,010,444.75) S (37,380,549.20) 39 Variable cost of old menu product (%) $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 (500,000.00) $ 10,000.00 $ 19,855,476.00 $ (5,559,533.28) $ 14,295,942.72 S (500,000.00) S 10,000.00 $ 20,302,385.52 $ (5,684,667.95) $ 14,617,717.57 $ (500,000.00) 10,000.00 $ 20,758,233.23 $ (5,812,305.30) $ 14,945,927.93 $ (500,000.00 $ 10,000.00 $ 21,223,197.90 $ (5,942,495.41) S 15,280,702.48 $ (500,000.00) $ 10,000.00 $ 21,697,461.85$ (6,075,289.32) $ 15,622,172.53 $ (250,000.00) S 10,000.00 $ 22,189,336.47 S (6,213,014.21) $ 15,976,322.26 $ (250,000.00) $ 10,000.00 $ 22,433,629.84 S (6,281,416.35) $ 16,152,213.48 $ (250,000.00) 10,000.00 22,680,366.13 (6,350,502.52) 16,329,863.62 1 2 3 4 5 6 7 8 81,649,318.08 $ 14,022,210.64 $ 14,324,824.25 S 14,632,532.07 $ 14,945,368.91 $ 15,263,365.61 $ 15,592,398.86 $ 15,741,415.44 $ 97,527,978.70 40 Additional fixed cost of new product (incl. advertisement cost) $ 41 Reduction in fixed cost of old product $ 42 Taxable income $ 43 Taxes $ 44 After tax income $ 45 46 Years 0 47 Continuing value 48 49 Total cash flow ($45,280,058.30) $ 50 8% cost of capital $ 129,955,967.64 51 NPV $ 84,675,909.34 52 Index 2.87 53 IRR 35% 54 55 Total cash flow ($45,280,058.30) S 56 Accumulated cash flow ($45,280,058.30) 57 Payback period 58 59 0 60 Total cash flow $ (45,280,058.30) $ 61 Present value (S45,280,058.30) 62 Accumulated present value (S45,280,058.30) 63 Present value payback period $ 14,022,210.64 $ (531,257,847.66) 14,324,824.25 $ ($16,933,023.41) 14,632,532.07 $ ($2,300,491.35) $ 14,945,368.91 12,644,877.57 $ S 15,263,365.61 27,908,243.18 $ 15,592,398.86 $ 43,500,642.04 S 15,741,415.44 59,242,057.48 97,527,978.70 156,770,036.18 S 3.15 years 1 14,022,210.64 $ $12,983,528.37 ($32,296,529.93) 2 14,324,824.25 $ $12,281,227.92 ($20,015,302.01) 3 14,632,532.07 $ $11,615,775.72 ($8,399,526.29) 4 14,945,368.91 $ $10,985,292.31 $2,585,766.03 5 15,263,365.61 $ $10,387,990.17 $12,973,756.19 6 15,592,398.86 $ $9,825,856.17 $22,799,612.36 7 15,741,415.44 $ $9,184,964.72 $31,984,577.08 8 97,527,978.70 $52,691,332.26 $84,675,909.34 3.76 years 28% 2 Year 0 1 2 3 4 5 6 7 8 3 Acq stage Assumptions: 4 Research and development S (4,600,000.00) $4.6M in product research and development (R&D team of 20 $130,000 salary, plus $2M in resources) https://www.salary.com/research/salary/employer/chipotle-mexican-grill-inc/research-a 5 Initial promotion, updates to menu S (31,460,000.00) $1,000 promotion package cost x 12 mo x 2,580 stores = $30,960,000 and $500,000 in updates to menu and digital media 6 New supply network $ (3,000,000.00) $3M in additional supply and packaging 7 Employee training $ (309,600.00) 6 employees per store x $20 per hour x 1 hrs x 2,580 stores ($309,600) 8 Product sampling $ (2,000,000.00) $2M in promotional product sampling (77 of $10 kits for 2580 stores) 9 Additional investment in WC-7% 7% 7% 7% 7% 7% 7% 7% 7% 7% 10 WC balance $ 3,910,458.30 $ 4,184,190 $ 4,477,084 $ 4,790,480 $ 5,125,813 S 5,484,620 S 5,868,543 $ 6,279,342 $ 6,718,895 11 Incremental investment in working capital $ (3,910,458.30) $ (273,732.08) $ (292,893.33) $ (313,395.86) $ (335,333.57) $ (358,806.92) $ (383,923.40) S (410,798.04) $ (451,203.00) 12 13 Operating stage Assumptions: 14 Additional sales of new product $ 55,863,690.00 0.02 0.02 0.02 0.02 0.01 0.01 0.01 15 Lost sales of existing product $ (5,000,000.00 $ (5,000,000.00) $ (5,000,000.00) $ (5,000,000.00) S (5,000,000.00) S (5,000,000.00) S (5,000,000.00) $ (5,000,000.00) 16 Variable cost of new product (% of sales) 60% 60% 60% 60% 60% 60% 60% 60% 17 Variable cost of old menu product (%) 60% 60% 60% 60% 60% 60% 60% 60% 18 Additional fixed cost of new product $ (500,000.00) $ (500,000.00) $ (500,000.00) $ (500,000.00) S (500,000.00) S (250,000.00) $ (250,000.00) $ (250,000.00) 19 Reduction in fixed cost of old product $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 20 Tax rate 28% 28% 28% 28% 28% 28% 28% 21 22 Disposition / Continuing stage 23 5 times the last forecasted year's net operating stage cash flow 24 25 Year 0 1 2 3 4 5 6 7 8 26 Acquisition stage cash flow: 27 Research and development $ (4,600,000.00) 28 Initial promotion $ (31,460,000.00) 29 Expenses related to the new supply network S (3,000,000.00) 30 Training S (309,600.00) 31 Vendor testing $ (2,000,000.00) 32 Additional investment in WC $ (3,910,458.30) $ (273,732.08) $ (292,893.33) $ (313,395.86) $ (335,333.57) $ (358,806.92) S (383,923.40) $ (410,798.04) $ (451,203.00) 33 Total acquisition stage cash flow $ (45,280,058.30) $ (273,732.08) $ (292,893.33) $ (313,395.86) $ (335,333.57) $ (358,806.92) $ (383,923.40) S (410,798.04) $ (451,203.00) 34 35 Operating stage cash flow: 36 Additional sales of new product S 55,863,690.00 $ 56,980,963.80 $ 58,120,583.08 $ 59,282,994.74 $ 60,468,654.63 $ 61,073,341.18 S 61,684,074.59 S 62,300,915.34 37 Lost sales of existing product $ (5,000,000.00) S (5,000,000.00) $ (5,000,000.00) $ (5,000,000.00) S (5,000,000.00 $ (5,000,000.00 $ (5,000,000.00 $ (5,000,000.00) 38 Variable cost of new product % of sales) $ (33,518,214.00) $ (34,188,578.28) $ (34,872,349.85) $ (35,569,796.84) S (36,281,192.78) $ (36,644,004.71) $ (37,010,444.75) S (37,380,549.20) 39 Variable cost of old menu product (%) $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 $ 3,000,000.00 (500,000.00) $ 10,000.00 $ 19,855,476.00 $ (5,559,533.28) $ 14,295,942.72 S (500,000.00) S 10,000.00 $ 20,302,385.52 $ (5,684,667.95) $ 14,617,717.57 $ (500,000.00) 10,000.00 $ 20,758,233.23 $ (5,812,305.30) $ 14,945,927.93 $ (500,000.00 $ 10,000.00 $ 21,223,197.90 $ (5,942,495.41) S 15,280,702.48 $ (500,000.00) $ 10,000.00 $ 21,697,461.85$ (6,075,289.32) $ 15,622,172.53 $ (250,000.00) S 10,000.00 $ 22,189,336.47 S (6,213,014.21) $ 15,976,322.26 $ (250,000.00) $ 10,000.00 $ 22,433,629.84 S (6,281,416.35) $ 16,152,213.48 $ (250,000.00) 10,000.00 22,680,366.13 (6,350,502.52) 16,329,863.62 1 2 3 4 5 6 7 8 81,649,318.08 $ 14,022,210.64 $ 14,324,824.25 S 14,632,532.07 $ 14,945,368.91 $ 15,263,365.61 $ 15,592,398.86 $ 15,741,415.44 $ 97,527,978.70 40 Additional fixed cost of new product (incl. advertisement cost) $ 41 Reduction in fixed cost of old product $ 42 Taxable income $ 43 Taxes $ 44 After tax income $ 45 46 Years 0 47 Continuing value 48 49 Total cash flow ($45,280,058.30) $ 50 8% cost of capital $ 129,955,967.64 51 NPV $ 84,675,909.34 52 Index 2.87 53 IRR 35% 54 55 Total cash flow ($45,280,058.30) S 56 Accumulated cash flow ($45,280,058.30) 57 Payback period 58 59 0 60 Total cash flow $ (45,280,058.30) $ 61 Present value (S45,280,058.30) 62 Accumulated present value (S45,280,058.30) 63 Present value payback period $ 14,022,210.64 $ (531,257,847.66) 14,324,824.25 $ ($16,933,023.41) 14,632,532.07 $ ($2,300,491.35) $ 14,945,368.91 12,644,877.57 $ S 15,263,365.61 27,908,243.18 $ 15,592,398.86 $ 43,500,642.04 S 15,741,415.44 59,242,057.48 97,527,978.70 156,770,036.18 S 3.15 years 1 14,022,210.64 $ $12,983,528.37 ($32,296,529.93) 2 14,324,824.25 $ $12,281,227.92 ($20,015,302.01) 3 14,632,532.07 $ $11,615,775.72 ($8,399,526.29) 4 14,945,368.91 $ $10,985,292.31 $2,585,766.03 5 15,263,365.61 $ $10,387,990.17 $12,973,756.19 6 15,592,398.86 $ $9,825,856.17 $22,799,612.36 7 15,741,415.44 $ $9,184,964.72 $31,984,577.08 8 97,527,978.70 $52,691,332.26 $84,675,909.34 3.76 years
