Question
The class excel model includes cost of goods sold but not selling, general, and administrative (SG&A) expenses. Suppose that the firm has $200 of these
The class excel model includes cost of goods sold but not selling, 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, 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) |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started