Blu Containers Modeling Complete Version, Assignments of Corporate Finance

Download Blu Containers Modeling Complete Version and more Assignments Corporate Finance in PDF only on Docsity!

Practical Skills Module: Financial Modeling

Blu Containers Company

April 8, 2025

Financial Model Solution - Vertical Orientation Powered by:

C:\Documents\Blue Containers Model.xls Page 2 of 15 04/08/2025 21:17: Blu Containers Company

Summary Outputs

SUMMARY VALUES - BASE CASE Projected ($ Millions) Trend 2020A 2021A 2022A 2023 2024 2025 2026 2027 Income Statement Items Net Revenue $213 $237 $205 $252 $234 $281 $281 $ Growth 10.8% (13.4%) 23.3% (7.5%) 20.4% (0.0%) (4.6%) EBITDA $50 $68 $33 $74 $47 $86 $77 $ Margin 23.5% 28.9% 16.2% 29.3% 20.2% 30.7% 27.3% 20.3% Growth 36.3% (51.6%) 123.2% (36.2%) 83.5% (11.3%) (29.2%) Net Income $14 $24 $2 $30 $13 $39 $33 $ Margin 6.6% 10.0% 1.1% 11.8% 5.5% 13.8% 11.8% 7.2% Growth 68.1% (90.7%) 1250.1% (56.6%) 201.2% (14.1%) (41.7%) SUMMARY VALUES - BEST CASE Projected ($ Millions) Trend 2020A 2021A 2022A 2023 2024 2025 2026 2027 Income Statement Items Net Revenue $213 $237 $205 $264 $245 $297 $300 $ Growth 10.8% (13.4%) 29.0% (7.4%) 21.5% 1.1% (6.3%) EBITDA $50 $68 $33 $86 $59 $102 $95 $ Margin 23.5% 28.9% 16.2% 32.4% 23.9% 34.3% 31.7% 25.1% Growth 36.3% (51.6%) 158.3% (31.6%) 74.4% (6.7%) (25.9%) Net Income $14 $24 $2 $37 $20 $49 $46 $ Margin 6.6% 10.0% 1.1% 14.1% 8.3% 16.6% 15.2% 10.7% Growth 68.1% (90.7%) 1594.2% (45.3%) 141.8% (7.6%) (33.8%) SUMMARY VALUES - WORST CASE Projected ($ Millions) Trend 2020A 2021A 2022A 2023 2024 2025 2026 2027 Income Statement Items Net Revenue $213 $237 $205 $238 $220 $263 $260 $ Growth 10.8% (13.4%) 16.4% (7.7%) 19.3% (1.0%) (5.9%) EBITDA $50 $68 $33 $61 $34 $68 $57 $ Margin 23.5% 28.9% 16.2% 25.5% 15.4% 26.0% 21.9% 14.0% Growth 36.3% (51.6%) 83.8% (44.3%) 101.5% (16.4%) (40.1%) Net Income $14 $24 $2 $21 $4 $26 $20 $ Margin 6.6% 10.0% 1.1% 8.9% 1.9% 10.1% 7.6% 2.1% Growth 68.1% (90.7%) 864.8% (80.8%) 548.6% (25.6%) (74.1%)

Blu Containers Company Inputs and Assumptions OTHER ASSUMPTIONS 2023 2024 2025 2026 2027 Income Statement Cost Adjustments - Gain/(Loss) MM $0.0 $0.0 $0.0 $0.0 $0. Cash Flow Statement Investing Activities - Other MM $0.0 $0.0 $0.0 $0.0 $0. Capital Expenditures MM $16.0 $17.0 $17.3 $17.5 $18. Income Tax Reduction in EBT for timing differences MM $5.0 $5.0 $5.0 $5.0 $5. Working Capital Days Accounts Receivable days 48.0 44.0 40.0 40.0 40. Inventories days 70.0 65.0 60.0 60.0 55. Prepaid Expenses days 30.0 30.0 30.0 30.0 30. Other Assets days 3.0 3.0 3.0 3.0 3. Accounts Payable days 40.0 40.0 40.0 40.0 40. Other Liabilities days 10.0 10.0 10.0 10.0 10. Change in Debt & Equity Senior Term Debt Issuance / (Repayment) MM ($25.0) ($25.0) ($25.0) ($25.0) ($25.0) Common Stock Issuance / (Buy-back) MM $0.0 $0.0 $0.0 $0.0 $0.

