Sales-based Pro Forma Development

A sales-based proforma company model helps in forecasting a company’s financial performance based on predicted future sales. This model plays a crucial role in strategic planning, providing insights for making informed business decisions. It uses various assumptions regarding sales growth and related costs and is essential for capital budgeting, financial planning, and for assessing the impact of different operational strategies.

By applying  the principles learned in financial statement analysis and corporate issuer modules, we need to demonstrate building a financial model using Excel, using financial information from a hypothetical company, Diego Holdings Limited

Company (Diageo) Overview

Diageo Holdings Ltd (Diageo), a UK-based company, primarily operates in the alcoholic beverage industry. After providing a brief overview of the company, we will focus on constructing pro forma income statements, statements of cash flows, and balance sheets. Data sources for this example include the company’s fiscal years ended 30 June 2021 and 2020.

Diageo, whose reporting year ends on 30 June, operates and reports two main business segments:

1. Spirits & Liqueurs. This division comprises a diverse portfolio of premium spirits and liqueurs, including well-known brands such as Johnnie Walker, Smirnoff, Baileys, and Captain Morgan. This segment represented approximately 78 percent of FY2021 revenue and 88 percent of total current operating profit. Current operating profit is a non-IFRS measure reported by Diageo, equal to IFRS operating profit, excluding items related to discontinued brands or infrequent or immaterial items, such as impairment or litigation provisions.
2. Beer & Wine. This segment includes a range of beer and wine products, with major brands such as Guinness, Tanqueray, and Blossom Hill. The segment represented approximately 22 percent of FY2021 revenue and 12 percent of current operating profit.

Segment Financial Information

Segment financial information for Diageo is summarized in the table below.

$$\begin{align}&\begin{array}{l|c|c|c}
\textbf{Revenue} & \textbf{2020} & \textbf{2021} & \textbf{2022} \\
\textbf{(GBP millions)} & & & \\
\hline
\text{Spirits} & 1,700 & 1,680 & 1,650 \\
\text{& Liqueurs} & & & \\
\hline
\text{Beer & Wine} & 500 & 480 & 470 \\
\hline
\textbf{Total Revenues} & \textbf{2,200} & \textbf{2,160} & \textbf{2,120} \\\end{array}\\&\begin{array}{l|c|c|c}\\\textbf{Current Operating Profit (GBP millions)}\end{array}\\ &\begin{array}{l|c|c|c}\text{Spirits} & 475 & 470 & 462 \\
\text{& Liqueurs} & & & \\
\hline
\text{Beer & Wine} & 65 & 58 & 63 \\
\hline
\text{Corporate-level} & -25 & -28 & -26 \\
\text{costs} & & & \\
\hline
\textbf{Total Current} & \textbf{515} & \textbf{500} & \textbf{499} \\\end{array}\\ &\begin{array}{l|c|c|c}\textbf{Current Operating Profit Margins}\end{array}\\ &\begin{array}{l|c|c|c}\text{Spirits} & 27.9\% & 28.0\% & 28.0\% \\
\text{& Liqueurs} & & & \\
\hline
\text{Beer & Wine} & 13.0\% & 12.1\% & 13.4\% \\
\hline
\text{Corporate-level} & -1.1\% & -1.3\% & -1.2\% \\
\text{costs} & & & \\
\text{(% of total} & & & \\
\text{revenue)} & & & \\
\hline
\textbf{Total Current} & \textbf{23.4%} & \textbf{23.1%} & \textbf{23.5%} \\
\textbf{Operating Margin} & & & \\
\end{array}\end{align}
$$

Looking at the above information, the company’s largest and most profitable business segment is the Spirits & Liquors segment, which had a current operating profit margin of approximately 28 percent (= GBP462 million/GBP1,650 million) in fiscal year 2022.

Construction of Pro Forma Income Statement

Constructing pro forma income statements involves four main forecasting steps: Revenue Forecasting, Cost of goods sold (COGS) forecasting, other operating expenses forecasting, and non-operating items forecasting.

Revenue Forecasting

Begin by estimating the revenue growth and future revenue for the company. Analyze the historical data to understand the past growth trends. Consider the market growth, market share, or growth relative to GDP to make an accurate projection.

For each segment, changes in revenue are influenced by volume, pricing, and foreign currency estimates based on historical trends adjusted for expected deviations. Pricing changes include not only individual product price adjustments but also variations in the product mix, which involve selling different quantities of higher- and lower-priced products. Revenue changes due to volume or price/mix are considered organic growth and are shown separately from the impact of acquisitions, divestiture, and foreign exchange (forex impact in the model).

Consider the following historical and projected Information for the Spirits & Liqueurs Segment of the Diego company.

$$
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{Spirits &} & 1,720 & 1,710 & 1,700 & 1,680 & 1,650 & 1,766 & 1,872 \\
\text{Liquors Segment} & & & & & & & \\
\text{Revenues} & & & & & & & \\
\hline
\text{YoY %} & 0.6\% & -0.6\% & -0.6\% & -1.2\% & -1.8\% & 7.0\% & 6.0\% \\
\hline
\text{Volume growth} & 5.9\% & -10.1\% & 9.1\% & 0.0\% & 7.0\% & 6.0\% & 6.0\% \\
\text{(%)} & & & & & & & \\
\hline
\text{Price (%)} & 6.0\% & 2.6\% & -5.4\% & 0.0\% & 4.0\% & 4.0\% & 4.0\% \\
\hline
\text{Organic growth} & 12.3\% & -7.8\% & 3.2\% & 0.0\% & 11.3\% & 10.2\% & 10.2\% \\
\text{(%)} & & & & & & & \\
\hline
\text{Forex impact} & -4.0\% & 2.5\% & -3.8\% & 0.0\% & 0.0\% & 0.0\% & 0.0\% \\
\text{and scope change} & & & & & & & \\
\text{(%)} & & & & & & & \\
\end{array}
$$

