Fill This Form To Receive Instant Help
Homework answers / question archive / 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 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.
In the Method1 worksheet:
1.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.
2.Complete Rows 82 and 83 to determine the values needed to balance the balance sheet.
3.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:
1.Complete Rows 53, 54 and 55 to determine the values needed to balance the balance sheet.
2.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. Class A | |||||
INCOME STATEMENT | |||||
Source: FactSet Fundamentals | Historical | Pro Forma | |||
JAN '20 | 01-2021F | 01-2022F | 01-2023F | ||
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.4 | 4.9 | |
Pretax Income | -252.4 | 81.3 | 491.0 | 965.7 | |
Taxes | 20.3 | 122.7 | 241.4 | ||
Net Income | -252.4 | 61.0 | 368.2 | 724.3 | |
All figures in millions of U.S. Dollar | |||||
Chewy, Inc. Class A | |||||
BALANCE SHEET | |||||
Source: FactSet Fundamentals | Historical | Pro Forma | |||
JAN '20 | 01-2021F | 01-2022F | 01-2023F | Points | |
Assets | |||||
Cash & Short-Term Investments | 212.1 | 233.3 | 256.6 | 282.3 | |
Excess Cash | 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 | 692.8 | 762.0 | 838.2 | |
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,025.6 | 1,128.1 | 1,240.9 | |
Liabilities & Shareholders' Equity | |||||
Revolver | 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.2 | 749.5 | |
Total Equity | -404.0 | (343.0) | 25.2 | 749.5 | |
Total Liabilities & Shareholders' Equity | 932.3 | 1,125.4 | 1,638.9 | 2,523.0 | |
All figures in millions of U.S. Dollar | |||||
Balance Sheet Check | 99.8 | 510.8 | 1,282.1 | ||
Chewy, Inc. Class A | |||||
CASH FLOW STATEMENT | |||||
Source: FactSet Fundamentals | Historical | Pro Forma | |||
JAN '20 | 01-2021F | 01-2022F | 01-2023F | Points | |
Operating Activities | |||||
Net Income / Starting Line | -252.4 | 1.0 | |||
Depreciation, Depletion & Amortization | 48.9 | 2.0 | |||
Other Funds | 136.0 | 0.0 | 0.0 | 0.0 | |
Changes in Working Capital | 123.0 | ||||
Accounts Receivable | 2.0 | ||||
Inventories | 2.0 | ||||
Other Current Assets | 2.0 | ||||
Accounts Payable | 2.0 | ||||
Other Current Liabilities | 2.0 | ||||
Net Operating Cash Flow | 55.4 | 1.0 | |||
Investing Activities | |||||
Capital Expenditures | -48.64 | 2.0 | |||
Other Funds | -1.23 | (27.6) | (30.4) | (33.4) | |
Net Investing Cash Flow | -49.86 | 1.0 | |||
Financing Activities | |||||
Change in Capital Stock | 110.35 | 0.0 | 0.0 | 0.0 | |
Issuance/Reduction of Debt, Net | -10.53 | 2.0 | |||
Other Funds | 18.38 | 0.0 | 0.0 | 0.0 | |
Net Financing Cash Flow | 118.19 | 1.0 | |||
Net Change in Cash | 123.76 | 1.0 | |||
Beginning Excess Cash/(Revolver) | 1.0 | ||||
Ending Excess Cash/(Revolver) | 0.00 | 1.0 | |||
All figures in millions of U.S. Dollar |
Chewy, Inc. Class A | |||||
INCOME STATEMENT | |||||
Source: FactSet Fundamentals | Historical | Pro Forma | |||
JAN '20 | 01-2021F | 01-2022F | 01-2023F | ||
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.4 | 4.9 | |
Pretax Income | -252.4 | 81.3 | 491.0 | 965.7 | |
Taxes | 20.3 | 122.7 | 241.4 | ||
Net Income | -252.4 | 61.0 | 368.2 | 724.3 | |
All figures in millions of U.S. Dollar | |||||
Chewy, Inc. Class A | |||||
BALANCE SHEET | |||||
Source: FactSet Fundamentals | Historical | Pro Forma | |||
JAN '20 | 01-2021F | 01-2022F | 01-2023F | Points | |
Assets | |||||
Cash & Short-Term Investments | 212.1 | 233.3 | 256.6 | 282.3 | |
Excess Cash | 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 | 692.8 | 762.0 | 838.2 | |
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,025.6 | 1,128.1 | 1,240.9 | |
Liabilities & Shareholders' Equity | |||||
Revolver | 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.2 | 749.5 | |
Total Equity | -404.0 | (343.0) | 25.2 | 749.5 | |
Total Liabilities & Shareholders' Equity | 932.3 | 1,125.4 | 1,638.9 | 2,523.0 | |
All figures in millions of U.S. Dollar | |||||
Balance Sheet Check | 99.8 | 510.8 | 1,282.1 | ||
Total Assets, Except Excess Cash | 2.0 | ||||
Total Liabilities & Equity, Except Revolver | 2.0 | ||||
Excess Cash/(Revolver) | 1.0 |