Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Principles Of Financial Auditing

Authors: Athmane Mokhbi

1st Edition

B09LGTJJFG, 979-8763532265

More Books

Students also viewed these Accounting questions

Question

I need solution for managerial accounting chapter 23 P23 26 A

Answered: 1 week ago

Question

Are there any questions that you want to ask?

Answered: 1 week ago