- Please answer and fill in ALL the Excel blanks where needed, many are in B AND
ID: 2625872 • Letter: #
Question
- 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
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.
12-7
B C2010
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 =
Explanation / Answer
Please see answers below
1. Stevens Textiles Pro Forma Income Statement December 31, 2014
2006
Forecast Basis
2007
Sales
36000
1.15 x Sales
41,400
Operating Costs
32,440
.9011 x sales (07)
37,306
EBIT
3,560
4,094
Interest
460
.1 x Debt (06)
560
EBT
3,100
35,34
Taxes (40%)
1,249
1,414
Net Income
1,860
2,120
Dividends
837
954
Addition to RE
1,023
1,166
Stevens Textiles Pro Forma Balance Sheet
December 31, 2014 (Thousands of Dollars)
Forecast Basis %
Pro Forma after
2006
2007 Sales
Additions
Pro Forma
Financing
Financing
Cash
10,800
.0300
1,242
1242
Acc. Rec.
6480
.1883
7452
7452
Inventories
9000
.2005
10350
10350
Total Curr. Assets
16560
19044
19044
Fixed Assets
12600
.3500
14490
14490
Total Assets
29160
33534
33534
Acc. Payable
4320
.1200
4968
4968
Accruals
2880
.0800
3312
3312
Notes Payable
2100
2100
+2128
4228
Total Curr. Liablities
9300
10380
12508
Long-term debt
3500
3500
3500
Total Debt
12800
13880
16008
Common Stock
3500
3500
3500
Retained Earnings
12860
1.166*
14026
14026
Total Liablities and Eq.
29160
31406
33534
AFN = 2128
2. Notes payable: $4228
2006
Forecast Basis
2007
Sales
36000
1.15 x Sales
41,400
Operating Costs
32,440
.9011 x sales (07)
37,306
EBIT
3,560
4,094
Interest
460
.1 x Debt (06)
560
EBT
3,100
35,34
Taxes (40%)
1,249
1,414
Net Income
1,860
2,120
Dividends
837
954
Addition to RE
1,023
1,166
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.