Blu Containers Company Economic and Sales Scenarios SCENARIO SWITCH: (^1 2023 2024 2025 2026 ) ECONOMIC SCENARIOS Cost Inflation 2.0% 2.0% 2.0% 2.5% 2.5% Base Case (^) 2.0% 2.0% 2.0% 2.5% 2.5% Best Case (^) 1.8% 1.8% 1.8% 2.0% 2.0% Worst Case 2.5% 2.5% 2.5% 2.5% 2.5% SALES SCENARIOS Sales Price - $/Unit 800.0 725.0 825.0 800.0 750. Base Case 800.0 725.0 825.0 800.0 750. Best Case: +4.0% 832.0 754.0 858.0 832.0 780. Worst Case: -4.0% 768.0 696.0 792.0 768.0 720. Sales Volume Growth 5.0% 4.0% 4.0% 4.0% 4.0% 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%

Blu Containers Company Costs of Production Schedule Projected 2020A 2021A 2022A 2023 2024 2025 2026 2027 Annual Sales Volume (000's units) 344.5 361.7 376.2 391.2 406.9 420. Cost Inflation (%) 2.0% 2.0% 2.5% 2.5% COSTS PER UNIT Variable Costs Raw Materials ($/unit) 226.0 230.5 235.1 241.0 247. Utilities (^) ($/unit) 66.2 67.5 68.9 70.6 72. Total Variable Costs (^) ($/unit) 292.2 298.0 304.0 311.6 319. Fixed Costs Rent ($/unit) 65.0 63.7 62.5 61.6 61. Operating Labour (^) ($/unit) 120.3 118.0 115.7 114.0 113. Other (^) ($/unit) 5.5 5.4 5.3 5.2 5. Total Fixed Costs ($/unit) 190.8 187.1 183.5 180.9 179. Total Costs (^) ($/unit) $483.0 $485.1 $487.5 $492.5 $499. COSTS IN MILLIONS Variable Costs Raw Materials ($ MM) 81.7 86.7 92.0 98.1 103. Utilities ($ MM) 23.9 25.4 26.9 28.7 30. Total Variable Costs ($ MM) 105.7 112.1 118.9 126.8 134. Fixed Costs Rent ($ MM) 23.5 24.0 24.4 25.1 25. Operating Labour ($ MM) 43.5 44.4 45.3 46.4 47. Other ($ MM) 2.0 2.0 2.1 2.1 2. Total Fixed Costs ($ MM) 69.0 70.4 71.8 73.6 75. Total Costs ($ MM) $174.7 $182.5 $190.7 $200.4 $209.

Blu Containers Company Income Statement ($ Millions) Projected 2020A 2021A 2022A 2023 2024 2025 2026 2027 Sales Volume (000's units) 344.5 361.7 376.2 391.2 406.9 420. Revenue Gross Revenue 244.8 269.3 239.2 289.4 272.7 322.7 325.5 315. Freight & Warehousing 31.3 32.7 34.4 36.9 39.1 41.5 44.3 46. Net Revenue 213.5 236.6 204.8 252.5 233.6 281.2 281.2 268. Cost of Sales 159.9 164.6 167.9 174.7 182.5 190.7 200.4 209. S,G & A 3.4 3.6 3.8 3.9 4.0 4.1 4.2 4. Total Costs 163.3 168.2 171.7 178.6 186.5 194.8 204.5 213. Cost Adjustments - Gain/(Loss) 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0. EBITDA 50.2 68.4 33.1 73.9 47.1 86.5 76.7 54. Depreciation 15.4 15.5 15.8 16.2 16.7 17.3 17.9 18. EBIT 34.8 52.9 17.3 57.7 30.4 69.2 58.8 35. Interest Expense 15.0 15.0 14.0 12.0 10.6 9.5 7.6 6. EBT 19.8 37.9 3.3 45.7 19.8 59.7 51.3 29. Current Income Taxes 3.0 8.0 0.0 14.2 5.2 19.1 16.2 8. Deferred Income Taxes 2.7 6.2 1.1 1.8 1.8 1.8 1.8 1. Total Income Taxes 5.7 14.2 1.1 16.0 6.9 20.9 17.9 10. Net Income $14.1 $23.7 $2.2 $29.7 $12.9 $38.8 $33.3 $19. Margins EBITDA Margin 23.5% 28.9% 16.2% 29.3% 20.2% 30.7% 27.3% 20.3% EBIT Margin 16.3% 22.4% 8.4% 22.9% 13.0% 24.6% 20.9% 13.4% Return on Equity 0.9% 10.5% 4.4% 12.0% 9.5% 5.3%

Blu Containers Company

Balance Sheet ($ Millions)

 - 2020A 2021A 2022A Projected 

• Cash 16.4 21.1 0.3 0.0 0.0 0.0 1.4 0. ASSETS
• Accounts Receivable 27.0 27.8 28.3 33.2 28.1 30.8 30.8 29.
• Inventory 36.5 36.1 35.1 33.5 32.4 31.4 32.9 31.
• Prepaid Expenses 14.6 14.4 14.9 14.4 15.0 15.7 16.5 17.
• Other 1.4 1.8 1.2 1.4 1.5 1.6 1.6 1.
• Total Current Assets 95.9 101.2 79.8 82.5 76.9 79.4 83.3 79.
• Net PP&E 398.5 398.0 397.7 397.5 397.8 397.8 397.4 397.
• Other 19.0 15.0 12.0 12.0 12.0 12.0 12.0 12.
• Total Long Term Assets 417.5 413.0 409.7 409.5 409.8 409.8 409.4 409.
• Total Assets $513.4 $514.2 $489.5 $492.0 $486.7 $489.2 $492.7 $488.
• Bank Debt - Revolver 0.0 0.0 0.0 1.1 7.7 1.2 0.0 2. LIABILITIES AND EQUITY
• Accounts Payable 18.3 18.7 18.2 19.1 19.9 20.9 22.0 23.
• Other 4.7 4.9 4.8 4.8 5.0 5.2 5.5 5.
• Total Current Liabilities 23.0 23.6 23.0 25.0 32.7 27.4 27.4 31.
• Deferred Income Taxes 0.7 6.9 8.0 9.8 11.5 13.3 15.0 16.
• Senior Secured Term Debt 250.0 225.0 200.0 175.0 150.0 125.0 100.0 75.
• Total Long Term Liabilities 250.7 231.9 208.0 184.8 161.5 138.3 115.0 91.
• Total Liabilities 273.7 255.5 231.0 209.8 194.2 165.6 142.4 123.
• Common Shares 120.0 120.0 120.0 120.0 120.0 120.0 120.0 120.
• Retained Earnings 119.7 138.7 138.5 162.3 172.6 203.6 230.3 245.
• Shareholder's Equity 239.7 258.7 258.5 282.3 292.6 323.6 350.3 365.
• Total Liabilities and Equity $513.4 $514.2 $489.5 $492.0 $486.7 $489.2 $492.7 $488. - Check 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.

Blu Containers Company Depreciation Schedule ($ Millions) Projected 2020A 2021A 2022A 2023 2024 2025 2026 2027 Years Remaining Existing Assets: 25 Depreciation Years on New Assets: 30 Depreciation to Existing Assets $15.9 $15.9 $15.9 $15.9 $15. CAPEX 2023 $16.0 0.3 0.5 0.5 0.5 0. 2024 $17.0 0.3 0.6 0.6 0. 2025 $17.3 0.3 0.6 0. 2026 $17.5 0.3 0. 2027 $18.0 0. Total Depreciation $16.2 $16.7 $17.3 $17.9 $18.

Blu Containers Company Working Capital Schedule ($ Millions) Projected 2020A 2021A 2022A 2023 2024 2025 2026 2027 Days per Year (^) (days) 365 365 365 366 365 365 365 Income Statement Items Net Revenue ($ MM) 236.6 204.8 252.5 233.6 281.2 281.2 268. Cost of Sales ($ MM) 164.6 167.9 174.7 182.5 190.7 200.4 209. Days In Accounts Receivable (days) 42.9 50.4 48.0 44.0 40.0 40.0 40. Inventories (^) (days) 80.1 76.3 70.0 65.0 60.0 60.0 55. Prepaid Expenses (^) (days) 31.9 32.4 30.0 30.0 30.0 30.0 30. Other Assets (^) (days) 4.0 2.6 3.0 3.0 3.0 3.0 3. Accounts Payable (^) (days) 41.5 39.6 40.0 40.0 40.0 40.0 40. Other Liabilities (^) (days) 10.9 10.4 10.0 10.0 10.0 10.0 10. Account Balances Accounts Receivable ($ MM) 27.8 28.3 33.2 28.1 30.8 30.8 29. Inventories ($ MM) 36.1 35.1 33.5 32.4 31.4 32.9 31. Prepaid Expenses ($ MM) 14.4 14.9 14.4 15.0 15.7 16.5 17. Other Assets ($ MM) 1.8 1.2 1.4 1.5 1.6 1.6 1. Accounts Payable (^) ($ MM) 18.7 18.2 19.1 19.9 20.9 22.0 23. Other Liabilities (^) ($ MM) 4.9 4.8 4.8 5.0 5.2 5.5 5. Net Working Capital (^) ($ MM) 56.5 56.5 58.6 52.0 53.3 54.4 51. Change in Working Capital (^) ($ MM) $0.0 ($2.1) $6.6 ($1.3) ($1.1) $3.

Blu Containers Company Debt and Interest Schedule ($ Millions) Projected 2022A 2023 2024 2025 2026 2027 FINANCING COMPONENT Cash Amount Outstanding - Beginning 0.3 0.0 0.0 0.0 1. Change in Cash (0.3) 0.0 0.0 1.4 (1.4) Amount Outstanding - Ending $0.3 $0.0 $0.0 $0.0 $1.4 $0. Interest Rate 1.0% 1.0% 1.0% 1.0% 1.0% Annual Interest Income $0.0 $0.0 $0.0 $0.0 $0. Revolver Operating Cash Flow 45.6 37.9 56.6 51.8 42. Investing Cash Flow (16.0) (17.0) (17.3) (17.5) (18.0) Mandatory Debt Repayments (25.0) (25.0) (25.0) (25.0) (25.0) Common Stock Issuance / (Buy-back) 0.0 0.0 0.0 0.0 0. Dividends (5.9) (2.6) (7.8) (6.7) (3.9) FCF After Mandatory Debt Repayment and Dividend ($1.4) ($6.7) $6.5 $2.6 ($4.0) Revolver Outstanding - Beginning 0.0 1.1 7.7 1.2 0. Additions / (Repayments) 1.1 6.7 (6.5) (1.2) 2. Revolver Outstanding - Ending $0.0 $1.1 $7.7 $1.2 $0.0 $2. Interest Rate 6.0% 6.0% 6.0% 6.0% 6.0% Annual Interest Expense $0.0 $0.1 $0.5 $0.1 $0. Senior Secured Term Debt Amount Outstanding - Beginning 200.0 175.0 150.0 125.0 100. Additions / (Repayments) (25.0) (25.0) (25.0) (25.0) (25.0) Amount Outstanding - Ending $200.0 $175.0 $150.0 $125.0 $100.0 $75. Interest Rate 6.0% 6.0% 6.0% 6.0% 6.0% Annual Interest Expense $12.0 $10.5 $9.0 $7.5 $6. Net Interest Expense $12.0 $10.6 $9.5 $7.6 $6.