Learning Module 7: Financial Statement Modeling

Learning Module 7 Financial Statement Modeling EXAMPLE 2 Analysis of Revenue (2) Use the provided data as well as the data in Example 1 on Novo Nordisk to answer the following questions: Xiaoping Wu is an equity analyst covering European pharmaceutical compa- nies for his clients in China. Wu projects that global nominal GDP will grow 3% annually over the long run, based on 2% real growth and 1% inflation. The prevalence of diabetes is increasing globally because of increasingly unhealthy diets and sedentary lifestyles. As a result, Wu believes global sales of diabetes drugs will grow 100 bps faster than nominal GDP over the long run. Wu believes the revenue growth rate of Novo Nordisk's Diabetes and Obesity Care segment will decelerate linearly over four years to match the projected long-run growth rate of the diabetes drug market. 1. Is Wu using a top-down, bottom-up, or hybrid approach to modeling Novo Nordisk's revenue? Solution Wu's long-run revenue projections are based on Novo Nordisk's growth relative to nominal GDP growth, which is a top-down approach. However. his estimated growth rate is applied to only one of Novo Nordisk's segments (Diabetes and Obesity Care), indicating a hybrid approach. Wu's four-year forecasts are also based in part on the historical growth rate of the Diabe- tes and Obesity Care segment, which is a bottom-up approach. Wu is thus using a hybrid approach. 2. Based on Wu's projections for revenue growth, calculate the estimated reve- nue growth rate for the Diabetes and Obesity Care segment in 2021. Assume no impact from exchange rate changes. Solution The data in Example 1 indicate that Novo Nordisk's Diabetes and Obesity Care segment grew by 5% in 2020. Wu projects the long-run growth rate to be in line with the diabetes drug market growth at 4% (100 bps faster than GDP growth of 3%). The difference between the 2020 growth rate and the projected long-run growth rate is 1% (= 5%-4%), and Wu expects the modest deceleration in growth to occur linearly over four years, implying a reduction of 25 bps per year in the growth rate. The estimated growth rates by year are thus 2021 = 4.75% 2022-4.5% 2023-4.25% 2024=4% Thereafter, 4% The estimated revenue growth rate for 2021 is 4.75%. Helga Hansen is a buy-side analyst in Denmark. In 2021, Hansen was investigat- ing Rybelsus, a Novo Nordisk product launched in 2019, in a class of diabetes drugs called GLP-1 analogs. As of 2021, Rybelsus is one of several GLP-1 analogs on the market, competing with Novo Nordisk's own Victoza and Ozempic, Eli Lilly's Trulicity, and exenatide (brands Byetta and Bydureon) by AstraZeneca. Rybelsus is the same drug compound as Ozempic but is a once-daily pill, unlike Ozempic and all other GLP-1 analogs, which are injectable drugs. Eli Lilly and AstraZeneca reported global sales of their products in US dollars (USD). Hansen converted the companies' reported figures to Danish krone using annual average USD/DKK exchange rates to compile Exhibit 3, which shows annual sales of the GLP-1 analogs measured in millions of DKK. Exhibit 3 is in the Example2 sheet in the downloadable Microsoft Excel workbook. Exhibit 3: GLP-1 Analog Sales, Annual (DKK millions) Product Victoza Ozempic Rybelsus Trulicity Byetta and Bydureon Company Novo Nordisk Novo Nordisk Novo Nordisk Eli Lilly AstraZeneca 2020 18,747 21,211 1,873 33,135 3,374 2019 21,934 11,237 50 27,533 2018 24,333 1,796 0 20,215 4,396 4,486 1. What was the growth rate in total GLP-1 analog sales in 2020? Solution Total GLP-1 analog sales in 2020 and 2019 were DKK78,340 million and DKK65,150 million, respectively. The growth rate of total GLP-1 analog sales in 2020 (78,34065,150/65,150) was 20%. The full calculations to support this solution are in the Example2 sheet in the downloadable Microsoft Excel workbook. 2. What percentage of GLP-1 analog sales growth in 2020 can be attributed to Rybelsus? Solution Total GLP-1 analog sales increased by DKK13,190 million in 2020. Rybel- sus's sales increased by DKK1,823 million, which implies that Rybelsus's growth accounted for (1,823/13,190) 14% of GLP-1 analog sales growth. The full calculations to support this solution are in the Example2 sheet in the downloadable Microsoft Excel workbook. 3. Hansen previously projected that the growth rate of the GLP-1 analog market would slow to 18% in 2020. She also expected Trulicity market share to fall by 5 percentage points. What was Hansen's estimate of 2020 Trulicity sales? How close was she to the actual result? Solution Based on 2019 sales of DKK65,150 million and a projected growth rate of 18%, Hansen projected the total GLP-1 analog sales to be DKK76,877 million in 2020. Trulicity's market share in 2019 was -42%, which Hansen projected to fall by 5 percentage points, resulting in a -37% market share in 2020. Hansen thus projected 2020 Trulicity sales to be DKK28,645 million. Actual Trulicity sales in 2020 were DKK33,135 million, so Hansen's estimate was too low by DKK4,490 million or 14%. The full calculations to support this solution are in the Example2 sheet in the downloadable Microsoft Excel workbook. 4. Is Hansen's approach to modeling sales best described as bottom-up, top-down, or hybrid? Solution Hansen bases her estimates on market growth and market share, which would normally imply a top-down approach. The analysis, however, is ap- plied to an individual product line, implying a bottom-up approach. There- fore, Hansen is using a hybrid approach. EXAMPLE 4 The Effect of Prices and Costs on Gross Profit and Margin Assume that a company's COGS as a percentage of sales equals 25%. If input costs double the following period, and the company can pass the entire increase on to its clients through a 25% price increase, COGS as a percentage of sales will increase (to 40%) because an equal absolute amount has been added to the numerator and to the denominator. Period 1 Period 2 Sales 100.0 125.0 COGS 25.0 50.0 Gross profit 75.0 750 25% 40% COGS as of sales Gross margin % 75% 60% Thus, although the absolute amount of gross profit will remain constant, the gross margin will decrease (from 75% to 60%). EXAMPLE 5 L'Oréal's Operational Cost Structure versus Competitors As shown in Exhibit 7. L'Oréal reported an EBIT (earnings before interest and taxes) margin of 18.6% in 2019, which makes it the most profitable company among a selection of beauty companies. However, the average EBIT margin of 19.6% for home and personal goods companies operating in mass markets is even greater than that of L'Oréal. Luxury goods companies tend to have higher gross margins, owing to higher prices, than mass market companies, but those margins are offset by higher "go to market costs such as advertising and pro- motion (A&F) expenditures. With the exception of Avon, the business model of which is based on direct selling. A&P is substantially greater at the beauty companies than at the mass market producers. L'Oréal is often considered a pure beauty company. But if the underlying business is examined in detail, the company's operations can be split 50/50 between a luxury beauty high-end business and a general consumer business. In the general consumer business, L'Oréal's products compete with those of such players as Colgate, Procter & Gamble, and Henkel in the mass market. Exhibit 7 presents. relevant data and can also be found in the Example4 sheet in the downloadable Microsoft Excel workbook. Exhibit 7: European and US Home and Personal Care Companies, Beauty vs. Mass Market Companies: Simplified and Common-Size Income Statement (2019) Company Sales (mins) Gross Margin A&P% SG&A/ Other % EBIT % Beauty L'Oreal €29,874 73.0% 30.8% 23.6% 18.6% Estée Lauder $14,863 77.2% 22.5% 37.1% 17.6% 13.5% Beiersdorf €7,653 57.9% 34.8% 9.6% Avon $4.763 57.8% 1.5% 53.6% Beauty Average 66.5% 22.4% 31.0% 2.6% 13.1% Company Sales (mins) Mass Market Colgate $15,693 Reckitt Group Procter & Gamble Clorox Kimberly-Clark Henkel €20,114 Mass Market Average Gross Margin 59.4% A&P % 10.8% €12.846 $67/684 $6,214 818.450 60.5% 14.4% EBIT % 23.9% 19.9% 18.2% 16.0% 26.2% 48.6% 10.0% 20.4% 43.9% 9.8% 18.1% 32.7% 4.1% 13,5% 45.9% 48.5% N/A 31.9% 9.8% SG&A/ Other % 24.7% 20.7% 15.1% 14.0% 19.6% EXAMPLE 6 Analysis of the Consumer Goods Company Unilever The consumer goods company Unilever reported an overall underlying operat- ing margin of 18.4% in 2019. As shown in Exhibit 8 (see the Examples sheet in the downloadable Microsoft Excel workbook), the operating margin is lowest in the fastest growing product category, home care products. The other parts of the business, personal care and foods categories, enjoy higher margins but are growing more slowly. Exhibit 8: Unilever Revenue and Profit from Product Categories (€ millions, unless noted) Avg Growth Rate Segment 2019 2018 '19/'18 YoY 2017-2019 Beauty & Personal Care 21,868 20,624 6% 3% Foods & Refreshments 19,287 20,227 -5% -5% Home Care 10,825 10,131 7% 3% Total revenues. 51,980 50,982 2% 0% Underlying operating profit Beauty & Personal Care 4,960 4,543 9% Foods & Refreshments 3,382 3,576 -5% Home Care 1,605 1,344 19% Total 9,947 9,463 5% Underlying operating profit margin Beauty & Personal Care Foods & Refreshments Home Care Total 22.7% 22.0% 17.5% 17.7% 14.8% 13.3% 19.1% 18.6% 1. Determine the estimated sales, operating profit, and operating profit margin by using the following two approaches: (A) Assume total sales growth of 2.0% and overall underlying operating margin of 19.1% for the next five years, and (B) assume each individual segment's sales growth and under- lying operating margin continue at the same rate reported in 2019. Which approach will result in a higher underlying estimated operating profit after five years? Solution Exhibit 9 shows that operating profit after five years will be EUR 10,962 million under approach A and EUR11,549 million under approach B. The full calculations to support this solution are in the Examples sheet in the downloadable Microsoft Excel workbook. 2. Compare and explain the results under the two alternative approaches (A and B) in Question 1. Solution Approach A assumes a constant 2.0% total sales growth rate and a stable 19.1% underlying operating margin. Therefore, the operating profit growth rate is in line with the revenue growth rate and constant at 2.0%, which therefore assumes no difference in growth rates and profitability of the seg- ments. Approach B assumes growth rates of 6%, -5%, and 7% of sales for the Beauty & Personal Care, Foods & Refreshments, and Home Care segments. This results in a faster overall compounded growth rate than with Approach A (3% versus 2%) and an annual increase, on average, in the total underlying operating profit margin of 6 bps due to the mix effect of different segment margins. In 2024E, Approach A yields an underlying operating profit margin of 19.1% compared with 19.4% for Approach B. 3. Assume Unilever can grow segment revenues over the next five years at the following rates: Beauty & Personal Care 3.0%, Foods & Refreshments 2.0%, and Home Care 4.0%. But underlying operating profit margins in Beauty & Personal Care will fall 20 bps annually for the next five years (because of high competition, limited growth, and costs resulting from the adop- tion of sustainable packaging), and operating profit margins in the Foods & Refreshments and Home Care segments will increase by 15 and 50 bps, respectively, each year for the next five years (helped by increasing demand for the company's products and better utilization of its factories). Calculate the overall underlying operating profit margin in each of the next five years. Solution As shown in Exhibit 10, the overall underlying operating profit margin improves from 19.1% in 2019 to 19.5% in 2024 because the margin decline in Beauty & Personal Care is more than offset by the margin increase in Foods & Refreshments and the faster growing Home Care segment. The full calculations to support this solution are in the Examples sheet in the download- able Microsoft Excel workbook. EXAMPLE 7 Interest Expense Calculations Dutch grocer Ahold Delhaize, operating in several regions, has a debt structure with a relatively high amount of debt, primarily in the form of leases, as shown in Exhibit 12 (see the Examples worksheet in the downloadable Microsoft Excel workbook). Exhibit 11: Ahold's Debt, Interest Income, and Expense € millions 3 Jan. 2021 29 Dec. 2019 Average Loans 3,863 3,841 8,716 3,852 Other non-current financial liabilities (primarily leases) 8,905 8.811 Current financial liabilities 2,386 3,257 2,822 Gross debt 15,154 15.814 15,484 Less: cash and cash equivalents 2,933 3,717 3,325 Net debt 12,221 12,097 12.159 Interest income 35 65 Interest expense 138 175 Net interest expense 103 110 Source: Ahold Delhaize 2020 Annual Report 1. Calculate the interest rate on the average gross debt and interest rate on the average cash position for the year ended 3 Jan. 2021. Solution Interest rate on average gross debt is calculated as interest expense divided by average gross debt: (EUR138 million/EUR15,484 million) 0.89% or 89 bps. The interest rate on average cash position is interest income divided by the average cash position (EUR35 million/EUR3,325 million) = 1.05%. 2. Calculate the interest rate on the average net debt, assuming the other financial income and expenses are not related to the debt or cash balances, for the year ended 3 Jan. 2021. Solution The interest rate on the average net debt is calculated as net interest expense divided by average net debt (EUR103 million/EUR12,159 million) = 0.85% or 85 bps. EXAMPLE 8 Tax Rate Estimates ABC. a hypothetical company, operates in Countries A and B. The tax rate in Country A is 40%, and the tax rate in Country B is 10%. In the first year, the company generates an equal amount of profit before tax in each country, as shown in Exhibit 13 (see the Example 7 sheet in the downloadable Microsoft Excel workbook). Exhibit 12: Tax Rates That Differ by Jurisdiction A B Total Profit before tax 100 100 200 Effective tax rate 40% 10% 25% Tax 40 10 50 Net profit 60 90 150 1. What will happen to the effective tax rate for the next three years if the profit before tax in Country A is stable but the profit before tax in Country B grows 15% annually? Solution The effective tax rate will gradually decline because a higher proportion of profit will be generated in the country with the lower tax rate each year. In Exhibit 13, the effective tax rate declines from 25% in the beginning to 22% in the third year. Exhibit 13: Worksheet for Problem 1 Year 0 1 2 3 Profit before tax, Country A 100 100 100 100 Growth rate 0% 0% 0% Profit before tax, Country B 100 115 132 152 Growth rate 15% 15% 15% Total profit before tax 200 215 232 252 Effective tax rate, Country A 40% 40% 40% 40% Effective tax rate, Country B 10% 10% 10% 10% Year Total tax 0 50 52 Total effective tax rate 25% 1 24% 2 53 55 23% 3 22% 2. Evaluate the cash tax and effective tax rates for the next three years if the tax authorities in Country A allow some costs (e.g., accelerated depreciation) to be taken sooner for tax purposes. For Country A. the result will be a 50% reduction in taxes paid in the current year but an increase in taxes paid by the same amount in the following year (this happens each year). Assume stable profit before tax in Country A and 15% annual before-tax-profit growth in Country B. Solution The combined cash tax rate (last line in Exhibit 14) will be 15% in the first year and then rebound in subsequent years. Only the rate for the first year will benefit from a tax deferral, in subsequent years, the deferral for a given year will be offset by the addition of the amount postponed from the previ ous year. The combined effective tax rate will be unaffected by the deferral. As shown in Exhibit 14, beginning with the second year, the combined cash tax and effective tax rates decline over time but rernain identical to each other. The full calculations to support this solution are in the Example? sheet in the downloadable Microsoft Excel workbook. Exhibit 14: Worksheet for Problem 2 Year 0 1 2 3 Profit before tax. Country A 100 100 100 100 Growth rate 0% 0% 0% Profit before tax, Country B 100 115 132 152 Growth rate 15% 15% 15% Total profit before tax 200 215 232 252 Effective tax rate, Country A 40% 40% 40% 40% Effective tax rate, Country B 10% 10% 10% 10% Total tax per income statement 50 52 53 55 Total effective tax rate 25% 24% 21% 22% Cash taxes, Country A 21) 40 40 40 Cash taxes, Country B 10 12 13 15 Total cash tax 30 52 53 55 Year 0 1 2 3 Cash tax rate 15% 24% 23% 22% 3. Repeat the exercise of Problem 2, but now assume that Country B, rather than Country A, allows some costs to be taken sooner for tax purposes and that the tax effect described applies to Country B. Continue to assume stable profit before tax in Country A and 15% annual profit growth in Country B. Solution The combined effective tax rate unchanged from Exhibit 13 and Exhibit 14. Because of the growth assumed for Country B, however, the annual tax postponement will result in a lower cash tax rate in Country 8 than the effective tax rate in Country B. Consequently, as shown in Exhibit 15, the combined cash tax rate will be less than the effective tax rate. Exhibit 15: Worksheet for Problem 3 Year 0 1 2 3 Profit before tax, Country A 100 100 100 100 Growth rate 0% 0% 0% Profit before tax, Country B 100 115 132 152 Growth rate 15% 15% 15% Total profit before tax 200 215 131 252 Effective tax rate, Country A 40% 40% 40% 40% Effective tax rate, Country B 10% 10% 10% 10% Total tax per income statement 50 52 53 55 Total effective tax rate 25% 24% 23% 22% Cash tases, Country A 40 40 40 40 Cash taxes, Country B 5 11 12 14 Total cash tax 45 51 52 54 Cash tax rate 23% 24% 23% 22% EXAMPLE 9 Working Capital Forecasts with Efficiency Ratios Exhibit 17 (see the Examples sheet in the downloadable Microsoft Excel work- book) shows revenues, COGS, and year-end working capital account balances for YY Ltd.. a fictional company, for years 1-3. Based on the data in the Exhibit. answer questions 1-3. Exhibit 16: YY Ltd. Financial Data, millions of CNY Year 1 2 3 Revenue 174,915 205,839 245,866 COGS 152,723 177,285 6,919 209,114 Accounts receivable 5,598 10:161 Inventory 29.481 32.585 Accounts payable 46.287 41.671 59,528 72.199 1. Calculate days sales outstanding, inventory days on hand, and days payable outstanding for years 1, 2, and 3, using year-end balances and assuming a 365-day fiscal year Solution Days sales outstanding is equal to accounts receivable/(revenues/365), in-ventory days on hand is equal to inventories/(COGS/365), and days payable outstanding is equal to accounts payable/(COGS/365). Using the data in Exhibit 17, the three ratios for years 1-3 are as follows. Full calculations to support this solution are in the Example8 sheet in the downloadable Microsoft Excel workbook Year 1 2 3 Days sales outstanding 12 12 15 Inventory days on hand 70 67 73 Days payable outstanding 111 123 126 2. Your colleague Liang forecasts revenue growth of 18%, 16%, and 13% and gross margins of 17%, 17%, and 16% in years 4, 5, and 6. respectively. Using Liang forecasts, calculate expected revenue and COGS in each of year 4. 5. and 6. Solution Using Liang's forecasts for annual revenue growth and gross margins and the data in Exhibit 17, expected revenue and COGS for years 4-6 are as follows, shown in millions of CNY. Full calculations to support this solution are in the Example8 sheet in the downloadable Microsoft Excel workbook Year (millions of CNY) Revenue Growth rate 1 174,915 2 205,839 3 245,366 4 290,122 5 336,541 16% 6 380,292 13% 18% 190 18% Year (millions of CNY) COGS 1 2 152,723 Gross margin 13% 177,285 14% 3 4 5 6 209.114 240,801 279.329 319,445 15% 17% 17% 16% 3. Liang forecasts that days sales outstanding, inventory days on hand, and days payable outstanding in years 4, 5, and 6 will remain the same as year 3 amounts. Using Liang's forecasts as well as forecasted revenue and COGS. calculate expected accounts receivable, inventory, and accounts payable year-end balances for each of year 4, 5 and 6. Solution Each of the efficiency ratios can be rearranged to yield working capital balances because we have values for two of the three variables in them: the efficiency ratios are assumed to remain constant from year 3 levels and the revenue and COGS variables have already been forecast. Days sales outstanding is equal to accounts receivable/(revenues/365), thus, accounts receivable is equal to days sales outstanding x (revenues/365). Similarly, inventories is equal to inventory days on hand x (COGS/365) and accounts payable is equal to days payable outstanding (COGS/365). Using the data in Exhibit 17 and the revenue and COGS forecasts, year-end working capital account balances are as follows, shown in millions of CNY Full calculations to support this solution are in the Examples sheet in the downloadable Microsoft Excel workbook. Year (millions of CNY) 1 2 3 4 5 6 Revenue COGS 174,915 205.439 245.866 290,122 336.541 380,292 152,723 177,285 209.114 240.801 279,329 319,445 Accounts receivable 5.598 6,949 10.161 11,990 13,908 15,716 Inventories Accounts payable 29.481 32.585 41.671 47.985 55.663 63.657 46.287 59.528 1.199 83.139 96442 110,292 Daya sales outstanding 12 12 15 15 15 15 Inventory days on hand 70 67 73 73 Days payable outstanding 123 126 126 126 126