From the above table, assume that historical volume growth typically ranges between 4% and -6%. For future years, volume growth is expected to remain robust but slower than the high growth achieved in 2021. The consumption is expected to stay relatively stable.

In this model, we anticipate a 7% volume growth in 2023, which then decreases to 6% in 2024.

Price/mix contributed approximately 6.0%, 2.6%, and -5.4% to the Spirits & Liqueurs segment revenue growth in FY2020, FY2021, and FY2022, respectively. Despite fluctuations, price/mix is expected to remain a significant contributor to future revenue growth, given the favorable industry structure and the company’s efforts to increase the share of revenues from “premium spirits” .

A 4% price/mix contribution to revenue growth is projected for 2023, continuing into 2024. The combined projections for 2023 of 6% volume growth and 4% price/mix impact result in overall organic revenue growth of 10.2% (=[(1 + 0.06) × (1 + 0.04)] – 1).

In addition to volume and price/mix impacts, Diageo’s revenues are influenced by exchange rate movements. Assume that 70% of  Diageo’s revenues are generated outside the eurozone, while most of its production occurs within the eurozone. The model predicts no foreign currency impact on revenue for the 2023-24 forecast period.

A similar analysis for the Beer & Wine Segment is summarized in the table below:

$$
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{Beer & Wine} & 510 & 505 & 500 & 480 & 470 & 496 & 522 \\
\text{Segment Revenues} & & & & & & & \\
\text{(GBP millions)} & & & & & & & \\
\hline
\text{YoY %} & -1.0\% & -1.0\% & -1.0\% & -4.0\% & -2.1\% & 5.5\% & 5.2\% \\
\hline
\text{Volume} & 2.0\% & -4.0\% & -2.0\% & 0.0\% & 3.0\% & 2.5\% & 2.5\% \\
\text{growth } & & & & & & & \\
\hline
\text{Price/mix} & 3.0\% & 1.5\% & -2.0\% & 0.0\% & 2.5\% & 2.5\% & 2.5\% \\
\text{(%)} & & & & & & & \\
\hline
\text{Organic} & 5.0\% & -2.5\% & -4.0\% & 0.0\% & 5.6\% & 5.2\% & 5.2\% \\
\text{growth (%)} & & & & & & & \\
\hline
\text{Forex impact} & -6.0\% & -1.5\% & 2.0\% & 0.0\% & 0.0\% & 0.0\% & 0.0\% \\
\text{and scope} & & & & & & & \\
\text{change (%)} & & & & & & & \\
\end{array}
$$

For the next discussion, consider the following consolidated Historical and Projected Income Statement (Operating) for Diageo Holdings Ltd (GBP millions)

$$
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{Sales} & 2,230 & 2,215 & 2,200 & 2,160 & 2,120 & 2,262 & 2,394 \\
\hline
\text{Cost of} & 780 & 775 & 770 & 756 & 744 & 785 & 832 \\
\text{sales} & & & & & & & \\
\hline
\text{Gross} & 1,450 & 1,440 & 1,430 & 1,404 & 1,376 & 1,477 & 1,562 \\
\text{profit} & & & & & & & \\
\hline
\text{Gross} & 65.0\% & 65.0\% & 65.0\% & 65.0\% & 64.9\% & 65.3\% & 65.2\% \\
\text{margin} & & & & & & & \\
\hline
\text{Change in} & 0.0\% & 0.0\% & 0.0\% & -0.1\% & -0.1\% & 0.4\% & -0.1\% \\
\text{gross margin} & & & & & & & \\
\hline
\text{Distribution} & 510 & 490 & 480 & 468 & 456 & 492 & 521 \\
\text{costs} & & & & & & & \\
\hline
\text{Distribution} & 22.9\% & 22.1\% & 21.8\% & 21.7\% & 21.5\% & 21.7\% & 21.8\% \\
\text{costs as} & & & & & & & \\
\text{percent of sales} & & & & & & & \\
\hline
\text{Administrative} & 200 & 205 & 210 & 215 & 220 & 225 & 230 \\
\text{expenses} & & & & & & & \\
\hline
\text{Administrative} & 9.0\% & 9.3\% & 9.5\% & 10.0\% & 10.4\% & 9.9\% & 9.6\% \\
\text{expenses as} & & & & & & & \\
\text{percent of sales} & & & & & & & \\
\hline
\text{Other operating} & 5 & 5 & 5 & 5 & 5 & 5 & 5 \\
\text{expenses (income)} & & & & & & & \\
\hline
\text{EBIT} & 735 & 740 & 735 & 716 & 695 & 755 & 806 \\
\hline
\text{EBIT margin} & 33.0\% & 33.4\% & 33.4\% & 33.1\% & 32.8\% & 33.4\% & 33.7\% \\
\end{array}
$$

Cost of Goods Sold (COGS) forecasting

Next, project the COGS based on a set percentage of sales or utilize a more detailed approach that considers the business strategy and competitive environment. If there are anticipated changes in the supply chain or production costs, adjust the projections accordingly.

Diageo’s gross margin has remained relatively stable from FY2018 (65.0%) to FY2021 (65.0%), while total sales have decreased slightly. Looking forward, we project the gross margin to increase by 100 basis points each year over the next three years, driven by rising total revenues, especially from price/mix, which is significantly accretive to the gross margin.

Consequently, if revenue growth exceeds (or falls short of) our projections, we anticipate higher (or lower) gross margin expansion accordingly.

SG&A Expenses and Other Operating Expenses Forecasting

