Purple Pillows Inc is using the percentofsales method to cal

Purple Pillows, Inc is using the percent-of-sales method to calculate External Financing Needed (EFN) over the next two years. Assume the following:

* They are at 100% asset capacity

* Depreciation, and interest grow in proportion with sales

* The tax rate remains constant over the three years

* Long-term notes are the plug variable

1) Fill in all the blank yellow cells with the appropriate functions

2) Balance the balance sheet using LT notes as the plug

3) Ensure that your spreadsheet is fully dynamic by changing the yellow-highlighted growth, tax, and dividend rates

4) Use your sheet to calculate EFN for the three scenarios

Income Statement 10% 26% 35% Growth Rate Tax Rate Dividend Rate Purple Pillows, Inc 2018 2019 Sales COGS SG&A; 2017 $ 1,000,000 $ (350,000) $ (400,000) Operating Income 250,000 $ $(50,000) $200,000 $ $ (75,000) $125,000 $ $ (32,500) $ 92,500$ Depreciation EBIT Interest paid Taxable income Taxes Net Income $32,375 Dividend Add to RE 60,125

Solution

Second scenario:

Third scenario:

Income statement	10%	Growth rate
Purple pillows,Inc	26%	Tax rate
	35%	Dividend rate
	2017	2018	2019
Sales	10,00,000	11,00,000	12,10,000
Cogs	-3,50,000	-3,85,000	-4,23,500
SG&A	-4,00,000	-4,40,000	-4,84,000
Operating income	2,50,000	2,75,000	3,02,500
Depreciation	-50,000	-55,000	-60,500
EBIT	2,00,000	2,20,000	2,42,000
Interest paid	-75,000	-82,500	-90,750
Taxable income	1,25,000	1,37,500	1,51,250
Taxes	-32,500	-35,750	-39,325
Net income	92,500	1,01,750	1,11,925
Dividend	-32,375	35,612.50	39,173.75
Addition to retained earnings	60,125	1,37,362.50	1,51,098.75