The class excel model includes cost of goods sold but not sold, general, and administrative (SG&A) expenses. Suppose that the firm has $200 of these expenses each year, irrespective of level of sales.

a. Change the model to accommodate this new assumption. Show the resulting profit and loss statements, balance sheets, the free cash flows, and valuation.

b. Calculate your financial formulae on the newly created model. If the formula does not apply state it does not apply, and why.

Sorry, I don't have excel right now so this Accounting class homework assignment in excel. please you use those information below in excel thank you. SETTING UP THE FINANCIAL STATEMENT MODEL
Sales growth	10%
Current assets/Sales	15%
Current liabilities/Sales	8%
Net fixed assets/Sales	77%
Costs of goods sold/Sales	50%
Depreciation rate	10%
Interest rate on debt	10.00%
Interest paid on cash and marketable securities	8.00%
Tax rate	40%
Dividend payout ratio	40%
Year	0	1
Income statement
Sales	1,000	1,100	<-- =B15*(1+$B$2)
Costs of goods sold	(500)	(550)	<-- =-C15*$B$6
Interest payments on debt	(32)	(32)	<-- =-$B$8*(B36+C36)/2
Interest earned on cash and marketable securities	6		<-- =$B$9*(B27+C27)/2
Depreciation	(100)		<-- =-$B$7*(C30+B30)/2
Profit before tax	374		<-- =SUM(C15:C19)
Taxes	(150)		<-- =-C20*$B$10
Profit after tax	225		<-- =C21+C20
Dividends	(90)		<-- =-$B$11*C22
Retained earnings	135		<-- =C23+C22
Balance sheet
Cash and marketable securities	80		<-- =C39-C28-C32
Current assets	150	165	<-- =C15*$B$3
Fixed assets
At cost	1,070		<-- =C32-C31
Depreciation	(300)		<-- =B31+C19
Net fixed assets	770	847	<-- =C15*$B$5
Total assets	1,000		<-- =C32+C28+C27
Current liabilities	80	88	<-- =C15*$B$4
Debt	320	320	<-- =B36
Stock	450	450	<-- =B37
Accumulated retained earnings	150		<-- =B38+C24
Total liabilities and equity	1,000		<-- =SUM(C35:C38)

Answer rating: 100% (QA)

Answer Fixed selling general and admin exps of 200 are adde... View the full answer