In this step, determine whether SG&A will be fixed or grow with revenue. Utilize historical data and trends to inform this projection. For instance, if SG&A historically grows at a similar rate to revenue, apply this trend to future projections to estimate the SG&A expenses.

Considering Diageo, distribution costs have varied significantly over time, rising from 22.9% in FY2018 and subsequently declining to 21.7% in FY2021. Assume that Diageo remains strongly committed to brand building and geographic diversification. As such, we project modest increases in distribution costs as a percentage of revenue of 20 basis points annually.

Administrative costs as a percentage of revenue have risen from 9.0% to 10.0% as revenues have declined (probably due to the COVID-19 pandemic). However, the growth in absolute GBP amounts has been modest, with costs of approximately GBP200 million in FY2018–FY2022. Consequently,we predict a 1% annual growth in administrative costs through FY2024.

Other operating expenses (income), primarily consisting of provisions for impairments of intangible assets, restructurings, and divestiture gains, have remained stable at GBP5 million from FY2018 to FY2022. As we do not anticipate any transactions that would result in other operating expenses or income, we forecast zero change for this line in the model.

Operating Profit by Segment Forecasting

In this section, we use a segment approach to alternatively estimate operating profit and margin. Diageo Holdings Ltd. discloses current operating profit for each of its segments, along with operating costs at the corporate or holding company level. It’s important to note that current operating profit is a non-IFRS measure that excludes certain items, which are disclosed on Diageo’s income statement as “Other operating expenses (income).” Therefore, the sum of the segment current operating profit equals consolidated EBIT before these other operating expenses (income).

Consider the following historical and projected operating profit by segment for the Diageo company:

$$
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{Revenue} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\textbf{(GBP millions)} & & & & & & & \\
\hline
\text{Spirits &} & 1,720 & 1,710 & 1,700 & 1,680 & 1,650 & 1,766 & 1,872 \\
\text{Liqueurs} & & & & & & & \\
\hline
\text{Beer &} & 510 & 505 & 500 & 480 & 470 & 496 & 522 \\
\text{Wine} & & & & & & & \\
\hline
\textbf{Total} & \textbf{2,230} & \textbf{2,215} & \textbf{2,200} & \textbf{2,160} & \textbf{2,120} & \textbf{2,262} & \textbf{2,394} \\
\textbf{revenues} & & & & & & & \\
\hline
\text{Spirits &} & 477 & 475 & 470 & 462 & 456 & 489 & 518 \\
\text{Liqueurs} & & & & & & & \\
\hline
\text{Beer &} & 66 & 65 & 58 & 63 & 60 & 66 & 69 \\
\text{Wine} & & & & & & & \\
\hline
\text{Holding/} & -24 & -25 & -28 & -26 & -24 & -25 & -27 \\
\text{Corporate-level} & & & & & & & \\
\text{costs} & & & & & & & \\
\hline
\textbf{Total current} & \textbf{519} & \textbf{515} & \textbf{500} & \textbf{499} & \textbf{492} & \textbf{530} & \textbf{560} \\
\textbf{operating profit} & & & & & & & \\
\hline
\textbf{Current} & & & & & & & \\
\textbf{Operating Profit} & & & & & & & \\
\textbf{Margins} & & & & & & & \\
\hline
\text{Spirits &} & 27.7\% & 27.8\% & 28.0\% & 27.5\% & 27.6\% & 27.7\% & 27.7\% \\
\text{Liqueurs} & & & & & & & \\
\hline
\text{Beer &} & 12.9\% & 12.9\% & 11.6\% & 13.1\% & 12.8\% & 13.3\% & 13.2\% \\
\text{Wine} & & & & & & & \\
\hline
\text{Holding/} & -1.1\% & -1.1\% & -1.3\% & -1.2\% & -1.1\% & -1.1\% & -1.1\% \\
\text{Corporate-level} & & & & & & & \\
\text{costs (percent of)} & & & & & & & \\ \text{total revenue)} & & & & & & & \\
\hline
\textbf{Total current} & \textbf{23.3%} & \textbf{23.3%} & \textbf{22.7%} & \textbf{23.1%} & \textbf{23.2%} & \textbf{23.4%} & \textbf{23.4%} \\
\textbf{operating} & & & & & & & \\ \textbf{profit margin} & & & & & & & \\
\end{array}
$$

From the above disclosure, for the Spirits & Liqueurs segment, the forecast of higher revenue growth, partially driven by strong price/mix growth, assumes an improving product mix that will also result in a higher gross margin. However, the benefit to the gross margin will be somewhat offset by higher distribution costs. Consequently, the expectation is that the Spirits & Liqueurs segment’s operating margin will increase to 27.7% by FY2024. As a benchmark, this forecast can be compared with the financial results reported by similar companies in the industry.

In contrast, the Beer & Wine segment is not expected to see much upside. We project the operating margin to increase modestly to 13.2%. Overall, Diageo Holdings Ltd.’s consolidated operating margin is forecasted to improve from 23.1% in FY2021 to 23.4% in FY2024, largely due to growth and margin improvement in the Spirits & Liqueurs segment, the most profitable division, and leveraging from that sales growth on corporate-level costs.

Non-Operating Items Forecasting

The model includes three non-operating line items: finance expenses (or interest expenses), income taxes, and shares outstanding.

Consider the following debt position and financial costs and income for Diageo Holdings Ltd (in GBP millions)

