Question

Please Do in Excel Lunar Powered Corporation Income Statement ($ in millions) 2019 2020e Sales 3000 Cost of Goods -2100 SGA -450 Interest -50 -70 EBT 400 Taxes (35%) -140 Net income 260 Dividends -50 -60 Addn: Ret. earnings 210 === === Lunar Powered Corporation Balance Sheet 2019 2020e 2019 2020e Cash 20 Accounts payable 160 Accounts receivables 370 Notes payable 200 Inventory 300 Current liabilities 360 Short term Investments 60 Long-term debt 440 Current assets 750 Common stock 300 Net plant & equip. 1250 Retained earnings 900 Total assets 2000 Total liab. & equity 2000 ===== ===== ===== ===== Use the Lunar Powered Corporation Financial Statements above to answer the following questions and to report the projected 2020 figures: a. LPC has been growing rapidly during the past few years and has had to continually raise funds. The CFO has asked you to prepare a forecast of additional funds needed for 2020. She has indicated that sales will increase by 40% next year. Assuming a constant percentage of sales, complete the Lunar Powered Corporation 2020 income statement and balance sheet in the columns provided above. Assume that fixed assets are already at 100% capacity and will need to grow 40% too. Also, assume that interest expense will increase to $70 and that dividends will be $60, and that the short term investments account will not change now to allow LPC to maintain future financial flexibility. Finally, assume that additional funds will be provided by increasing long-term debt (or reducing debt if there is excess cash), and not from increasing notes payable or issuing stock. What additional funds will be needed in 2020 in this scenario (how much new long term debt will we need to obtain)? Determine this from the projected financial statements that you have prepared above. How much new additional funding will be needed? _______ b. The CFO has asked you to implement a new inventory reduction program. You have developed a plan and suggested a course of action to the CFO that you believe will improve the Inventory Turnover ratio (S/Inv.) to 12X. Project the impact of your plan on the inventory account for 2020 and explain how this will affect the need for additional funds in the coming year. Estimate the $ effect, but do not restate the rest of the balance sheet or the income statement. New inventory balance _______ Reduction in funds needed _______ c. The CFO also has asked you to improve the Gross Profit Margin (Sales-Cost of Goods Sold)/Sales)) by enacting reductions in the cost of goods sold. You are making changes that will improve the GPM to 35%. Project the impact of your plan on the addition to retained earnings for 2020 and explain how this will affect the need for additional funds in the coming year. New addition to retained earnings _______ Reduction in funds needed _______

Answer 1

Lunar Powered Corporation
Amt $M
Income statement
Details	Year 2019	% of Sales	Year 2020 estimated	Remarks
Sales	3000		4200	=+40%
COGS	2100	70%	2940	=+40%
Gross Profit	900	30%	1260
SGA	450	15%	630	=+40%
Interest	50	2%	70	=+40%
EBT	400	13%	560
Tax @35%	140		196
Net Income	260		364
Dividend	50		60	As given
Addn to Ret Earning	210		304

Balance Sheet
At the end of	2019	% of Sales	2020 estimated
Assets
Cash	20		28	=+40%
Accounts Receivable	370		518	=+40%
Inventory	300		420	=+40%
Short term Investment	60		60	no change
Current Asset	750		1026
Net Plant & Equipment	1250		1750	=+40%
Total Assets	2000		2776

Liabilities & Equity
Accounts Payable	160		224	=+40%
Notes Payable	200		200	no change
Current Liabilities	360		424
Long term debt	440		440	Keeping same to find additional debt
Common Stock	300		300	no change
Retained Earning	900		1204	With transfer from Income statement
Total Liabilities & Equity	2000		2368

	Balancing Amount			408
a	So additional funding required =		408

b	Target Inventory TO ratio	12
	Sales/Inv=12
	4200/Inv =12
	Inventory =	350
	So reduction is Asset =	70
	So reduction in funding requirement =	70
c	Revised GM estimate
	Details	Year 2020 estimated	% of Sales
	Sales	4200
	COGS	2730	65%
	Gross Profit	1470	35%
	SGA	630	15%
	Interest	70	2%
	EBT	770	18%
	Tax @35%	269.5
	Net Income	500.5
	Dividend	50
	Addn to Ret Earning	450.5
	Increase in retained earning =	146.5
	So reduction in funding required =	146.5