Purple Pillows, Inc is using the percent-of-sales method to calculate External F
ID: 2608188 • Letter: P
Question
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,125Explanation / Answer
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.75Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.