$$
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{Long-term} & 450 & 475 & 500 & 480 & 480 & 480 & 480 \\
\text{financial debt} & & & & & & & \\
\hline
\text{Short-term} & 50 & 75 & 100 & 95 & 95 & 95 & 95 \\
\text{financial debt} & & & & & & & \\
\text{and accrued} & & & & & & & \\
\text{interest} & & & & & & & \\
\hline
\textbf{Gross debt} & \textbf{500} & \textbf{550} & \textbf{600} & \textbf{575} & \textbf{575} & \textbf{575} & \textbf{575} \\
\hline
\text{Interest} & 15.0 & 14.2 & 13.5 & 12.8 & 10.0 & 10.0 & 10.0 \\
\text{expense} & & & & & & & \\
\hline
\text{Interest rate} & 3.0\% & 2.7\% & 2.3\% & 2.2\% & 1.7\% & 1.7\% & 1.7\% \\
\text{(on beginning} & & & & & & & \\
\text{balance)} & & & & & & & \\
\hline
\text{Interest} & 0.1 & 0.1 & 0.2 & 0.2 & 0.1 & 0.1 & 0.1 \\
\text{income} & & & & & & & \\
\hline
\textbf{Net finance} & \textbf{14.9} & \textbf{14.1} & \textbf{13.3} & \textbf{12.6} & \textbf{9.9} & \textbf{9.9} & \textbf{9.9} \\
\textbf{cost} & & & & & & & \\\end{array}
$$

Note that the net finance cost on Diageo’s income statement is derived from interest expenses on debt minus interest income earned on cash and investments. As such, to forecast net finance cost, it is necessary to estimate the debt and cash positions as well as the interest rates paid and earned. Companies may pay a fixed or variable interest rate on their debt. If the interest rate is variable, it is usually determined by a market reference rate plus a credit spread.

From the above table, Diageo’s interest expenses are fixed and calculated as 1.7% incurred on gross debt at the beginning of the period (GBP 575 million at the end of FY2020). Other financial expenses are assumed to be zero. Both gross debt and the interest rate paid on it are expected to remain constant from the year-ended FY2021 level.

Although interest income is typically forecasted after forecasting the cash position from the projected statement of cash flows, in this case, we have simply estimated GBP 0.1 million in interest income through the model period.

In each of FY2018–FY2021, annual interest income was GBP 0.1, GBP 0.1, GBP 0.2, and GBP 0.2 million, respectively, assuming that Diageo maintains its liquidity in assets with zero or very low yields. Note that for companies that hold liquid assets with higher interest rates or in higher interest rate environments, interest income should be forecasted in the same manner as interest expense: forecasted cash and investments multiplied by a forecasted interest rate.

Corporate Tax Forecasting

Assume that at the time of analysis, the statutory corporate income tax rate for Diageo Holdings Ltd. is 30 percent. Also, assume that historically, Diageo Holdings Ltd.’s effective tax rate has been close to the statutory rate. As such, an estimated 30 percent effective tax rate is used in the forecast period.

Moreover, assume that Diageo Holdings Ltd. has no significant minority interests in its subsidiaries.

The table below provides the forecast for corporate income tax based on an effective tax rate of 30% for Diageo Holdings Ltd., using the EBIT values previously calculated.

$$
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{Corporate} & & & & & & & \\
\textbf{Income Tax} & & & & & & & \\
\textbf{Forecast} & & & & & & & \\
\textbf{(GBP millions)} \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{EBIT} & 735 & 740 & 735 & 716 & 695 & 755 & 806 \\
\hline
\text{Tax rate} & 30\% & 30\% & 30\% & 30\% & 30\% & 30\% & 30\% \\
\hline
\text{Tax expense} & 220.5 & 222.0 & 220.5 & 214.8 & 208.5 & 226.5 & 241.8 \\
\end{array}
$$

The table above provides the forecast for corporate income tax based on an effective tax rate of 30% for Diageo Holdings Ltd., using the EBIT values previously calculated.

Share Outstanding Forecasting

Shares outstanding to compute earnings per share (EPS) on the income statement are disclosed in two ways: basic and diluted, both as weighted averages throughout the fiscal year. Basic shares outstanding include common equity securities, while diluted shares outstanding add the number of shares from the exercise or conversion of in-the-money instruments minus an assumed repurchase of those if-issued shares.

Typically, the two main factors affecting shares outstanding over time are share issuance related to equity-based compensation of employees (which increases shares outstanding) and share repurchases (which decrease shares outstanding). Less common but sometimes significant transactions include acquisitions financed with stock, secondary issuance, and conversions of preferred stock or other instruments to common stock.

The tables below show the beginning and ending basic shares outstanding for the past six fiscal years, as well as the annual net amount of share repurchases and issuance that were gathered from the statements of stockholders’ equity and notes to financial statements.

$$\begin{array}{l|c|c|c|c|c|c|c}
\textbf{} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{FY2024E} \\
\hline
\text{Beginning} & 48.6 & 49.6 & 50.0 & 49.8 & 49.8 & 50.3 & 50.3 \\
\text{basic shares} & & & & & & & \\
\text{outstanding} & & & & & & & \\
\hline
\text{Share} & 0.0 & -0.3 & -1.0 & -0.0 & 0.0 & 0.0 & 0.0 \\
\text{repurchases} & & & & & & & \\
\hline
\text{Share} & 1.0 & 0.7 & 0.8 & 0.1 & 0.4 & 0.5 & 0.5 \\
\text{issuance} & & & & & & & \\
\hline
\text{Ending} & 49.6 & 50.0 & 49.8 & 49.8 & 50.3 & 50.3 & 50.8 \\
\text{basic shares} & & & & & & & \\
\text{outstanding} & & & & & & & \\
\hline
\text{Weighted} & 49.1 & 49.8 & 50.1 & 49.8 & 50.1 & 50.3 & 50.6 \\
\text{average basic} & & & & & & & \\
\text{shares} & & & & & & & \\
\hline
\text{Dilutive} & 2.7 & 2.6 & 2.6 & 2.6 & 2.6 & 2.6 & 2.6 \\
\text{securities} & & & & & & & \\
\hline
\text{Weighted} & 51.8 & 52.4 & 52.7 & 52.4 & 53.1 & 52.9 & 53.2 \\
\text{average diluted} & & & & & & & \\
\text{shares} & & & & & & & \\
\end{array}
$$

