Executive MBA -
Accounting for Executives
PetCo vs Chewy
Financial & Corporate Strategy Comparison
To analyze and forecast the financial statements of two public listed retail firms with the ultimate aim of assessing their profit sustainability.
- Download their most recent annual reports.
- You will need to construct a spreadsheet to help you conduct your financial statement analysis: you can utilize the Starbucks spreadsheet as a template (see attached)
- The forecasted balance sheet and income statement should be for a maximum of five years.
- Use only the annual reports provided by the firms. Do not read research reports from analysts etc. Do your own independent analysis.
- Use end of fiscal year data even if the fiscal years end on different dates for the firms you are analyzing.
- You will be required to prepare a 10-page summary to inform a senior executive what you have learnt from your analysis. This analysis should not be chatty; it should be based solely on your business strategy and numerical/ratios analysis.
- Submit the write-up (IN WORD format) and your spreadsheet.
Starbucks Introduction Starbucks Case Study Forecast; Ratio Analysis and Valuation INSTRUCTIONS: Fill the information highlighted in the sheets 'Forecast' Other worksheets and cells are locked to avoid any confusion Password to unlock: STARBUCKS NOTES IF YOU UNLOCK THE EXCEL: Do NOT change any pre-existing formulas in any of the worksheets. Do NOT insert or delete cells, rows, columns or worksheets. Editing any sheet of excel CAN cause ERRORS or even Excel to FREEZE LINKS TO SHEETS WHERE YOU CAN EDIT: ForecastForecast LINKS TO DIFFERENT SHEETS: Published Income StatementsPub_IS Published Balance SheetsPub_BS Reformulated Income StatementRef_IS Reformulated Balance SheetRef_BS Forecasted Income StatementFor_IS Forecasted Balance SheetFor_BS Forecasted Cash Flow StatementFor_CFS Ratio AnalysisRatio_Analysis Cost of CapitalWACC Stock ValuationValuation Forecast SHARE PRICE BASED ON CURRENT ASSUMPTIONS121 GrowthEffectDriverActualActualActualForecastForecastForecastForecastTerminalForecastForecastForecastForecastTerminal 2018201920202021202220232024202520212022202320242025 Sales by Company-operated RetailGrowth, Margin & Efficiency11.6%9.4%-11.0%25.0%10.0%8.0%4.0%2.0%ROCE152.21%279.03%45.15%101.12%-2.12% Licensing RevenuesGrowth, Margin & Efficiency12.6%8.4%-19.1%25.0%10.0%8.0%4.0%2.0%EBIT Margin23.30%17.73%6.64%-1.33%0.52% CPG, Foodservice and other RevenuesGrowth, Margin & Efficiency-0.2%-12.1%-3.0%25.0%10.0%8.0%4.0%2.0%NOATO6.5315.746.80-76.11-4.08 MarginsEffectDriverActualActualActualForecastForecastForecastForecastTerminalROE136.23%-142.21%-13.18%8.24%0.44% 20182019202020212022202320242025 Sales by Company-operated RetailGrowth, Margin & Efficiency11.6%9.4%-11.0%25.0%10.0%8.0%4.0%2.0%ROA23.46%16.57%3.81%-2.26%-0.13% Licensing RevenuesGrowth, Margin & Efficiency12.6%8.4%-19.1%25.0%10.0%8.0%4.0%2.0%NP Margin18.28%13.56%3.93%-2.59%-0.16% CPG, Foodservice and other RevenuesGrowth, Margin & Efficiency-0.2%-12.1%-3.0%25.0%10.0%8.0%4.0%2.0%ATO1.281.220.970.880.79 Cost of SalesMargin% of Sales by Company-operated Retail40.3%39.6%40.2%42.0%42.0%42.0%42.0%42.0%Leverage5.81-8.58-3.46-3.64-3.46 Store Operating ExpensesMargin% of Sales by Company-operated Retail48.1%48.7%56.2%58.0%58.0%58.0%58.0%58.0%DUPONT CHECK Other Operating ExpensesMargin% of Total revenues2.2%1.4%1.8%1.8%1.7%1.8%1.8%1.7%ROCE- 0- 0- 0- 0- 0 General and Administrative ExpensesMargin% Total revenues6.9%6.9%7.1%7.5%7.5%7.5%7.5%7.5%ROA- 0- 0- 0- 0- 0 Nonoperating income / expense, netMarginAmount1,651.2487.0(278.7)(300.0)(300.0)(300.0)(300.0)(300.0)ROE- 0- 0- 0- 0- 0 Interest RateNet Profit Margin% of Average Net Debt-4.5%5.6%3.8%5.6%5.6%5.6%5.6%5.6% EfficiencyEffectDriverActualActualActualForecastForecastForecastForecastTerminal 20182019202020212022202320242025 Sales by Company-operated RetailGrowth, Margin & Efficiency11.6%9.4%-11.0%25.0%10.0%8.0%4.0%2.0% Licensing RevenuesGrowth, Margin & Efficiency12.6%8.4%-19.1%25.0%10.0%8.0%4.0%2.0% CPG, Foodservice and other RevenuesGrowth, Margin & Efficiency-0.2%-12.1%-3.0%25.0%10.0%8.0%4.0%2.0% ReceivablesEfficiency% Licensing Revenues and CPG, Foodservice and other revenues13.8%17.7%20.3%21.0%21.0%21.0%21.0%21.0% InventoriesEfficiency% Cost of Sales17.7%17.9%20.2%21.0%21.0%21.0%21.0%21.0% Prepaid Expenses and Other AssetsNOAT & ATO Efficiency% Total revenue5.9%1.8%3.1%4.0%4.0%4.0%4.0%4.0% Equity and Other InvestmentsNOAT & ATO Efficiency% Growth in Investment-30.5%18.3%20.9%18.5%19.7%19.7%19.7%19.7% PPE TurnoverCapital Expenditure, NOATO & ATO Efficiency4.564.292.262.102.102.102.102.10 Goodwill & Other assetsNOAT & ATOAmount5,130.76,518.06,507.86,507.86,507.86,507.86,507.86,507.8 Investments (short-term and long-term)ATOAmount449.2290.5487.3487.3487.3487.3487.3487.3 Accounts PayableWorking Capital Efficiency% Cost of Sales14.0%14.0%14.0%14.0%14.0%13.0%13.0%13.0% Capital EmployedEffectDriverActualActualActualForecastForecastForecastForecastTerminal 20182019202020212022202320242025 Shareholders Equity (sharecapital)Leverage & Capital EmployedNumber1382.71221.21172.81172.81172.81172.81172.81172.8 Long-term DebtCapital EmployedAmount9440.11116717597.117597.117597.117597.117597.117597.1 Ratio_Analysis Starbucks: Financial Ratios: Actual - Terminal ActualActualActualActualForecastForecastForecastForecastTerminal 201720182019202020212022202320242025 Return on Capital Employed (ROCE) EBIT (before-tax)5,758.94,700.71,561.7-390.5167.9-319.5171.9-155.4 Average Capital Employed (Average Net Operating Assets)3,783.41,684.73,459.2-386.2-7,932.5-15,252.8-23,135.8-31,031.7 Return on Captial employed152.2%279.0%45.1%101.1%-2.1%2.1%-0.7%0.5% EBIT Margin EBIT (before-tax)5,758.94,700.71,561.7-390.5167.9-319.5171.9-155.4 Sales24,719.526,508.623,518.029,397.532,337.334,924.236,321.237,047.6 EBIT Margin23.3%17.7%6.6%-1.3%0.5%-0.9%0.5%-0.4% Net Operating Asset turnover Sales24,719.526,508.623,518.029,397.532,337.334,924.236,321.237,047.6 Average Net Operating Asets3,783.41,684.73,459.2-386.2-7,932.5-15,252.8-23,135.8-31,031.7 Net Operating Asset turnover6.5315.746.80-76.11-4.08-2.29-1.57-1.19 PPE turnover Sales24,719.526,508.623,518.029,397.532,337.334,924.236,321.237,047.6 Average PPE5,424.36,180.410,403.613,998.815,398.716,630.617,295.817,641.7 PPE turnover4.564.292.262.102.102.102.102.10 Days of trade receivable Sales24,719.526,508.623,518.029,397.532,337.334,924.236,321.237,047.6 Average trade receivables781.8786.2881.31,013.11,199.91,307.31,384.81,426.0 Days of trade receivable11.510.813.712.613.513.713.914.0 Days of Inventory Cost of sales - Food & Paper7,930.78,526.97,694.910,061.411,067.611,953.012,431.112,679.7 Average Inventory1,382.31,465.01,540.41,832.12,218.52,417.22,560.32,636.6 Days of Inventory63.662.773.166.573.273.875.275.9 Days of trade payable Cost of sales - Food & Paper7,930.78,526.97,694.910,061.411,067.611,953.012,431.112,679.7 Average trade payables980.91,184.51,093.81,203.21,479.01,549.81,581.11,628.2 Days of trade payable45.150.751.943.748.847.346.446.9 Net working capital days30.022.834.935.437.940.142.743.1 Current ratio Average currrent assets3,074.53,226.63,035.63,802.94,653.15,069.75,370.05,530.0 Average current liabilities3,311.84,295.63,926.33,317.73,866.64,151.14,336.54,465.8 Current Ratio0.930.750.771.151.201.221.241.24 Net Debt to Equity Net Debt234.68,189.912,758.94,921.12,832.8-4,601.4-7,755.3-15,286.6 Average Shareholder's equity3,316.4-2,527.6-7,015.2-9,226.2-11,809.5-14,368.4-16,957.4-19,510.7 Net Debt to Equity ratio0.1-3.2-1.8-0.5-0.20.30.50.8 Return on Assets Net Income4518.03594.6924.7-760.2-51.9-345.1297.7164.6 Average Total Assets19261.021688.024297.133589.640902.947435.353841.859866.1 Return on Assets23.5%16.6%3.8%-2.3%-0.1%-0.7%0.6%0.3% Asset Turnover Sales24719.526508.623518.029397.532337.334924.236321.237047.6 Average Total Assets19261.021688.024297.133589.640902.947435.353841.859866.1 Asset Turnover1.31.21.00.90.80.70.70.6 Net Profit Margin Net Income4518.03594.6924.7-760.2-51.9-345.1297.7164.6 Sales24719.526508.623518.029397.532337.334924.236321.237047.6 Net Profit Margin18.3%13.6%3.9%-2.6%-0.2%-1.0%0.8%0.4% Leverage Average Total Assets19,261.021,688.024,297.033,589.640,902.947,435.353,841.859,866.1 Average Shareholders Equity3,316.4-2527.6-7015.2-9226.2304060404-11809.4543971857-14368.4462125155-16957.368182385-19510.6980244639 Leverage5.8-8.6-3.5-3.6-3.5-3.3-3.2-3.1 Return on Equity Net Income4,518.03,594.6924.7-760.2-51.9-345.1297.7164.6 Average Shareholder's Equity3,316.4-2,527.6-7,015.2-9,226.2-11,809.5-14,368.4-16,957.4-19,510.7 Return on Equity136.2%-142.2%-13.2%8.2%0.4%2.4%-1.8%-0.8% Valuation Starbucks: Valuation Valuation Assumptions: Weighted Average Cost of Capital7.3% Average Shares Outstanding (forecasts) (in millions):1,172.8 Terminal Value Growth Assumption:2.00% Share price118.18/10/21 202020212022202320242025Terminal 12356Year Cash Flow from Operations (before tax)6,4375,2025,1424,6994,455 Less: Tax on Net Operating Income34556133-132-114 Less: Cash outflow from operating investments3,864-6334,7781,0005,655 Free Cash Flow10,6464,62510,0535,5679,99610,196 Perpetuity of Terminal Value191,335 Present value of Free Cash Flow9,9194,0158,1313,909131,709 Estimated Enterprise Value157,682 Less: Net Debt8,190 Estimated Equity Value149,492 Estimated Equity Value Per Share121 Pub_IS Starbucks: Consolidated Statements of Income: Fiscal Years 2017 - 2020 ($ Millions except per share amounts)2017201820192020 Sales by Company-operated Retail17,650.719,690.321,544.419,164.6 Licensing Revenues2,355.02,652.22,875.02,327.1 CPG, Foodservice and other Revenues2,381.12,377.02,089.22,026.3 Total revenues22,386.824,719.526,508.623,518.0 Cost of Sales(7,065.8)(7,930.7)(8,526.9)(7,694.9) Gross Profit15,321.016,788.817,981.715,823.1 Store Operating Expenses(8,486.4)(9,472.2)(10,493.6)(10,764.0) Other Operating Expenses(518.0)(554.9)(371.0)(430.3) Depreciation and Amortization(1,011.4)(1,247.0)(1,377.3)(1,431.3) General and Administrative Expenses(1,408.4)(1,708.2)(1,824.1)(1,679.6) Total operating expenses (excl cost of sales)(11,424.2)(12,982.3)(14,066.0)(14,305.2) Nonoperating income / expense, net(60.0)1,651.2487.0(278.7) Income from Equity Investees391.4301.2298.0322.5 Interest and Other Income, Net181.8191.496.539.7 Interest expense(92.5)(170.3)(331.0)(437.0) Income Before Income Taxes4,317.55,780.04,466.21,164.4 Provision for Income Taxes(1,432.6)(1,262.0)(871.6)(239.7) Net Income2,884.94,518.03,594.6924.7 SUPPLEMENTARY DATA Weighted avg. shares outstanding - diluted1,461.51,461.51,233.21,181.8 Cash dividends declared per share1.11.31.51.2 Dividends Amount1,534.61,929.21,837.51,923.5 Pub_BS Starbucks: Consolidated Balance Sheets: 2017 - 2020 ($ Millions)2017201820192020 Cash and Equivalents2,462.38,756.32,686.64,350.9 Short-Term Investments228.6181.570.5281.2 Operating Current Assets: Receivables870.4693.1879.2883.4 Inventories1,364.01,400.51,529.41,551.4 Prepaid Expenses and Other Assets358.11,462.8488.2739.5 Total Current Assets5,283.412,494.25,653.97,806.4 Long-term Investments542.3267.7220.0206.1 Equity and Other Investments481.6334.7396.0478.7 Net Property, Plant and Equipment4,919.55,929.16,431.714,375.5 Goodwill & Other assets3,138.85,130.76,518.06,507.8 Total Non-Current Assets9,082.211,662.213,565.721,568.1 Total Assets14,365.624,156.419,219.629,374.5 Check---- Operating current liabilities Accounts Payable782.51,179.31,189.7997.9 Accrued Compensation and Related Costs524.5656.8664.6696.0 Accrued Occupancy Costs151.3164.2176.9162.7 Accrued expenses1,473.91,691.12,868.51096.2 Current Portion of Long-Term Debt-349.9-2937.5 Total Current Liabilities2,932.24,041.34,899.75,890.3 Other long-term liabilities2,043.89,849.19,383.916624 Long-Term Debt3,932.69,090.211,167.014,659.6 Total Non-Current Liabilities5,976.418,939.320,550.931,283.6 Tota Liabilities8,908.622,980.625,450.637,173.9 Shareholder's equity Common Stock & Additional paid-in capital49.448.743.5380.8 Retained Earnings5,563.21,457.4(5,771.2)(7,815.6) Accumulated Other Comp. Income/(Loss)(155.6)(330.3)(503.3)(364.6) Total Common Shareholders' Equity5,457.01,175.8(6,231.0)(7,799.4) Total Liabilities and Shareholder's Equity14,365.624,156.419,219.629,374.5 Check- 0- 0- 0- 0 SUPPLEMENTARY DATA Capital expenditure (net)14341976.41806.61483.6 Ref_IS Starbucks: Restated Income Statement: Fiscal Years 2017 - 2020 ($ Millions except per share amounts)2017201820192020 Sales by Company-operated Retail17,650.719,690.321,544.419,164.6 Licensing Revenues2,355.02,652.22,875.02,327.1 CPG, Foodservice and other Revenues2,381.12,377.02,089.22,026.3 Total revenues22,386.824,719.526,508.623,518.0 Cost of Sales(7,065.8)(7,930.7)(8,526.9)(7,694.9) Gross Profit15,321.016,788.817,981.715,823.1 Store Operating Expenses(8,486.4)(9,472.2)(10,493.6)(10,764.0) Other Operating Expenses(518.0)(554.9)(371.0)(430.3) Depreciation and Amortization(1,011.4)(1,247.0)(1,377.3)(1,431.3) General and Administrative Expenses(1,408.4)(1,708.2)(1,824.1)(1,679.6)