Forecast the preliminary balance sheets and income statements in Column H. Don't include
ang line of credit or special dividend in the preliminary forecast.
After completing the preliminary forecast of the balance sheets and income statement, go to
=the area below the preliminary forecast and identify the financing deficit or surplus. Then use
â– Excel's IF statements to specify the amount of ang new line of credit OR special dividend
â– (you should not have a new line of credit AND a special dividend, only one or the other).
After specifying the amounts of the special dividend or line of credit, create a second column
(1) for the final forecast next to the column for the preliminary forecast (H). In this final
forecast, be sure to include the effect of the special dividend or line of credit.
Income Statements:
= (December 31, in thousands of d
Sales
- Expenses (excluding depr. & am
Depreciation and Amortization
EBIT
Interest expense on long-term d
Interest expense on line of credi
EBT
= Tazes (25%)
â– Net Income
2023
$455,150
$386,878
$14,565
$53,708
2024
Preliminary
Input forecast
ratios
(doesn't
2023
Histori
2024
cal
ratios
Forecasting
basis
Growth
% of sales
% of fized assets
$11,880 erest rate x average debt during gear
$0
$41,828
$10,457
$31,371
2024 Final
forecast
(includes
special
Common dividends (regular divi $12,554
Growth
8.00%
Special dividends
Zero in preliminary forecast
Addition to retained earnings
$18,817
Balance Sheets
2023
=(December 31, in thousands of d
Histori
2024
2023
ratios
cal Forecasting Input
basis
ratios
2024
Preliminary
forecast
(doesn't
2024 Final
forecast
(includes
special
Assels-
Cash
$18,206
% of sales
Accounts Receivable
$100,133
% of sales
Inventories
$45,515
% of sales
= Total current assets
$163,854
- Fixed assets
$182,060
% of sales
Total assets
$345,914
Liabilities and equity
= Accounts payable
$31,861
Accruals
$27,309
% of sales
% of sales
Line of credit
$0 Zero in preliminary forecast
Total current liabilities
$59,170
Long-term debt
$120,000
Total liabilities
$179,170
Common stock
$60,000
Previous
Previous
Retained Earnings
$106,745 vious. Addition to retained earnings
Total common equity
$166,745
Total liabilities and equity
$345,914