FINANCIAL MODELING ASSIGNMENT
INITIATING COVERAGE
OVERVIEW
As the new junior analyst in the Equity Research department at ABC Bank, your boss, the senior analyst, just
sent you the following email asking you to build a financial model for a company called Blu Containers.
From: SeniorAnalyst@[Link]
Sent: May 1, 2023 4:12 PM
To: JuniorAnalyst@[Link]
Subject: Initiating Coverage – Blu Containers
Good morning!
I hope all is well and that you are settling into your new role. I just found out that we need to pick up coverage
of a company called Blu Containers. Normally I would put together the model when we initiate coverage but I
will be traveling for the next two weeks and meeting with clients so I would like you to build the model.
I really want to make sure it’s an excellent model so I have shared my thoughts below on how you should
approach every section including the various assumptions. We can review the model in two weeks once I get
back to the office. Good luck!
COMPANY OVERVIEW
Blu Containers is a manufacturer of environmentally friendly water storage tanks made of recycled materials
that are used by a wide range of companies all over the world.
ASSUMPTIONS
The most recent statements available for Blu are from 2022. I would therefore like you to build an integrated
annual model for the Company for the next 5 years (2023-2027) using the historical data and the following
assumptions. Assume all figures are in US dollars.
Sales
• In 2022, net revenue was $594.4 per unit (gross revenue of $694.4 per unit, less freight &
warehousing costs of $100.00 per unit).
• This is a cyclical industry and the sales prices are expected to average $800 in 2023, $725 in
2024, $825 in 2025, $800 in 2026 and $750 in 2027.
• Freight & warehousing costs are expected to increase at inflation.
• Blu’s factory has a capacity of 420,000 units per year.
• The company expects sales volumes to grow at 5.0% in 2023 and 4.0% every year thereafter.
1
CFA Program Practical Skills Module: Financial Modeling
FINANCIAL MODELING ASSIGNMENT
INITIATING COVERAGE
Operating Costs
• A breakdown of the forecasted operating costs for 2023 is provided below:
Description Units Amount
Variable Costs
Raw Materials $ 226.00
Utilities $ 66.20
Total Variable Costs $ 292.20
Fixed Costs
Rent $MM 23.50
Operating Labour $MM 43.50
Other $MM 2.00
Total Fixed Costs $MM 69.00
• Operating costs are expected to grow at inflation thereafter.
• SG&A is expected to be $3.9 million in F2023 and grow at inflation thereafter.
Capex and Depreciation
• The company plans to invest $16.0 million on capex in 2023, $17.0 million in 2024, $17.3 million in
2025, $17.5 million in 2026 and $18.0 million in 2027.
• Blu uses the Straight-Line method of depreciation.
• Existing assets have a remaining useful life of 25 years while new assets will be depreciated over
30 years.
Working Capital
• Based on the historical data, please make reasonable assumptions for the working capital days
over the next five years.
Income Taxes
• The Company’s tax rate is 35%.
• The aggregate reduction in government pre-tax earnings due to timing differences between
accounting and government rules is expected to be $5 million per year for the next five years.
Debt
• The senior secured term debt and revolver pay interest at a fixed rate of 6.0%.
• Excess cash earns interest income at a rate of 1.0%.
• Amortization on senior secured term debt will be $25 million per year until the debt is repaid.
• The Company will draw on its revolver if it ever has a cash shortfall.
• The revolver has a cash sweep such that whenever a revolver balance is outstanding, all excess
cash flow after dividends and share repurchases is used to pay off the revolver.
2
CFA Program Practical Skills Module: Financial Modeling
FINANCIAL MODELING ASSIGNMENT
INITIATING COVERAGE
Equity
• The company has 14.8 million common shares outstanding and the stock recently closed at
$11.50.
• The dividend for its common shares is expected to be based on a payout ratio of 20.0% of Net
Income.
Other Assumptions
Hopefully you now have everything you need to get started. Feel free to make any other reasonable
assumptions that you may need to build the model.
I look forward to seeing the model in a couple weeks!
INSTRUCTIONS
Based on the email from your senior analyst:
1. Using one of the worksheet files provided (Beginner, Intermediate, or Advanced) and the information in
this case, build a financial model that forecasts the Company’s financial statements annually for the
next five years.
2. Create an assumptions page to enter your inputs. Please make sure the assumptions page is logical
and well organized.
3. Build scenarios to run three cases (Base/Best/Worst) on a few of the assumptions that create the most
variability on the Company’s earnings (make reasonable assumptions for the Best case and Worst
case variables).
4. Create schedules for the key items that will appear on the financial statements.
5. Summarize the key operating and financial results for each of the three cases (Base/Best/Worst).
6. Set up the model so that it is nicely formatted and printable.
3
CFA Program Practical Skills Module: Financial Modeling