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