This article demonstrates the structure of corporate financial models in the case of food and beverage companies. These companies generally have stable returns and growth. Models for food and beverage all include analysis or historic relative to projected ROIC and valuation with normalised cash flow. Each of the models illustrates the effects of different terminal value methods and assumptions. The first model available for download is a model of Carlsberg beer. Other models include Flower Foods and Aziza. The file with Carlsberg analysis is available by pressing on the button below. I have tried to include scenario analysis as well as various valuation analysis in this file.
The Carlsberg model was developed for a class in Denmark where the objective was to build a basic model and work through valuation. This model is presented as a comprehensive analysis that illustrates normalised cash flow in the terminal period of the DCF model, issues with measuring and evaluating ROIC, scenario analysis and effective presentation of valuation. As with all of the corporate models, the general process involves the following steps: (1) acquire historic financial data (this is why I am obsessive about reading data from the internet and pdf); (2) create assumptions from historic data and analyse the historic ROIC; (3) work through forecasts of key items of revenues and operating expenses and try to understand fixed and variable operating cost; (4) evaluate changes in working capital and try to understand cap exp; (5) work through depreciation and plant balances using the historic levels as a starting point.
This model does not go all the way to net income and compute a balance sheet as it was used for demonstrating how to construct a LBO and how to work through valuation issues. Instead, the valuation section in this model does include tricky adjustments to arrive at normalised cash flow. You can think of the adjustments to arrive at stabalised cash flow as working through the components of cash flow — the EBITDA, the Capital Expenditures required to earn EBITDA, the Working Capital Adjustment to EBITDA and Deferred Tax. Adjustments to arrive at stabalised EBITDA are made through analysis of ROIC and use of the INTERPOLATE function. Adjustments to capital expenditures are made through depreciation analysis with growth analysis. Adjustments to working capital change are made using the formula WC/EBITDA x EBITDA in Terminal Year x [g /(1+g)] and stable capital expenditures that account for historic and future growth are modeled using a user defined function name STABLE_CAPEXP.
The model can also be used together with the cost of capital analysis for beer companies where a number of different companies are downloaded and alternative ways of computing the cost of capital are analysed. The model is described in the video below (the sound level is low and you may have to use headphones or something, I am fixing this in other videos).
Other models included below are for a chicken company and for Flower Foods. Note how you can stable trends in growth and ROIC for Flower Foods.