Worksheet to Use for Milestone #$3$: 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\mathrm{.}0\%$ Tax rate for PepsiCo................................................................................. $27\mathrm{.}00\%$ Continuing value assumption......................................................................... $4$ Cost of money......................................................................................... $8\mathrm{.}40\%$ Acquisition stage cash flow: Year$012345678910$ 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$012345678910$ Additional revenues expected from the sale of the new product:$100,000,000250,000,000450,000,000490,500,000534,645,000582,763,050635,211,725673,324,428713,723,894756,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$$

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

Q:

Most Likely Scenario: Excel Spreadsheet: Using the original numbers, fill in the yellow sections to calculate NPV , PI , IRR, payback period, and PV payback. Discuss and explain the meaning of each...

Q:

I am in need of help for Milestone 3 on this question: Scenario You have been hired as the new controller for Paulson Paint, Inc. You will have the opportunity to utilize your financial and...

Q:

QUESTION 2 Curechem SA Limited is a large publicly traded firm on the Johannesburg Stock Exchange. Based on a R100,000 market feasibility study it is considering the manufacture and sale of a new...

Q:

Al Dente Sarante MBA 5100 Case Study 1 Summer 2020 Slock 1 The Summer of 2020 posses several challenges for you as a recently hired MBA intern. You pre hired to work in a consulting capacity (while...

Q:

- + fit to page ALDente istorante MBA 6100 Case Study 1 Spring 2019 As a recently hired MBA intem, you are working in a consulting capacity to provide an analysis for Al Dente's Italian Restaurant....

Q:

I have some questions I would like help with. They are from my Finance 101 class. Can someone please help me? Q1. Jiffy Co. expects to pay a dividend of $3.00 per share in one year. The current price...

Q:

Hi everyone. Can someone please help me to help me to answer the questions below from my finance 101 midterm exam? Q1. Jiffy Co. expects to pay a dividend of $3.00 per share in one year. The current...

Q:

QUESTIONS: 1. Analyze the effectiveness of land and nature jerky consumer and trade promotions in 2018 and 2019. 2. Evaluate Ryan's proposed options. What are the financial and strategic implications...

Q:

MBA 6100 Case study Case #1 Sales $2,158,400 Cost of sales (all variable) $1,246,050 Gross Margin $912,350 Operating expenses: Variable $222,380 Fixed $170,940 Total operating expenses: $393,320...

Q:

Basic information Exhibit 1 Hallstead Jewelers Income Statements for Years Ended January 31 (thousands of dollars) 2003 2004 2006 Sales $9,000,000 $8,000,000 $11,000,000 Cost of goods sold 4,050,000...

Q:

The following data gives the means of two samples taken from a population. Test whether there is any significant difference between the two samples at 95% level. x = 60, y = 59, n = 100, n = 200 and ...

Q:

On January 1, 2012, Fran started a small flower merchandising business that she named Frans Flowers. The company experienced the following events during the first year of operation. 1. Started the...

Q:

which of the four BSC perspectives ( financial , customer, internal process, or learning and growth ) do you think organizations typically find most challenging to implement, and why?

Q:

There are many management theories that are utilized in organizations. These theories were developed by scholars in the management discipline. One individual was responsible for identifying the major...

Previous Question Next Question