Practical Skills Module: Financial Modeling

Financial Model Worksheet - Vertical Orientation Powered by:

 0
Summary Outputs

SUMMARY VALUES - BASE CASE

Projected
($ Millions) Trend -3A -2A -1A 0 1 2 3 4
Income Statement Items
Net Revenue
Growth

EBITDA
Margin
Growth

Net Income
Margin
Growth

SUMMARY VALUES - BEST CASE

Projected
($ Millions) Trend -3A -2A -1A 0 1 2 3 4
Income Statement Items
Net Revenue
Growth

EBITDA
Margin
Growth

Net Income
Margin
Growth

SUMMARY VALUES - WORST CASE

Projected
($ Millions) Trend -3A -2A -1A 0 1 2 3 4
Income Statement Items
Net Revenue
Growth

EBITDA
Margin
Growth

Net Income
Margin
Growth
Inputs and Assumptions

GENERAL EQUITY

- First year of forecast in financial model: Stock Price - 07/23/24

- Three scenarios have been used for Inflation, Product Pricing Fully Diluted Shares O/S (MM) - Dec. 31, -1
and sales volume growth Common Dividend Payout Rate

PRODUCT PRICING OPERATIONS

- The product pricing cases used in the model are as follows:

Pricing Case 0-4 Annual Factory Capacity (000's units):
Base Case Research Forecast
Best Case
Worst Case

COSTS INTEREST RATES

Cost Item 0 Increases

Raw Materials per Unit Inflation on a unit basis Interest earned on Cash balances:
Utilities per Unit Inflation on a unit basis Interest rate on Bank Revolver:
Rent MM Inflation on a total basis Interest rate on Senior Secured Term Debt:
Operating Labour MM Inflation on a total basis
Other MM Inflation on a total basis
SG&A MM Inflation on a total basis

DEPRECIATION TAXES

- Depreciation Methodology Used: Straight Line

- Years remaining for depreciation of existing assets: Tax rate assumed in the model:
- Years used for depreciation of new assets: Additional tax assumptions in "Other Assumptions" box
 0
Inputs and Assumptions

OTHER ASSUMPTIONS 0 1 2 3 4

Income Statement
Cost Adjustments - Gain/(Loss) MM
Cash Flow Statement
Investing Activities - Other MM
Capital Expenditures MM

Income Tax
Reduction in EBT for timing differences MM

Working Capital Days

Accounts Receivable days
Inventories days
Prepaid Expenses days
Other Assets days
Accounts Payable days
Other Liabilities days
Change in Debt & Equity
Senior Term Debt Issuance / (Repayment) MM
Common Stock Issuance / (Buy-back) MM
 0
Economic and Sales Scenarios

SCENARIO SWITCH: 0 1 2 3 4

ECONOMIC SCENARIOS

Cost Inflation
Base Case
Best Case
Worst Case

SALES SCENARIOS

Sales Price - $/Unit

Base Case

Sales Volume Growth

Base Case 5.0% 4.0% 4.0% 4.0% 4.0%
Best Case 5.0% 4.0% 5.0% 5.0% 4.0%
Worst Case 4.0% 4.0% 3.0% 3.0% 2.0%
 0
Revenue Schedule

Projected
-3A -2A -1A 0 1 2 3 4

Pricing
Gross Sales Price ($/unit) 694.4
Cost Inflation (%)
Freight & Warehousing ($/unit) 100.0
Net Sales Price ($/unit) 594.4

Sales Volume
Annual Factory Capacity (000's units) - - - -

Sales Volume Growth (%)

Annual Sales Volume (000's units) 344.5
Implied Operating Rate

Revenue
Gross Revenue ($ MM) 239.2
Freight & Warehousing ($ MM) 34.4
Net Revenue ($ MM) $204.8

0
Costs of Production Schedule

Projected
-3A -2A -1A 0 1 2 3 4

Annual Sales Volume (000's units) 344.5 0.0 0.0 0.0 0.0 0.0
Cost Inflation (%) 0.0% 0.0% 0.0% 0.0%

COSTS PER UNIT

Variable Costs
Raw Materials ($/unit)
Utilities ($/unit)
 Total Variable Costs ($/unit)

Fixed Costs
Rent ($/unit)
Operating Labour ($/unit)
Other ($/unit)
Total Fixed Costs ($/unit)

Total Costs ($/unit)

COSTS IN MILLIONS
Variable Costs
Raw Materials ($ MM)
Utilities ($ MM)
Total Variable Costs ($ MM)

Fixed Costs
Rent ($ MM)
Operating Labour ($ MM)
Other ($ MM)
Total Fixed Costs ($ MM)

Total Costs ($ MM)

0
Income Statement
($ Millions)
Projected
-3A -2A -1A 0 1 2 3 4
Sales Volume (000's units) 344.5
Revenue
Gross Revenue 244.8 269.3 239.2
Freight & Warehousing 31.3 32.7 34.4
Net Revenue 213.5 236.6 204.8

Cost of Sales 159.9 164.6 167.9

S,G & A 3.4 3.6 3.8
Total Costs 163.3 168.2 171.7

Cost Adjustments - Gain/(Loss) 0.0 0.0 0.0

EBITDA 50.2 68.4 33.1
 Depreciation 15.4 15.5 15.8
EBIT 34.8 52.9 17.3
Interest Expense 15.0 15.0 14.0
EBT 19.8 37.9 3.3
Current Income Taxes 3.0 8.0 0.0
Deferred Income Taxes 2.7 6.2 1.1
Total Income Taxes 5.7 14.2 1.1

Net Income $14.1 $23.7 $2.2

Margins
EBITDA Margin 23.5% 28.9% 16.2%
EBIT Margin 16.3% 22.4% 8.4%
Return on Equity 0.9%

0
Cash Flow Statement
($ Millions)
Projected
-3A -2A -1A 0 1 2 3 4
Operating Activities
Net Income 14.1 23.7 2.2
Depreciation & Amortization 15.4 15.5 15.8
Deferred Income Taxes 2.7 6.2 1.1
Changes in working capital 0.0 0.0 0.0
Operating Cash Flow 32.2 45.4 19.1

Investing Activities
CAPEX (14.1) (15.0) (15.5)
Other (5.0) 4.0 3.0
Investing Cash Flow (19.1) (11.0) (12.5)

Financing Activities
Revolver Issuance / (Repayment) 0.0 0.0 0.0
Term Debt Issuance / (Repayment) (25.0) (25.0) (25.0)
Common Shares Issuance/ (Buy-Back) 0.0 0.0 0.0
Common Dividends (2.8) (4.7) (2.4)
Financing Cash Flow (27.8) (29.7) (27.4)
Change in the Cash Position (14.7) 4.7 (20.8)
Beginning Cash 31.1 16.4 21.1
Ending Cash 16.4 21.1 0.3

0
Balance Sheet
($ Millions)
Projected
-3A -2A -1A 0 1 2 3 4

ASSETS
Cash 16.4 21.1 0.3
Accounts Receivable 27.0 27.8 28.3
Inventory 36.5 36.1 35.1
Prepaid Expenses 14.6 14.4 14.9
Other 1.4 1.8 1.2
Total Current Assets 95.9 101.2 79.8

Net PP&E 398.5 398.0 397.7

Other 19.0 15.0 12.0
Total Long Term Assets 417.5 413.0 409.7

Total Assets $513.4 $514.2 $489.5

LIABILITIES AND EQUITY

Bank Debt - Revolver 0.0 0.0 0.0
Accounts Payable 18.3 18.7 18.2
Other 4.7 4.9 4.8
Total Current Liabilities 23.0 23.6 23.0

Deferred Income Taxes 0.7 6.9 8.0

Senior Secured Term Debt 250.0 225.0 200.0
Total Long Term Liabilities 250.7 231.9 208.0

Total Liabilities 273.7 255.5 231.0

Common Shares 120.0 120.0 120.0

Retained Earnings 119.7 138.7 138.5
Shareholder's Equity 239.7 258.7 258.5
Total Liabilities and Equity $513.4 $514.2 $489.5

Check 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000

0
Depreciation Schedule
($ Millions)
Projected
-3A -2A -1A 0 1 2 3 4

Years Remaining Existing Assets:

Depreciation Years on New Assets:

Depreciation to Existing Assets $0.0 $0.0 $0.0 $0.0

CAPEX
0
1
2
3
4

Total Depreciation

0
Income Tax Schedule
($ Millions)
Projected
Tax Rate 0 1 2 3 4

Accounting EBT (as is on I/S) A

Less: Reduction in EBT for timing differences (1)

Government EBT B

Accounting Taxes (0% of A)

 Taxes as Appearing on Income Statement
Current Tax (0% of B)
Increase (Decrease) in Deferred Income Taxes
Total Income Taxes (Same as Accounting Taxes Above)

(1) Assumes aggregate reduction in government pre-tax earnings due to timing differences between accounting and government rules.

0
Working Capital Schedule
($ Millions)
Projected
-3A -2A -1A 0 1 2 3 4

Days per Year (days)

Income Statement Items

Net Revenue ($ MM)
Cost of Sales ($ MM)

Days In
Accounts Receivable (days)
Inventories (days)
Prepaid Expenses (days)
Other Assets (days)
Accounts Payable (days)
Other Liabilities (days)

Account Balances
Accounts Receivable ($ MM)
Inventories ($ MM)
Prepaid Expenses ($ MM)
Other Assets ($ MM)
Accounts Payable ($ MM)
Other Liabilities ($ MM)
Net Working Capital ($ MM)

Change in Working Capital ($ MM)

0
 Debt and Interest Schedule
($ Millions)
Projected
-1A 0 1 2 3 4
FINANCING COMPONENT
Cash
Amount Outstanding - Beginning
Change in Cash
Amount Outstanding - Ending
Interest Rate
Annual Interest Income

Revolver
Operating Cash Flow
Investing Cash Flow
Mandatory Debt Repayments
Common Stock Issuance / (Buy-back)
Dividends
FCF After Mandatory Debt Repayment and Dividend

Revolver Outstanding - Beginning

Additions / (Repayments)
Revolver Outstanding - Ending
Interest Rate
Annual Interest Expense

Senior Secured Term Debt

Amount Outstanding - Beginning
Additions / (Repayments)
Amount Outstanding - Ending
Interest Rate
Annual Interest Expense

Net Interest Expense

0
Shareholders' Equity Schedule
($ Millions)
Projected
-1A 0 1 2 3 4

Common Shares
Amount Outstanding - Beginning
New Issuance / (Buy-Back)
Amount Outstanding - Ending
Dividend Payout Rate 0.0% 0.0% 0.0% 0.0%
Net Income
Common Dividend

Retained Earnings
Amount Outstanding - Beginning
Net Income
Common Dividend
Amount Outstanding - Ending