The Method 1 tab contains a projected income statement and unbalanced balance sheet for Chewy, Inc, including
the most recent year's data for those two statements as well as the cash flow statement. The Method 2 tab
contains the same projected income statement and unbalanced balance sheet, but no cash flow statement. Work
in each tab separately--they should NOT be linked together in your formulas. Your tasks will be to complete the
Cash Flow Statement and balance the balance sheet using both techniques covered in this week's material.
This part of the assignment is worth a total of 36 points. Points possible for each row are shown in column G.
Always use formulas with cell references when possible. All answers should go in the relevant blue-shaded cells.
Do not add or delete cells, rows, columns or worksheets. Do not make changes to cells that are not blue-shaded.
In the Method1 worksheet:
Complete the Cash Flow Statement by filling in the rows with the blue background, starting with Row 58 and ending
with Row 80. Any item that is a cash inflow should display as a positive number, and any item that is a cash outflow
should display as a negative number.
Complete Rows 82 and 83 to determine the values needed to balance the balance sheet.
Complete the balance sheet rows for Excess Cash and Revolver with formulas needed to balance the balance sheet
as described in Method 1 in the class material for this week. Both rows must contain the appropriate formulas even
if your balance sheet balances after filling in only one. Your balance sheet must be set up to always balance,
regardless of changes that are made to the forecast numbers.
In the Method2 worksheet:
Complete Rows 53, 54 and 55 to determine the values needed to balance the balance sheet.
Complete the balance sheet rows for Excess Cash and Short Term Debt with formulas needed to balance the
balance sheet as described in Method 2 in the class material for this week. Both rows must contain the appropriate
formulas even if your balance sheet balances after filling in only one. Your balance sheet must be set up to always
balance, regardless of changes that are made to the forecast numbers.
Chewy, Inc, including
Method 2 tab
ow statement. Work
be to complete the
eek's material.
wn in column G.
t blue-shaded cells.
re not blue-shaded.
h Row 58 and ending
hat is a cash outflow
e the balance sheet
priate formulas even
ways balance,
o balance the
tain the appropriate
be set up to always
59036
Chewy, Inc. Class A
INCOME STATEMENT
Source: FactSet Fundamentals Historical Pro Forma
JAN '21 01-2022F 01-2023F 01-2024F
Sales 4,846.7 5,331.4 5,864.6 6,451.0
Cost of Goods Sold (COGS) incl. D&A 3,751.5 3,830.5 3,911.4 3,994.3
COGS excluding D&A 3,702.7 3,776.7 3,852.3 3,929.3
Depreciation 40.3 44.3 48.7 53.6
Amortization of Intangibles 8.6 9.5 10.4 11.4
Gross Income 1,095.2 1,500.9 1,953.2 2,456.7
SG&A Expense 1,347.9 1,415.3 1,457.8 1,486.1
EBIT (Operating Income) -252.7 85.6 495.4 970.6
Nonoperating Income - Net 0.4 0.0 0.0 0.0
Interest Expense - 4.3 4.7 5.2
Pretax Income -252.4 81.3 490.7 965.4
Taxes 20.3 122.7 241.4
Net Income -252.4 61.0 368.0 724.1
All figures in millions of U.S. Dollar
Chewy, Inc. Class A
BALANCE SHEET
Source: FactSet Fundamentals Historical Pro Forma
JAN '21 01-2022F 01-2023F 01-2024F Points
Assets
Cash & Short-Term Investments 212.1 233.3 256.6 282.3
Excess Cash 99.8 510.6 1,281.6 2.0
Short-Term Receivables 81.1 89.2 98.1 107.9
Inventories 317.8 349.6 384.5 423.0
Other Current Assets 18.8 20.7 22.7 25.0
Total Current Assets 629.8 792.6 1,272.6 2,119.9
Net Property, Plant & Equipment 297.8 327.6 360.3 396.3
Other Assets 4.7 5.2 5.7 6.3
Total Assets 932.3 1,125.4 1,638.7 2,522.5
Liabilities & Shareholders' Equity
Revolver 0.0 0.0 0.0 2.0
ST Debt & Curr. Portion LT Debt 15.5 15.5 15.5 15.5
Accounts Payable 683.0 751.4 826.5 909.1
Other Current Liabilities 402.0 442.2 486.4 535.1
Total Current Liabilities 1,100.5 1,209.1 1,328.4 1,459.7
Long-Term Debt 200.4 220.5 242.5 266.8
Other Liabilities 35.3 38.8 42.7 47.0
Total Liabilities 1,336.3 1,468.4 1,613.7 1,773.5
Preferred Stock (Carrying Value) 0.0 0.0 0.0 0.0
Common Equity -404.0 (343.0) 25.0 749.1
Total Equity -404.0 (343.0) 25.0 749.1
Total Liabilities & Shareholders' Equity 932.3 1,125.4 1,638.7 2,522.5
All figures in millions of U.S. Dollar
Balance Sheet Check 0.0 0.0 0.0
Chewy, Inc. Class A
CASH FLOW STATEMENT
Source: FactSet Fundamentals Historical Pro Forma
JAN '21 01-2022F 01-2023F 01-2024F Points
Operating Activities
Net Income / Starting Line -252.4 61.0 368.0 724.1 1.0
Depreciation, Depletion & Amortization 48.9 53.7 59.1 65.0 2.0
Other Funds 136.0 0.0 0.0 0.0
Changes in Working Capital 123.0
Accounts Receivable (8.1) (8.9) (9.8) 2.0
Inventories (31.8) (35.0) (38.5) 2.0
Other Current Assets (1.9) (2.1) (2.3) 2.0
Accounts Payable 68.3 75.1 82.6 2.0
Other Current Liabilities 40.2 44.2 48.6 2.0
Net Operating Cash Flow 55.4 181.4 500.5 869.8 1.0
Investing Activities
Capital Expenditures -48.64 (74.1) (81.5) (89.6) 2.0
Other Funds -1.23 (27.6) (30.4) (33.4)
Net Investing Cash Flow -49.86 (101.7) (111.8) (123.0) 1.0
Financing Activities
Change in Capital Stock 110.35 0.0 0.0 0.0
Issuance/Reduction of Debt, Net -10.53 20.0 22.0 24.3 2.0
Other Funds 18.38 0.0 0.0 0.0
Net Financing Cash Flow 118.19 20.0 22.0 24.3 1.0
Net Change in Cash 123.76 99.8 410.7 771.1 1.0
Beginning Excess Cash/(Revolver) 0.0 99.8 510.6 1.0
Ending Excess Cash/(Revolver) 0.00 99.8 510.6 1,281.6 1.0
All figures in millions of U.S. Dollar 27.0
outflow
59036
Chewy, Inc. Class A
INCOME STATEMENT
Source: FactSet Fundamentals Historical Pro Forma
JAN '21 01-2022F 01-2023F 01-2024F
Sales 4,846.7 5,331.4 5,864.6 6,451.0
Cost of Goods Sold (COGS) incl. D&A 3,751.5 3,830.5 3,911.4 3,994.3
COGS excluding D&A 3,702.7 3,776.7 3,852.3 3,929.3
Depreciation 40.3 44.3 48.7 53.6
Amortization of Intangibles 8.6 9.5 10.4 11.4
Gross Income 1,095.2 1,500.9 1,953.2 2,456.7
SG&A Expense 1,347.9 1,415.3 1,457.8 1,486.1
EBIT (Operating Income) -252.7 85.6 495.4 970.6
Nonoperating Income - Net 0.4 0.0 0.0 0.0
Interest Expense - 4.3 4.7 5.2
Pretax Income -252.4 81.3 490.7 965.4
Taxes 20.3 122.7 241.4
Net Income -252.4 61.0 368.0 724.1
All figures in millions of U.S. Dollar
Chewy, Inc. Class A
BALANCE SHEET
Source: FactSet Fundamentals Historical Pro Forma
JAN '21 01-2022F 01-2023F 01-2024F Points
Assets
Cash & Short-Term Investments 212.1 233.3 256.6 282.3
Excess Cash 99.8 510.6 1,281.6 2.0
Short-Term Receivables 81.1 89.2 98.1 107.9
Inventories 317.8 349.6 384.5 423.0
Other Current Assets 18.8 20.7 22.7 25.0
Total Current Assets 629.8 792.6 1,272.6 2,119.9
Net Property, Plant & Equipment 297.8 327.6 360.3 396.3
Other Assets 4.7 5.2 5.7 6.3
Total Assets 932.3 1,125.4 1,638.7 2,522.5
Liabilities & Shareholders' Equity
Revolver 0.0 0.0 0.0 2.0
ST Debt & Curr. Portion LT Debt 15.5 15.5 15.5 15.5
Accounts Payable 683.0 751.4 826.5 909.1
Other Current Liabilities 402.0 442.2 486.4 535.1
Total Current Liabilities 1,100.5 1,209.1 1,328.4 1,459.7
Long-Term Debt 200.4 220.5 242.5 266.8
Other Liabilities 35.3 38.8 42.7 47.0
Total Liabilities 1,336.3 1,468.4 1,613.7 1,773.5
Preferred Stock (Carrying Value) 0.0 0.0 0.0 0.0
Common Equity -404.0 (343.0) 25.0 749.1
Total Equity -404.0 (343.0) 25.0 749.1
Total Liabilities & Shareholders' Equity 932.3 1,125.4 1,638.7 2,522.5
All figures in millions of U.S. Dollar
Balance Sheet Check 0.0 0.0 0.0
Total Assets, Except Excess Cash 1,025.6 1,128.1 1,240.9 2.0
Total Liabilities & Equity, Except Revolver 1,125.4 1,638.7 2,522.5 2.0
Excess Cash/(Revolver) 99.8 510.6 1,281.6 1.0
9.0
59036