- Please answer and fill in ALL the Excel blanks where needed, many are in B AND C but others are in AFN ! I really appreciate the help!!!! Best answer will get max points!! I have included a picture of the actual excel spreadsheet to act as a guide. Thanks!

12-7

Balance Sheet as of December 31, 2013 (Thousands of Dollars)

Cash $ 1,080 Accounts payable $ 4,320

Receivables 6,480 Accruals 2,880

Inventories 9,000 Line of credit 0

Total current assets $16,560 Notes payable 2,100

Net fixed assets 12,600 Total current liabilities $ 9,300

Mortgage bonds 3,500

Common stock 3,500

______ Retained earnings 12,860

Total assets $29,160 Total liabilities and equity $ 29,160

Income Statement for December 31, 2013 (Thousands of Dollars)

Sales $36,000

Operating costs 32,440

Earnings before interest and taxes $ 3,560

Interest 460

Pre-tax earnings $ 3,100

Taxes (40%) 1,240

Net income $ 1,860

Dividends (45%) $ 837

Addition to retained earnings $ 1,023

12-7

B

C

2010

Sales

350.00

Net Income

10.50

M (Profit Margin)

Dividends Paid

$ 4.20

Payout Ratio

40.00%

RR

60.00%

2011

Sales - Increase

70.00

% Increase

Sales

M (Profit Margin)

Net Income

Payout Ratio

Dividends Paid

RR

AFN = (A*/S0)?S - (L*/S0)?S - MS1(RR)

(A*/S0)?S

(L*/S0)? S

MS1(RR)

AFN

12-b

AFN = (A*/S0)?S - (L*/S0)?S - MS(RR)

Step 1 Find S1

S1 = S0 times ((A*/S0) - (L*/S0))/((A*/S0) - (L*/S0)- MS(RR))

Step 2 Subtract S0 from S1

(A*/S0) =

Calculate from the Current Year's Balance Sheet and

(L*/S0) =

(A*/S0) - (L*/S0)

% of Assets that change with Sales minus % of Spontaneous Liabilities that change with Sales

S0 =

S1 =

S1 = S0 times ((A*/S0) - (L*/S0))/((A*/S0) - (L*/S0)- M(RR))

M

From above

M(RR)

From above

Change in Sales

S1-S0

% Change in Sales W/O AFN

12-7c

Forecasting

2010

2011

2010

basis

Ratios

Inputs

Without AFN

AFN

With AFN

Assets:

Cash and cash equivalents

A*

3.50

% of sales

Short-term investments

A*

-

Previous

Accounts Receivable

A*

26.00

% of sales

Inventories

A*

58.00

% of sales

Total current assets

A*

87.50

Fixed assets

A*

35.00

% of sales

Total assets

A*

122.50

Liabilities and equity

Accounts payable

L*

9.00

% of sales

Accruals

L*

8.50

% of sales

Notes payable

18.00

Previous

-

Total current liabilities

35.50

Long-term debt

6.00

Previous

Total liabilities

41.50

Common stock

15.00

Previous

Retained Earnings

66.00

Previous + Change in R/E

Total common equity

81.00

Total liabilities and equity

122.50

Required assets =

Specified sources of financing =

Additional funds needed (AFN) =

Required additional notes payable =

Additional short-term investments =

1. Suppose 2014 sales are projected to increase by over 2013 sales. Use the forecasted financial statement method to forecast a balance sheet and income statement for December 31, 2014. The interest rate on all debt is , and cash earns no interest income. Assume that all additional debt in the form of a line of credit is added at the end of the year, which means that you should base the forecasted interest expense on the balance of debt at the beginning of the year. Use the forecasted income statement to determine the addition to retained earnings. Assume that the company was operating at full capacity in 2013, that it cannot sell off any of its fixed assets, and that any required financing will be borrowed as notes payable. Also, assume that assets, spontaneous liabilities, and operating costs are expected to increase by the same percentage as sales. Determine the additional funds needed.

2. What is the resulting total forecasted amount of the line of credit?

3. In your answers to Parts a and b, you should not have charged any interest on the additional debt added during 2014 because it was assumed that the new debt was added at the end of the year. But now suppose that the new debt is added throughout the year. Dont do any calculations, but how would this change the answers to parts a and b?

Below is just a guide for above.