Notice that the basic and diluted shares outstanding on the income statement used to calculate basic and diluted EPS (weighted averages) are shown, differing by approximately 2.6 million shares in each of the past five years.

Moreover, Diageo Holdings Ltd’s outstanding shares have not changed significantly over six years because the company does not offer substantial share-based compensation or actively repurchase shares. Assuming that the management has not announced any plans to repurchase shares in the near future, the model assumes that the weighted average basic and diluted shares outstanding on the income statement will remain consistent at the FY2022 level.

Consolidated Historical and Projected Income Statement

With the forecast components in place, we can now construct a consolidated pro forma income statement. Here is the proforma income statement for Diageo Holdings Ltd:

\[
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{Sales} & 2,230 & 2,215 & 2,200 & 2,160 & 2,120 & 2,262 & 2,394 \\
\hline
\text{Cost of} & 780 & 775 & 770 & 756 & 744 & 785 & 832 \\
\text{sales} & & & & & & & \\
\hline
\text{Gross} & 1,450 & 1,440 & 1,430 & 1,404 & 1,376 & 1,477 & 1,562 \\
\text{profit} & & & & & & & \\
\hline
\text{Gross} & 65.0\% & 65.0\% & 65.0\% & 65.0\% & 64.9\% & 65.3\% & 65.2\% \\
\text{margin} & & & & & & & \\
\hline
\text{Change in} & 0.0\% & 0.0\% & 0.0\% & -0.1\% & -0.1\% & 0.4\% & -0.1\% \\
\text{gross margin} & & & & & & & \\
\hline
\text{Distribution} & 510 & 490 & 480 & 468 & 456 & 492 & 521 \\
\text{costs} & & & & & & & \\
\hline
\text{Distribution} & 22.9\% & 22.1\% & 21.8\% & 21.7\% & 21.5\% & 21.7\% & 21.8\% \\
\text{costs as} & & & & & & & \\
\text{percent of sales} & & & & & & & \\
\hline
\text{Administrative} & 200 & 205 & 210 & 215 & 220 & 225 & 230 \\
\text{expenses} & & & & & & & \\
\hline
\text{Administrative} & 9.0\% & 9.3\% & 9.5\% & 10.0\% & 10.4\% & 9.9\% & 9.6\% \\
\text{expenses as} & & & & & & & \\
\text{percent of sales} & & & & & & & \\
\hline
\text{Other} & 5 & 5 & 5 & 5 & 5 & 5 & 5 \\
\text{operating} & & & & & & & \\
\text{expenses (income)} & & & & & & & \\
\hline
\text{EBIT} & 735 & 740 & 735 & 716 & 695 & 755 & 806 \\
\hline
\text{EBIT} & 33.0\% & 33.4\% & 33.4\% & 33.1\% & 32.8\% & 33.4\% & 33.7\% \\
\text{margin} & & & & & & & \\
\hline
\text{Depreciation} & 30 & 32 & 33 & 34 & 35 & 36 & 37 \\
\text{and amortization} & & & & & & & \\
\text{(add-back)} & & & & & & & \\
\hline
\text{Depreciation} & 1.3\% & 1.4\% & 1.5\% & 1.6\% & 1.7\% & 1.6\% & 1.5\% \\
\text{and amortization} & & & & & & & \\
\text{as percent of sales} & & & & & & & \\
\hline
\text{EBITDA} & 765 & 772 & 768 & 750 & 730 & 791 & 843 \\
\hline
\text{EBITDA} & 34.3\% & 34.8\% & 34.9\% & 34.7\% & 34.4\% & 35.0\% & 35.2\% \\
\text{margin} & & & & & & & \\
\hline
\text{Net finance} & 15 & 14 & 13 & 12 & 9 & 9 & 9 \\
\text{costs} & & & & & & & \\
\hline
\text{Other} & 8 & 19 & 15 & 3 & 0 & 0 & 0 \\
\text{financial} & & & & & & & \\
\text{expenses} & & & & & & & \\
\hline
\text{Total} & 22 & 33 & 28 & 15 & 9 & 9 & 9 \\
\text{financial} & & & & & & & \\
\text{expenses} & & & & & & & \\
\hline
\text{Profit before} & 713 & 707 & 707 & 701 & 686 & 746 & 797 \\
\text{tax} & & & & & & & \\
\hline
\text{Income} & 214 & 212 & 212 & 210 & 206 & 224 & 239 \\
\text{tax} & & & & & & & \\
\hline
\text{Effective} & 30.0\% & 30.0\% & 30.0\% & 30.0\% & 30.0\% & 30.0\% & 30.0\% \\
\text{tax rate} & & & & & & & \\
\hline
\text{Income from} & 1 & 1 & 0 & 1 & 0 & 0 & 0 \\
\text{associates} & & & & & & & \\
\hline
\text{Profit from} & 500 & 496 & 495 & 492 & 480 & 522 & 558 \\
\text{continuing} & & & & & & & \\
\text{operations} & & & & & & & \\
\hline
\text{Profit from} & 0 & 0 & 6 & 0 & 0 & 0 & 0 \\
\text{discontinued} & & & & & & & \\
\text{operations} & & & & & & & \\
\hline
\text{Net profit} & 500 & 496 & 501 & 492 & 480 & 522 & 558 \\
\text{for the year} & & & & & & & \\
\hline
\text{YoY %} & 0.8\% & -0.8\% & 1.0\% & -1.8\% & -2.4\% & 8.7\% & 6.9\% \\
\hline
\text{EPS basic} & 10.04 & 9.90 & 9.94 & 9.82 & 9.58 & 10.42 & 11.14 \\
\text{continuing} & & & & & & & \\
\text{operations} & & & & & & & \\
\hline
\text{EPS diluted} & 9.54 & 9.42 & 9.45 & 9.20 & 8.95 & 9.92 & 10.63 \\
\text{continuing} & & & & & & & \\
\text{operations} & & & & & & & \\
\hline
\text{EPS basic} & 10.04 & 9.90 & 10.06 & 9.82 & 9.58 & 10.42 & 11.14 \\
\text{total} & & & & & & & \\
\hline
\text{EPS diluted} & 9.54 & 9.42 & 9.57 & 9.20 & 8.95 & 9.92 & 10.63 \\
\text{total} & & & & & & & \\
\hline
\text{Average number} & 49.8 & 50.1 & 50.1 & 50.1 & 50.1 & 50.1 & 50.1 \\
\text{of shares,} & & & & & & & \\
\text{basic (millions)} & & & & & & & \\
\hline
\text{Average number} & 52.4 & 52.7 & 52.6 & 52.6 & 52.6 & 52.6 & 52.6 \\
\text{of shares,} & & & & & & & \\
\text{diluted (millions)} & & & & & & & \\
\end{array}
\]

