Fill in the blanks and write two pages about the Excel document.

0
Increase in Costs Scenario:
Assumptions:
Fixed operating costs in dollars		$ 85,000,000	Growing at 10% per year over the life of the project
Variable operating cost as a percentage of revenues		66.0%
Tax rate for PepsiCo.................................................................................	27.00%
Continuing value assumption.........................................................................	4
Cost of money.........................................................................................	8.40%
Acquisition stage cash flow:
Year	0	1	2	3	4	5	6	7	8	9	10
Research and development costs	$ (5,000,000)
Product launch and promotion costs	$ (40,000,000)	$ (20,000,000)	$ (10,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)
Capacity, warehousing, equipment costs	$ (50,000,000)
Upfront inventory costs	$ (21,385,800)
Total Acquisitions Stage Costs	$ (116,385,800)	$ (20,000,000)	$ (10,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)	$ (5,000,000)
Operating stage cash flow:
Year	0	1	2	3	4	5	6	7	8	9	10
Additional revenues expected from the sale of the new product:	100,000,000	250,000,000	450,000,000	490,500,000	534,645,000	582,763,050	635,211,725	673,324,428	713,723,894	756,547,327
Fixed operating cost in dollars		(85,000,000)	(93,500,000)	(102,850,000)	(113,135,000)	(124,448,500)	(136,893,350)	(150,582,685)	(165,640,954)	(182,205,049)	(200,425,554)
Variable operating cost as a percentage of revenues = 66%		$ (66,000,000)	$ (165,000,000)	$ (297,000,000)	$ (323,730,000)	$ (352,865,700)	$ (384,623,613)	$ (419,239,738)	$ (444,394,122)	$ (471,057,770)	$ (499,321,236)
Total operating costs		$ (151,000,000)	$ (258,500,000)	$ (399,850,000)	$ (436,865,000)	$ (477,314,200)	$ (521,516,963)	$ (569,822,423)	$ (610,035,076)	$ (653,262,819)	$ (699,746,790)
Operating cash flows		$ (51,000,000)	$ (8,500,000)	$ 50,150,000	$ 53,635,000	$ 57,330,800	$ 61,246,087	$ 65,389,301	$ 63,289,352	$ 60,461,075	$ 56,800,538
Taxes at 27% (from PepsiCo's history and annual reports)		$ 13,770,000	$ 2,295,000	$ (13,540,500)	$ (14,481,450)	$ (15,479,316)	$ (16,536,443)	$ (17,655,111)	$ (17,088,125)	$ (16,324,490)	$ (15,336,145)
Net operating income		$ (37,230,000)	$ (6,205,000)	$ 36,609,500	$ 39,153,550	$ 41,851,484	$ 44,709,644	$ 47,734,190	$ 46,201,227	$ 44,136,585	$ 41,464,392
Disposition stage cash flow:											$ 165,857,570
Total cash flow	$ (116,385,800)	$ (57,230,000)	$ (16,205,000)	$ 31,609,500	$ 34,153,550	$ 36,851,484	$ 39,709,644	$ 42,734,190	$ 41,201,227	$ 39,136,585	$ 202,321,962
Present Value	$ 52,794,281
Net Present Value	$ 71,768,334
Profitability Index	1.62		(This should be calculated to two decimal places --- such as 1.63)
Internal Rate of Return	18.24%		(This should be calculated to two decimal places --- such as 7.92%)
Total cash flow	$ (116,385,800)	$ (57,230,000)	$ (16,205,000)	$ 31,609,500	$ 34,153,550	$ 36,851,484	$ 39,709,644	$ 42,734,190	$ 41,201,227	$ 39,136,585	$ 202,321,962
Accumulated cash flows
Payback	7.12	years	(This should be calculated to two decimal places such as 3.15 years)
Total cash flow	$ (116,385,800)	$ (57,230,000)	$ (16,205,000)	$ 31,609,500	$ 34,153,550	$ 36,851,484	$ 39,709,644	$ 42,734,190	$ 41,201,227	$ 39,136,585	$ 202,321,962
Present value each year
Accumulated cash flows
Present Value Payback	9.21	years	(This should be calculated to two decimal places such as 3.15 years)