Instuctions USE THE FAR LEFT COLUMN AS BASE CASE MAKE ALL CHANGES FROM THERE Remember to consider BOTH the balance sheet impact and the Income statement Start each with the base case First calculate the impact of a 8 decrease in transport, and then 10 decrease in warehousing AND simultaneously a 5 decrease in inventory Last column, Impact of service failure improvement Simultaneously do the following Increase net sales by 2 (fewer returns) Increase COGS by 2 (fewer returns) Increase inventory by 1 (higher inv to fill order) Fill out the blank in Excel and answer following questions 1 Help Katie present the profit leverage effect concept to RR management How should she explain it to them so that they can truly understand the equivalent sales impact of logistics 2 Use the Strategic Profit Model to calculate the balance sheet, income statement and key ratio effects for each of the scenarios Develop a recommendation for Katie to present to top management, including the aspects of the SPM she should present, and why 3 What potential goal conflicts would your recommendation create among merchandising, logistics, and stores 4 Identify two common metrics that merchandising, logistics, and the store could share that might reduce existing conflicts How would these metrics reduce conflict Home Insert Page Layout Formulas Data Review View D4 Trans Cost 8 reduction whse 10 Inv 5 reduction Service improved 1 internet retailers 2 in millions) 3 Sales 4 Cost of Goods Sold 5 ross Margin 6 Transportation 7 Warehousing 8 Inventory Carrying 9 Other Operating Cost OOC 10 Total Operating Cost TOC Symbol Base 74452 47546 26906 6635 8585 4733 6208 26161 GM S CGS TC WC Earnings before interest 745 239 161 345 11 and taxes 12Interest 13 Taxes 14 Net Income 15 16 Asset Deployment 17 Cash 18 Accounts Receivable 19 Inventory 20 Total Current Assets 21 Fixed Assets 22 Total Assets 23 Liabilities 24 Current liabilities 25 Long term debt 26 Total Liabilities 27 Stockholder's Equity EBIT INT TX 372 450 390 CA AR IN 8658 4767 7411 20836 10949 31785 TA 18848 3191 22039 9746 LTD SE 28 Total Liabilities Equity TL 29 Ratio Analysis 30 Profit Margin 31 Return on Assets 32 Inventory Turns year CGS IN 31785 0 46 1 09 6 42 NI TA Transportation as of warehousing as of Inventory carrying as 33 sales 8 91 34 sales WC s 11 53 6 36 35 of sales 36 37 Assume that the tax amount is provided by the tax dept

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 06, 2024

Instuctions: USE THE FAR LEFT COLUMN AS BASE CASE-- MAKE ALL CHANGES FROM THERE Remember to consider BOTH the balance sheet impact and the Income

image text in transcribed

Instuctions:

USE THE FAR LEFT COLUMN AS BASE CASE-- MAKE ALL CHANGES FROM THERE
Remember to consider BOTH the balance sheet impact and the Income statement
Start each with the "base case. First: calculate the impact of a 8% decrease in transport,
and then 10% decrease in warehousing AND simultaneously a 5% decrease in inventory.
Last column,
Impact of service failure improvement:
Simultaneously do the following:
Increase net sales by 2% (fewer returns)
Increase COGS by 2% (fewer returns)
Increase inventory by 1% (higher inv. to fill order) Fill out the blank in Excel and answer following questions 1. Help Katie present the profit-leverage effect concept to RR management. How should she explain it to them so that they can truly understand the equivalent sales impact of logistics? 2. Use the Strategic Profit Model to calculate the balance sheet, income statement and key ratio effects for each of the scenarios. Develop a recommendation for Katie to present to top management, including the aspects of the SPM she should present, and why. 3. What potential goal conflicts would your recommendation create among merchandising, logistics, and stores? 4. Identify two common metrics that merchandising, logistics, and the store could share that might reduce existing conflicts. How would these metrics reduce conflict?

Home Insert Page Layout Formulas Data Review View D4 Trans. Cost 8% reduction whse 10%/Inv 5% reduction Service improved** 1 internet retailers 2 in millions) 3 Sales 4 Cost of Goods Sold 5 ross Margin 6 Transportation 7 Warehousing 8 Inventory Carrying 9 Other Operating Cost OOC 10 Total Operating Cost TOC Symbol Base 74452 47546 26906 6635 8585 4733 6208 26161 GM-S-CGS TC WC Earnings before interest 745 239 161 345 11 and taxes 12Interest 13 Taxes 14 Net Income 15 16 Asset Deployment 17 Cash 18 Accounts Receivable 19 Inventory 20 Total Current Assets 21 Fixed Assets 22 Total Assets 23 Liabilities 24 Current liabilities 25 Long term debt 26 Total Liabilities 27 Stockholder's Equity EBIT INT TX 372 450 390 CA AR IN 8658 4767 7411 20836 10949 31785 TA 18848 3191 22039 9746 LTD SE 28 Total Liabilities & Equity TL 29 Ratio Analysis 30 Profit Margin 31 Return on Assets 32 Inventory Turns/year CGS/IN 31785 0.46% 1.09% 6.42 NI/TA Transportation as % of warehousing as % of Inventory carrying as % 33 sales 8.91% 34 sales WC/s 11.53% 6.36% 35 of sales 36 37 Assume that the tax amount is provided by the tax dept