Note that, although EBITDA is not typically presented on the face of the income statement as disclosed by the company, it can be calculated by adding depreciation and amortization expenses from the statement of cash flows to EBIT. We have shown it for assessing profitability.

Pro Forma Statement of Cash Flows

The forecast statements of cash flows start with the projected net income and other amounts derived from the forecast income statement. These statements typically require estimates for various elements such as capital expenditures, depreciation and amortization, working capital, share-based compensation, dividends, and share repurchases.

Specifically, for cash flow from operating activities, we need to estimate net income from income statements, share-based compensation, working capital, and depreciation & amortization. For cash flow from investing activities, we need to estimate capex. Lastly, for cashflow from financing activities, we need to estimate dividends and debt issuance, and repayment.

Forecasting Capital Investments and Depreciation Forecasts

Assume that Capital investments, or capex, represented 5.0% of revenue in FY2022 for Diageo Holdings Ltd.

Consider the following breakdowns of capex and D&A:

$$
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{D&A} & 25 & 28 & 30 & 32 & 34 & 36 & 37 \\
\text{(GBP millions)} & & & & & & & \\
\hline
\text{As percent} & 3.8\% & 4.1\% & 4.0\% & 4.0\% & 4.0\% & 4.0\% & 4.0\% \\
\text{of prior year} & & & & & & & \\
\text{fixed assets} & & & & & & & \\
\hline
\text{Capex, PP\&E,} & 40 & 50 & 60 & 55 & 58 & 61 & 65 \\
\text{and intangibles} & & & & & & & \\
\text{(GBP millions)} & & & & & & & \\
\hline
\text{Capex as} & 3.2\% & 3.8\% & 4.5\% & 5.0\% & 5.2\% & 5.2\% & 5.2\% \\
\text{% of sales} & & & & & & & \\
\hline
\text{Capex/D&A} & 1.6 & 1.8 & 2.0 & 1.7 & 1.7 & 1.7 & 1.8 \\
\text{ratio} & & & & & & & \\
\end{array}
$$

Given the positive volume growth outlook, we anticipate capex to remain slightly above the historical average at 5.2% of sales through FY2025.

With Diageo’s expanding fixed asset base, it is reasonable to expect an increase in depreciation. Furthermore, the model assumes that depreciation and amortization (D&A) will be 4.0% of the prior year’s fixed assets, based on the average of the past three years.

Working Capital Forecasts

Consider the following breakdown of working capital for Diageo:

$$
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{Inventories} & 1,170 & 1,246 & 1,364 & 1,493 & 1,426 & 1,340 & 1,245 \\
\text{(GBP millions)} & & & & & & & \\
\hline
\text{Accounts} & 210 & 271 & 199 & 158 & 171 & 185 & 200 \\
\text{receivable} & & & & & & & \\
\hline
\text{Accounts} & 517 & 544 & 534 & 586 & 597 & 604 & 610 \\
\text{payable} & & & & & & & \\
\hline
\text{Working} & 863 & 973 & 1,029 & 1,065 & 1,000 & 922 & 835 \\
\text{capital, net} & & & & & & & \\
\hline
\text{Percent} & 77\% & 86\% & 100\% & 105\% & 91\% & 78\% & 65\% \\
\text{of sales} & & & & & & & \\
\hline
\text{Change in} & & -110 & -56 & -36 & 64 & 79 & 87 \\
\text{working capital} & & & & & & & \\
\hline
\text{Days} & 1,166 & 1,095 & 1,431 & 1,650 & 1,500 & 1,350 & 1,200 \\
\text{inventories on hand} & & & & & & & \\
\hline
\text{Days sales} & 68 & 88 & 71 & 57 & 57 & 57 & 57 \\
\text{outstanding} & & & & & & & \\
\hline
\text{Days payable} & 515 & 478 & 561 & 648 & 628 & 608 & 588 \\
\text{outstanding} & & & & & & & \\
\end{array}
$$

In the table above, we include only the relevant balance sheet items related to revenues and costs, including inventories, accounts receivable, and accounts payable and keep the other items constant. Diageo Holdings Ltd. had positive net working capital of 91% of its sales in fiscal year 2022. The largest working capital component is inventory because much of Diageo’s spirits require aging.

Inventory days on hand in FY2022 were 1,500, reflecting an increase due to the volume slowdown during the COVID-19 pandemic. Inventory days are partially mitigated by extended payment terms to suppliers; days payable outstanding has averaged above 500 days since FY2018.

