Peaco Model

You are considering starting Peaco, which will produce Peakbabies, a product that competes with Ty$$s Beanie Babies. In year $0($right now$),$ you will incur costs of $$4$ million to build a plant. Depreciate this cost over years $1$ to $5$ using straight$-$line depreciation. In year $1,$ you expect to sell $80,000$ Peakbabies for a unit price of $$25$ each. The price of $$25$ will remain unchanged through years $1$ to $5.$ Peaco incurs two types of costs: variable costs and SG&A $($selling$,$ general, and administrative$)$ costs. Variable costs are equal to half of revenue each year. During year $1,$ SG&A costs equaled $40\%$ of revenue. This percentage is assumed to drop $2\%$ per year, so during year $2$ SG&A will equal $38\%$ of revenue and so on$.$ Peaco pays taxes of $35\%$ per year, but only when they make a profit. Negative profits result in no tax. Peaco$$s goal is to have profits for years $0$ to $5$ sum to $0($ignoring the time value of money$).$ This will ensure that the $$4$ million investment is paid back by the end of year $5.$ What annual percentage sales growth rate in the number of Peakbabies sold does Peaco require to pay back the plant cost by the end of year $5?$

$$ To get started, assume a sales growth rate of $20\%$ per year.

$$ Create income statements for years $1-5.$

$$ When creating the spreadsheet, use at least four range names.

$$ Helpful formulas $($you don$$t have to use these names$).$

o Revenue $=$ Number Sold $*$Price Each

o Cost of Goods Sold $($CGS$)=$ Variable Cost $+$ SG&A $+$ Depreciation. In this problem, fixed cost is included in SG&A$,$ so you don$$t have to worry about it$.$

o Income Before Income Tax $($IBIT$)=$ Revenue $$ CGS

o Tax $=$ Tax Rate $*$ IBIT. Note that tax is $0$ when IBIT is negative.

o Net Income $=$ EBIT $$ Tax

$$ Find the sales growth rate needed to get a total net income of $$0$ for years $1-5.$ Your answer probably won$$t be exactly $$0,$ just get it to the nearest $0\mathrm{.}1\%.$

$$ Color code the spreadsheet correctly to indicate inputs, calculations, and outputs.

$$ Each year $1-5$ should have its own calculations, in its own column.

$$ See the Hint picture on Moodle. Your spreadsheet doesn$$t have to look like the picture. It$$s just there if you aren$$t sure how to get started.

Complete your model showing the calculations in an Excel file. Use proper color coding, and do not hardcode.

Check your model

$$ With the numbers from the problem and $50\%$ sales growth, the total Net Income is

$$$$363,375.$

$$ If you change to the following inputs, your total Net Income is $$1,094,506.$

o The plant costs $$5,000,000.$

o Initial sales are $100,000$ units.

o The sales growth is $35\%$ per year.

o The selling price is $$27.$

o Variable costs are $45\%$ of Revenue

o Initial SG&A is $36\%$ of Revenue in Year $1,$ declining by $3\%$ per year.

o Depreciation remains $5$ years, straight$-$line.

o The tax rate is $38\%.$

$$ Change your model back to the original inputs given in the problem from the textbook.

What sales growth is needed to ensure a total Net Income of $$0$ over years $1$ to $5?$ Your

answer should be accurate to one decimal place, and may not be exactly $$0.$