Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
420.0 Carry over from previous year $420.00
200.0 $253
$620.0 $673
$1,200.0 $1,320
Check: TA − Total Liab. & Eq. = $0.00
Most Recent Forecast
2016 Input 2017
$2,000.0 110% $2,200.00
1,800.0 90.00% $1,980.00
50.0 10.00% $55.00
$150.0 $165.00
40.0 8.00% × Avg bonds $40.00
0.0 8.00% × Beginning LOC $0.00
$110.0 $125.00
44.0 40.00% $50.00
$66.0 $75.00
$20.0 110% $22.00
$0.0 Pay if financing surplus $0.00
$46.0 Net income – Dividends $53.00
surplus financing: −$9.
g (negative), draw on line of credit Line of credit $59.00
ncing (positive), pay special dividend Special dividend $0.00
Most Recent Forecast
2016 Input 2017
$20.0 1.00% $22.00
280.0 14.00% $308.00
400.0 16.00% $352.00
$700.0 $682.00
500.0 25.00% $550.00
$1,200.0 $1,232.00
$80.0 4.00% $88.00
0.0 Draw on LOC if financing deficit $0.00
× 2017 Net fixed assets
× Pretax earnings
Basis for 2017 Forecast
× 2017 Sales
Old RE + Add. to RE
Basis for 2017 Forecast
× 2016 Sales
× 2016 Dividend
× 2017 Sales
× 2017 Sales
× 2017 Sales
× 2017 Sales
× 2017 Sales
$80.0 $88.00
500.0 Carry over from previous year $500.00
$580.0 $588.00
420.0 Carry over from previous year $420.00
200.0 $224
$620.0 $644
$1,200.0 $1,232
Check: TA − Total Liab. & Eq. = $0.00
Most Recent Forecast
2016 Input 2017
$2,000.0 110% $2,200.00
1,800.0 89.50% $1,969.00
50.0 10.00% $55.00
$150.0 $176.00
40.0 8.00% × Avg bonds $40.00
0.0 8.00% × Beginning LOC $0.00
$110.0 $136.00
44.0 40.00% $54.40
$66.0 $81.60
$20.0 110% $22.00
$0.0 Pay if financing surplus $35.60
$46.0 Net income – Dividends $24.00
Financial Deficit or Surplus
neous liabilities (accounts payable and accruals) $8.00
erm debt and common stock $0.00
$0.00
regular common dividends $59.60
$67.60
$32.00
surplus financing: $35.60
g (negative), draw on line of credit Line of credit $0.00
ncing (positive), pay special dividend Special dividend $35.60
× Pretax earnings
× 2016 Dividends
Basis for 2017 Forecast
× 2016 Sales
× 2017 Sales
× 2017 Net fixed assets
Old RE + Add. to RE
No Change
1. Balance Sheets Most Recent
2016 Input
Assets
Cash $20.0 1.00%
Accts. rec. 280.0 14.00%
Inventories 400.0 20.00%
Total CA $700.0
Net fixed assets 500.0 25.00%
Total assets $1,200.0
Liabilities and equity $0.0
Accts. pay. & accruals $80.0 4.00%
Line of credit 0.0 0.00% Draw on LOC if financin
Total CL $80.0
Long-term debt 500.0 0.00 Carry over from previo
Total liabilities $580.0
Common stock 420.0 Carry over from previo
Retained earnings 200.0
Total common equity $620.0
Total liabs. & equity $1,200.0
Check: TA − Total
2. Income Statement Most Recent
2016 Input
Sales $2,000.0 110%
Op. costs (excl. depr.) 1,800.0 90.00%
Depreciation 50.0 10.00%
EBIT $150.0
Less: Interest on LTD 40.0 8.00% × Avg bonds
Interest on LOC 0.0 8.00% × Avg LOC
Pretax earnings $110.0
Taxes (40%) 44.0 40.00%
Net income $66.0
Regular common dividends $20.0 110%
Special dividends $0.0 Pay if financin
Addition to RE $46.0 $0.00 Net income – Dividends
3. Elimination of the Financial Deficit or Surplus
Increase in spontaneous liabilities (accounts payable and accruals)
+ Increase in long-term debt and common stock
− Previous line of credit
Note: All inputs are linked to the first worksheet, "1. Mini Case", so don't make chan
If you want to see a different scenario, go the the first worksheet, "1. Mini Case", a
Manager there to make changes.
This worksheet shows how to incorporate the impact of financing feedback, which is caused if the LO
not just at the end of the year. The extra notes below show the changes from this model and the one
Case".
Financing Feeback
× 2017 Sales
Basis for 2017
× 2017 Sales
× 2017 Sales
× 2017 Sales
× 2017 Sales
Old RE +
Basis for 2017
× 2016 Sales
× 2017 Sales
× 2017 Net fixed assets
× Pretax earnings
× 2016 Dividends
+ Planned increase in retained earnings
+ After-tax operating income: EBIT (1-T)
− After-tax interest on LT debt: )NTLTD x (1-T)
− After-tax interest on previous LOC: rLOC x 0.5 x LOCt-1 x (1-T)
− Regular common dividends
Total planned increase in the retained earnings account
Increase in financing
− Increase in total assets
Amount of unadjusted deficit or surplus financing:
If there is a surplus (the financing need is positive), pay a specia
If there is a deficit (the financing need is positive), draw on
Unadjusted line
Adjustment factor (see not
Adjusted line of credit = Unadjusted LOC / Adjustment
The adjustment factor takes into account the financing feedback. The formula for the factor is:
Adjustment factor =1-[0.5 x rLOC x (1-T)]
The 0.5 in the formula is based on the assumption that the LOC will be added smoothly throughout t
be incurred on only half the new LOC. Interest is deductible for tax pursposes, so it is only the after-t
adjusted LOC.
10/28/15
Forecast
2016
$22.00
$308.00
$440.00
$770.00
$550.00
$1,320.00
$88.00
OC if financing deficit $60.45
$148.45
er from previous year $500.00
$648.45
er from previous year $420.00
$252
$672
$1,320
−otal Liab. & Eq. = $0.00
Forecast
2016
$2,200.00
$1,980.00
$55.00
$165.00
$40.00
$2.42 Note:
$122.58
$49.03
$73.55
$22.00
y if financing surplus $0.00
Net income – Dividends $51.55
$8.0
$0.0
$0.0 Note:
make changes here!
. Mini Case", and use the Scenario
sed if the LOC is added during the year and
nd the one in the first worksheet, "1. Mini
The interest on the LOC is based on the LOC's average value d
Basis for 2017 Forecast
We subtract the previous LOC because the plan does not call
Old RE + Add. to RE
Basis for 2017 Forecast
fixed assets
gs
ends
$99.0
$24.0
$0.0 Note:
$22.0
$53.0
$61.0 Note:
$120.0
−$9.
ay a special dividend: $0.0
ive), draw on the LOC:
justed line of credit = $59.0
ctor (see note below) = 0.98
/ Adjustment factor = $60.5
The increase in financing is equal to the sum of
spontaneous liabilities, planned external financing,
and the planned addition to the retained earnings
Note: interest expense is incurred on the planned LOC. Becau
equal to
(LOCt-1 + 0)/2 = 0.5*LOCt-1.
ctor is:
roughout the year, so the new interest will
y the after-tax impact that determines the
age value during the year.
oes not call for any projected LOC unless necessary.
LOC. Because the plan does not call for any LOC, the average balance is