From the forecasts, we expect inventory days to decline through FY2024 as the inventory increase that occurred during the COVID-19 pandemic is worked through, days sales outstanding to remain at FY2022 levels, and days payable outstanding to decline back to an average level, reflecting a normalization after the COVID-19 pandemic.

Consequently, the decrease in inventory days, the model projects a net positive contribution from working capital to the reconciliation of net income to cash flows from operations on the statement of cash flows, which is a stark contrast to prior years’ negative contribution.

Forecasted Cash Flow Statement

With net income, depreciation and amortization (D&A), changes in working capital, capital expenditures (capex), and debt estimates already established, the cash flow statement is essentially generated by linking the relevant lines on a spreadsheet, as shown in the following projected statement of cash flows for Diageo Holdings Ltd (in GBP millions) :

$$
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{Net income} & 148 & 159 & 113 & 144 & 179 & 205 & 234 \\
\text{(loss)} & & & & & & & \\
\hline
\text{D\&A} & 25 & 28 & 30 & 32 & 34 & 36 & 37 \\
\hline
\text{Share-based} & 3 & 3 & 4 & 2 & 2 & 2 & 2 \\
\text{compensation} & & & & & & & \\
\hline
\text{Investment in} & -7 & -162 & -72 & -13 & 64 & 79 & 87 \\
\text{working capital} & & & & & & & \\
\hline
\text{Other non-cash} & 20 & 22 & 3 & 10 & 0 & 0 & 0 \\
\text{amounts} & & & & & & & \\
\hline
\text{Cash flows} & 189 & 50 & 78 & 175 & 279 & 322 & 360 \\
\text{from operations} & & & & & & & \\
\hline
\text{Capex} & -40 & -50 & -60 & -55 & -58 & -61 & -65 \\
\text{(PP\&E and} & & & & & & & \\
\text{intangibles)} & & & & & & & \\
\hline
\text{Other investing} & 2 & 92 & 12 & 62 & 0 & 0 & 0 \\
\text{activities} & & & & & & & \\
\hline
\text{Cash flows} & -38 & 42 & -48 & 7 & -58 & -61 & -65 \\
\text{from investments} & & & & & & & \\
\hline
\text{Debt issuance} & 0 & 11 & 196 & -246 & 0 & 0 & 0 \\
\text{(repayment)} & & & & & & & \\
\hline
\text{Share issuance} & -27 & -104 & -2 & 2 & 0 & 0 & 0 \\
\text{(repurchases)} & & & & & & & \\
\hline
\text{Dividends paid} & -25 & -9 & -132 & -10 & -10 & -10 & -10 \\
\hline
\text{Cash flows} & -52 & -102 & 62 & -254 & -10 & -10 & -10 \\
\text{from financing} & & & & & & & \\
\hline
\text{FX translation} & 8 & -6 & 1 & -1 & 0 & 0 & 0 \\
\text{effects} & & & & & & & \\
\hline
\text{Net change} & 107 & -16 & 93 & -73 & 211 & 251 & 285 \\
\text{in cash} & & & & & & & \\
\hline
\text{Cash and} & 78 & 185 & 169 & 262 & 189 & 400 & 651 \\
\text{equivalents,} & & & & & & & \\
\text{beginning} & & & & & & & \\
\hline
\text{Cash and} & 185 & 169 & 262 & 189 & 400 & 651 & 936 \\
\text{equivalents, end} & & & & & & & \\
\end{array}
$$

The three significant items left to forecast are share-based compensation, share repurchases or issuance, and dividends. The model assumes flat share-based compensation, no share repurchases or issuance, and dividends paid equal to the FY2022 level through FY2024. Lines labeled “other non-cash amounts” are aggregated and zeroed out going forward because they are immaterial, difficult to forecast, or both.

Forecasted Balance Sheet

The projected balance sheet is formed by integrating the projected income statement, the projected statement of cash flows, and the historical starting balance sheet, as covered in an earlier module. Balance sheet items not specifically discussed are kept constant to maintain the accounting identity.

For simplicity, stockholders’ equity lines, including common stock, additional paid-in capital, retained earnings, treasury shares, and accumulated other comprehensive income, are combined. For each forecast period, common stockholders’ equity is calculated as the previous year’s value plus net income and share-based compensation minus dividends.

The projected balance sheet for Diageo Holdings Ltd. (generated using Excel) is shown in below:

