Fill This Form To Receive Instant Help

Help in Homework
trustpilot ratings
google ratings


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

Accounting

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

pur-new-sol

Purchase A New Answer

Custom new solution created by our subject matter experts

GET A QUOTE