Originally published: 20/04/2022 11:31
Publication number: ELQ-60715-1
View all versions & Certificate
Publication number: ELQ-60715-1
View all versions & Certificate
3 Statement Financial Model - Advanced
3 Statement Financial Model - Advanced
by Rahim Zulfiqar AliFounder & CEO - Excel Basement, Excel & POWER BI Trainer & Consultant, MBA (MIS), MCT, MSOM, Emerging Data ScientistFollow 415
Description
3 Statement Financial Model - Advanced
The worksheet includes:
1. Assumptions
2. Summary
3. Balance Sheet
4. Income Statement
5. Cash Flow Statement
6. Schedules
7. Debt Schedules
COMPANY OVERVIEW
LNC Manufacturing Inc. (“LNC” or the “Company”) is a world leader in the design, development, and marketing of industrial monitoring devices. The Company provides its consumers with a variety of cost-effective products for use on many types of machinery. The Company’s origin dates to 1942 when founder Laurence Nicern obtained his first patent for an industrial monitoring device.
FINANCIAL MODEL
You have been provided with three years of annual historical financial statements for LNC. The most recent statement available to you is fiscal 2017. Your task is to build an integrated annual model for the Company for the next 5 years (F2018-F2022) using the historical data and the following assumptions. Assume all figures are in dollars unless stated otherwise.
Sales
• LNC primarily earns revenue from the sale of industrial monitoring devices. It also supports its line of products with an auxiliary service and support business.
• In its core business, LNC recorded an average unit sales price of $14,105 in F2017. The volume and pricing in its core business is expected to grow 3.0% and 2.5% per year, respectively.
• Auxiliary sales were $574.5 million in F2017 and are expected to grow at 2.5% annually.
Operating Costs
• In F2017, 25% of the Cost of Sales was classified as variable costs and the remainder as fixed.
• Cost inflation is expected to be 2.0% per year for the next five years.
• SG&A is expected to be $615 million in F2018 and may grow at inflation thereafter.
Capex and Depreciation
• The Company is expected to spend $225 million on capex in F2018, $250 million in F2019 and $275 million for each year after. Capex is spent evenly over the course of the year.
• LNC uses the Straight-Line method of depreciation.
• Existing assets have a remaining useful life of 9 years while new assets will be depreciated over 15 years.
• Depreciation expense is included as a separate line on the Income Statement and not included in Cost of Sales or SG&A.
Working Capital
• Based on the historical data, please make up reasonable assumptions for the working capital days over the next five years.
Income Taxes
• The Company’s tax rate is 35%.
• It is expected that the Company’s pre-tax income for government purposes will be $40 million lower than the pre-tax income for accounting purposes every year in the forecast due to temporary timing differences.
Debt
• The interest rate on the fixed rate term debt and the revolving credit facility (revolver) is 5.25%.
• The variable rate term debt pays interest at a variable rate equal to LIBOR plus 225 basis points (100 basis points = 1.0%). The annual rate for LIBOR is expected to remain constant at 1.1%.
• Excess cash earns interest income at a rate of 1.0%.
• Amortization on the fixed rate term debt will be $50 million per year until the debt is repaid.
• Amortization on the variable rate term debt will be $25 million per year until it is repaid.
• Assume that debt amortization payments occur either in the middle or the end of each year.
• The Company will draw on its revolver if it ever has a cash shortfall.
• The revolver has a cash sweep such that whenever there is a revolver balance outstanding, all excess cash available after mandatory debt repayments, share issuances/repurchases and dividends goes towards paying off the revolver.
• The Company is not required to maintain a minimum cash balance.
Equity
• The Company plans to issue an additional $10 million of Preferred Shares in the middle of F2019 that will have an 8.5% dividend yield consistent with the Company’s other Preferred Shares.
• The Company plans to spend $150 million on Common Share repurchases in F2018 and another $150 million in F2019.
• LNC expects to introduce a new dividend for its Common Shares beginning in F2018. This dividend is expected to be based on a payout ratio of 25% of Net Income to Common each year.
Other
• The Company incurred significant Cost Adjustments from 2015 to 2017, mostly due to restructuring charges. Assume that these charges will not continue in the future years.
• Assume that Goodwill, Intangibles, Other Long-Term Assets, Other Long-Term Liabilities, and Other Shareholders’ Equity will remain constant over the forecast period.
Other Assumptions
• Feel free to make any other reasonable assumptions that you may need to build the model.
INSTRUCTIONS
1. Using the spreadsheet provided 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 in the model 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).
3 Statement Financial Model - Advanced
The worksheet includes:
1. Assumptions
2. Summary
3. Balance Sheet
4. Income Statement
5. Cash Flow Statement
6. Schedules
7. Debt Schedules
COMPANY OVERVIEW
LNC Manufacturing Inc. (“LNC” or the “Company”) is a world leader in the design, development, and marketing of industrial monitoring devices. The Company provides its consumers with a variety of cost-effective products for use on many types of machinery. The Company’s origin dates to 1942 when founder Laurence Nicern obtained his first patent for an industrial monitoring device.
FINANCIAL MODEL
You have been provided with three years of annual historical financial statements for LNC. The most recent statement available to you is fiscal 2017. Your task is to build an integrated annual model for the Company for the next 5 years (F2018-F2022) using the historical data and the following assumptions. Assume all figures are in dollars unless stated otherwise.
Sales
• LNC primarily earns revenue from the sale of industrial monitoring devices. It also supports its line of products with an auxiliary service and support business.
• In its core business, LNC recorded an average unit sales price of $14,105 in F2017. The volume and pricing in its core business is expected to grow 3.0% and 2.5% per year, respectively.
• Auxiliary sales were $574.5 million in F2017 and are expected to grow at 2.5% annually.
Operating Costs
• In F2017, 25% of the Cost of Sales was classified as variable costs and the remainder as fixed.
• Cost inflation is expected to be 2.0% per year for the next five years.
• SG&A is expected to be $615 million in F2018 and may grow at inflation thereafter.
Capex and Depreciation
• The Company is expected to spend $225 million on capex in F2018, $250 million in F2019 and $275 million for each year after. Capex is spent evenly over the course of the year.
• LNC uses the Straight-Line method of depreciation.
• Existing assets have a remaining useful life of 9 years while new assets will be depreciated over 15 years.
• Depreciation expense is included as a separate line on the Income Statement and not included in Cost of Sales or SG&A.
Working Capital
• Based on the historical data, please make up reasonable assumptions for the working capital days over the next five years.
Income Taxes
• The Company’s tax rate is 35%.
• It is expected that the Company’s pre-tax income for government purposes will be $40 million lower than the pre-tax income for accounting purposes every year in the forecast due to temporary timing differences.
Debt
• The interest rate on the fixed rate term debt and the revolving credit facility (revolver) is 5.25%.
• The variable rate term debt pays interest at a variable rate equal to LIBOR plus 225 basis points (100 basis points = 1.0%). The annual rate for LIBOR is expected to remain constant at 1.1%.
• Excess cash earns interest income at a rate of 1.0%.
• Amortization on the fixed rate term debt will be $50 million per year until the debt is repaid.
• Amortization on the variable rate term debt will be $25 million per year until it is repaid.
• Assume that debt amortization payments occur either in the middle or the end of each year.
• The Company will draw on its revolver if it ever has a cash shortfall.
• The revolver has a cash sweep such that whenever there is a revolver balance outstanding, all excess cash available after mandatory debt repayments, share issuances/repurchases and dividends goes towards paying off the revolver.
• The Company is not required to maintain a minimum cash balance.
Equity
• The Company plans to issue an additional $10 million of Preferred Shares in the middle of F2019 that will have an 8.5% dividend yield consistent with the Company’s other Preferred Shares.
• The Company plans to spend $150 million on Common Share repurchases in F2018 and another $150 million in F2019.
• LNC expects to introduce a new dividend for its Common Shares beginning in F2018. This dividend is expected to be based on a payout ratio of 25% of Net Income to Common each year.
Other
• The Company incurred significant Cost Adjustments from 2015 to 2017, mostly due to restructuring charges. Assume that these charges will not continue in the future years.
• Assume that Goodwill, Intangibles, Other Long-Term Assets, Other Long-Term Liabilities, and Other Shareholders’ Equity will remain constant over the forecast period.
Other Assumptions
• Feel free to make any other reasonable assumptions that you may need to build the model.
INSTRUCTIONS
1. Using the spreadsheet provided 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 in the model 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).
This Best Practice includes
1 Excel File