$$
\begin{array}{l|c|c|c|c|c|c|c}
\textbf{} & \textbf{2018} & \textbf{2019} & \textbf{2020} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{Cash and} & 186.8 & 178.6 & 269.4 & 201.0 & 418 & 671 & 958 \\
\text{equivalents} & & & & & & & \\
\hline
\text{Accounts} & 210 & 271 & 199 & 158 & 171 & 185 & 200 \\
\text{receivable} & & & & & & & \\
\hline
\text{Inventories} & 1,170 & 1,246 & 1,364 & 1,493 & 1,426 & 1,340 & 1,245 \\
\hline
\text{Other current} & 16 & 5 & 16 & 10 & 10 & 10 & 10 \\
\text{assets} & & & & & & & \\
\hline
\textbf{Total} & \textbf{1,583} & \textbf{1,700} & \textbf{1,848} & \textbf{1,861} & \textbf{2,025} & \textbf{2,206} & \textbf{2,412} \\
\textbf{current assets} & & & & & & & \\
\hline
\text{PP\&E,} & 752 & 785 & 808 & 845 & 864 & 887 & 913 \\
\text{intangibles,} & & & & & & & \\
\text{goodwill, net} & & & & & & & \\
\hline
\text{Investment in} & 20 & 1 & 1 & 2 & 2 & 2 & 2 \\
\text{associates} & & & & & & & \\
\hline
\text{Other non-} & 186 & 139 & 131 & 73 & 73 & 73 & 73 \\
\text{current assets} & & & & & & & \\
\hline
\textbf{Total} & \textbf{2,542} & \textbf{2,625} & \textbf{2,789} & \textbf{2,781} & \textbf{2,964} & \textbf{3,168} & \textbf{3,400} \\
\textbf{assets} & & & & & & & \\
\hline
\text{Short-term/} & 73 & 98 & 268 & 92 & 92 & 92 & 92 \\
\text{current debt} & & & & & & & \\
\hline
\text{Accounts} & 517 & 544 & 534 & 586 & 597 & 604 & 610 \\
\text{payable} & & & & & & & \\
\hline
\text{Other current} & 26 & 31 & 39 & 42 & 42 & 42 & 42 \\
\text{liabilities and} & & & & & & & \\
\text{accrued expenses} & & & & & & & \\
\hline
\textbf{Total} & \textbf{616} & \textbf{673} & \textbf{842} & \textbf{720} & \textbf{731} & \textbf{737} & \textbf{744} \\
\textbf{current liabilities} & & & & & & & \\
\hline
\text{Long-term/} & 397 & 424 & 452 & 424 & 424 & 424 & 424 \\
\text{non-current debt} & & & & & & & \\
\hline
\text{Other non-} & 121 & 102 & 92 & 88 & 88 & 88 & 88 \\
\text{current liabilities} & & & & & & & \\
\hline
\textbf{Total} & \textbf{1,407} & \textbf{1,425} & \textbf{1,403} & \textbf{1,548} & \textbf{1,720} & \textbf{1,918} & \textbf{2,144} \\
\textbf{common equity} & & & & & & & \\
\hline
\text{NCI} & 1 & 1 & 1 & 1 & 1 & 1 & 1 \\
\text{(Non-Controlling} & & & & & & & \\
\text{Interest)} & & & & & & & \\
\hline
\textbf{Total equity} & \textbf{2,542} & \textbf{2,625} & \textbf{2,789} & \textbf{2,781} & \textbf{2,964} & \textbf{3,168} & \textbf{3,400} \\
\textbf{and liabilities} & & & & & & & \\
\end{array}
$$

If all the mentioned lines are correctly linked and other lines are held constant from FY2022, the projected balance sheet should balance each year.

Note that the projected balance sheet, featuring both historical and forecasted data on key items such as cash and equivalents, accounts receivable, inventories, other current assets, property, plant, and equipment (PP&E), intangibles, goodwill, investment in associates, other non-current assets, total current liabilities, short-term debt, accounts payable, other current liabilities, long-term debt, other non-current liabilities, and total common equity.

Valuation Model Inputs

A financial statement model serves as the foundation for most valuation models. Valuation estimates can be derived using various metrics, such as free cash flow, EPS, EBITDA, and EBIT.

For instance assume that we neet to project the free cashflow to the firm for the Diageo Holdings a basis for a discounted cashflow (DCF) valuation model. Consider the following table:

$$
\begin{array}{l|c|c|c|c}
\textbf{} & \textbf{2021} & \textbf{2022} & \textbf{2023E} & \textbf{2024E} \\
\hline
\text{EBIT} & 716 & 695 & 755 & 806 \\
\hline
\text{Taxes} & -215 & -209 & -227 & -242 \\
\text{(30% tax rate)} & & & & \\
\hline
\text{After-tax} & 501 & 486 & 528 & 564 \\
\text{EBIT} & & & & \\
\hline
\text{D&A} & 32 & 34 & 36 & 37 \\
\hline
\text{Change in} & -13 & 64 & 79 & 87 \\
\text{working capital} & & & & \\
\hline
\text{Capital} & -55 & -58 & -61 & -65 \\
\text{expenditures} & & & & \\
\hline
\textbf{Free cash} & \textbf{465} & \textbf{526} & \textbf{582} & \textbf{623} \\
\textbf{flow to the firm} & & & & \\
\end{array}
$$

Note that all these variables are obtained from the forecasted income statements and statements of cash flows.

Question

XYZ Inc., a growing technology company, is preparing a sales-based proforma model to forecast its financial position for the next year. The firm has consistently shown a revenue growth of 8% over the past five years. The company’s Cost of Goods Sold (COGS) has been around 45% of the sales. The expected operating expenses, including Selling, General, and administrative expenses (SG&A), are estimated to grow at 2% annually. The management plans to make substantial capital expenditures in the upcoming year due to expansion. The company also anticipates changes in the financial structure, which will affect its financing costs.

As a financial analyst, which of the following steps would be the most crucial in ensuring the accuracy and reliability of the proforma model for XYZ Inc.?

1. Keeping the COGS and SG&A expenses fixed based on historical data.
2. Focusing primarily on estimating revenue growth.
3. Giving due consideration to anticipated changes in capital expenditures and financial structure.

Solution

The correct answer is C.

In the development of a proforma model, all aspects, like revenue growth, COGS, and operating expenses, are crucial. However, for XYZ Inc., which anticipates significant capital expenditures and changes in financial structure, giving due emphasis to these aspects is paramount. Estimating the impact of these changes on financing costs and other related elements is essential to ensure the proforma model’s accuracy.

A is incorrect. Relying strictly on historical data for COGS and SG&A without accounting for the projected growth and changes in the company’s operations may lead to inaccurate projections. These figures are likely to change with the anticipated capital expenditure and alterations in financial structure.

B is incorrect. While revenue growth estimation is vital, focusing solely on it could overlook other significant aspects like changes in capital expenditures and financial structure, which XYZ Inc. anticipates. Proper attention to these elements is crucial for a balanced and reliable proforma model.