Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please answer the Central Wave tab using the data from West Wave and Exhibit Data. Please also use cell referencing. Thank you. Exhibit 1 Simplified

image text in transcribed

Please answer the "Central Wave" tab using the data from "West Wave" and Exhibit Data. Please also use cell referencing. Thank you.

image text in transcribed Exhibit 1 Simplified Income Statement for the West Wave Panel A: Forecasts without the ERP system Units [1] = Exhibit 3 Revenue [2] = Exhibit 3 COGS [3] = [2] - [4] Margin [4] = Exhibit 3 2000 2001 2002 2003 2,271 477,784 418,925 58,859 ### ### 418,925 ### 2,271 477,784 418,925 58,859 ### ### 418,925 ### Price per unit COGS per unit Margin per unit [5] = [2] / [1] [6] = [3] / [1] [7] = [4] / [1] 210.4 184.5 25.9 210.4 184.5 25.9 210.4 184.5 25.9 210.4 184.5 25.9 Percentage margin [8] = [7] / [5] 12.3% 12.3% 12.3% 12.3% DSI Inventory [9] = Exhibit 3 [10] = [3]*[9]/365 45 51,648 ### 51,648 45 51,648 ### 51,648 36 0.06% 3 93 0.25% 8 143 0.25% 12 143 0.25% 12 Panel B: Impact of the ERP system Additional Units [11]=[31] Additional Margin [12] = Exhibit 5 DSI reduction [13]=[35] Panel C: Forecasts with the ERP system Units [14]=[1]+[11] Revenue [15]=[14]*[18] COGS [16]=[14]*[19] Margin [17]=[14]*[20] 2,307 485,632 425,515 60,117 2,364 498,748 436,060 62,688 2,414 509,301 445,286 64,015 2,414 509,301 445,286 64,015 Price per unit COGS per unit Margin per unit [18]=[6]/(1-[21]) [19]=[6] [20]=[18]-[19] 210.5 184.5 26.1 211.0 184.5 26.5 211.0 184.5 26.5 211.0 184.5 26.5 Percentage margin [21]=[8]+[12] 12.4% 12.6% 12.6% 12.6% DSI Inventory [22]=[9]-[13] [23]=[22]*[16]/365 33 40,259 33 40,259 Panel D: Calculation of additional units Pre-ERP units [24]=[1] Pre-ERP Availability [25] = Exhibit 3 Target Availability [26]=Case data Increase in Availability [27]=([26]-[25])/[25] Additional Sales [28]=Case data Percentage improvement [29] = Exhibit 4 Additional units in year [30]=[24]*[27]*[28]*[29] Additional units [31]=cumulative[30] Panel E: Calculation of inventory savings Target DSI Reduction [32]=Case data Percentage improvement [33] = Exhibit 4 DSI Reduction in year [34]=[32]*[33] DSI Reduction [35]=cumulative[34] 42 48,963 37 44,442 2,271 0.735 0.92 25% 25% 25% 36 36 2,271 0.735 0.92 25% 25% 40% 57 93 2,271 0.735 0.92 25% 25% 35% 50 143 2,271 0.735 0.92 25% 25% 0% 0 143 12 25% 3 3 12 40% 4.8 7.8 12 35% 4.2 12 12 0% 0 12 2004 2005 2,271 477,784 418,925 58,859 2,271 477,784 418,925 58,859 210.4 184.5 25.9 210.4 184.5 25.9 12.3% 12.3% 45 51,648 45 51,648 143 0.25% 12 143 0.25% 12 2,414 509,301 445,286 64,015 2,414 509,301 445,286 64,015 211.0 184.5 26.5 211.0 184.5 26.5 12.6% 12.6% 33 40,259 33 40,259 2,271 0.735 0.92 25% 25% 0% 0 143 2,271 0.735 0.92 25% 25% 0% 0 143 12 0% 0 12 12 0% 0 12 Complete (All Yellow Cells) 1. South, Central, North Waves 2. Complete Combined Simplified Income Statement Combined Waves 3. Complete Capital Expenditures and Depreciation Schedule 4. Complete Incremental Operating Expenses 5. Complete Incremental Operating Cash Flows bined Waves Whirlpool Europe Data Exhibit 1 Whirlpool Europe's Manufacturing Sites Location Amiens France Norrkoping Sweden Poprad Slovakia Neunkirchen Germany Schomdorf Germany Gassinetta Italy Naples Italy Siena Italy Trento Italy Isithebe South Africa Products Washers and dryers Microwave Ovens Washers Dishwashers Washers Refrigerators and Cooking Appliances Washers Chest Freezers Refrigerators and Freezers Refrigerators and Freezers ? ? Exhibit 2A Project Atlantic implementation groupingsa Wave West Belgium France Netherlands Plus: Warehouse Management & Physical Distribution Wave South Italy Portugal Spain Wave Central Czech Republic Hungary Poland Slovakia Wave North Denmark Finland Ireland Norway Sweden United Kingdom South May 2000 Feb 2001 Central Mar 2001 Dec 2001 Exhibit 2B Wave implementation schedule Start Date: End Date: West May 1999 Apr 2000 Exhibit 3 Inventory 1997 data and projected improvements 1997 inventory data Wave West South Central North DSI 45 51 67 55 Product Availability 73.50% 83.10% 76.80% 83.20% X 1000 Units Sold Adjusted 2271.14 1415.95 977.67 1443.16 Exhibit 4 Improvements in DSI and Availability by Year and Wave Wave West South Central North 2000 25% 2001 40% 35% 2002 35% 40% 40% 2001 0.25% 0.10% 2002 0.25% 0.25% 0.13% Exhibit 5 Margin Improvements by Year and Wave Wave West South Central North 2000 0.06% Exhibit 6 Forecasted Other Expense Savings by Year (000s US$) 2000 2001 Order Desk Headcount 0 190 Finance Headcount 81 135 Warehouse Space 18 72 Bad Debit Expense 102 512 Information Systems 420 840 621 1,749 2002 411 216 155 922 840 2,544 North Jan 2002 Aug 2002 1000 Units Sold 2271139 1415949 977665 1443156 Revenue (000s US$) 477,784 283549 185625 280901 Margin (000s US$) 58,859 46241 43678 29818 2003 2004 2005 25% 40% 40% 20% 40% 20% 2003 0.25% 0.25% 0.25% 0.13% 2004 0.25% 0.25% 0.25% 0.25% 2005 0.25% 0.25% 0.25% 0.25% 2003 442 324 230 1,024 1,280 3,300 2004 474 405 274 1,024 1,280 3,457 2005 506 405 288 1,024 1,280 3,503 2006 537 405 288 1,024 1,280 3,534 2007 569 405 288 1,024 1,280 3,566 Exhibit 2 Simplified Income Statement for the South Wave 2000 Panel A: Forecasts without the ERP system Units [1] = Exhibit 3 Revenue [2] = Exhibit 3 COGS [3] = [2] - [4] Margin [4] = Exhibit 3 2001 2002 2003 2004 1,416 283549 237,308 46241 1,416 283549 237,308 46241 1,416 283549 237,308 46241 1,416 283549 237,308 46241 1,416 283549 237,308 46241 200.25 167.60 32.66 200.25 167.60 32.66 Price per unit COGS per unit Margin per unit [5] = [2] / [1] [6] = [3] / [1] [7] = [4] / [1] 200.25 167.60 32.66 200.25 167.60 32.66 200.25 167.60 32.66 Percentage margin [8] = [7] / [5] 16.3% 16.3% 16.3% DSI Inventory [9] = Exhibit 3 [10] = [3]*[9]/365 51 33,158 51 33,158 51 33,158 0 13 0.10% 4 28 0.25% 9 38 0.25% 12 38 0.25% 12 1,416 283,549 237,308 46,241 1,429 286,549 239,532 47,017 1,444 290,110 242,073 48,036 1,454 292,013 243,662 48,351 1,454 292,013 243,662 48,351 Panel B: Impact of the ERP system Additional Units [11]=[31] Additional Margin [12] = Exhibit 5 DSI reduction [13]=[35] Panel C: Forecasts with the ERP system Units [14]=[1]+[11] Revenue [15]=[14]*[18] COGS [16]=[14]*[19] Margin [17]=[14]*[20] 0.0 16.3% 51 33,158 16.3% 51 33,158 Price per unit COGS per unit Margin per unit [18]=[6]/(1-[21]) [19]=[6] [20]=[18]-[19] 200.3 167.6 32.7 200.5 167.6 32.9 200.9 167.6 33.3 200.9 167.6 33.3 200.9 167.6 33.3 Percentage margin [21]=[8]+[12] 16.3% 16.4% 16.6% 16.6% 16.6% DSI Inventory [22]=[9]-[13] [23]=[22]*[16]/365 51 33,158 47 30,713 42 27,855 39 26,035 39 26,035 1,416 83.10% 0.92 11% 25% 0% 0.0 0.0 1,416 83.10% 0.92 11% 25% 35% 13.3 13 1,416 83.10% 0.92 11% 25% 40% 15.2 28 1,416 83.10% 0.92 11% 25% 25% 9.5 38 1,416 83.10% 0.92 11% 25% 0% 0.0 38 12 0% 0 0 12 35% 4.2 4.2 12 40% 4.8 9 12 25% 3 12 12 0% 0 12 Panel D: Calculation of additional units Pre-ERP units [24]=[1] Pre-ERP Availability [25] = Exhibit 3 Target Availability [26]=Case data Increase in Availability [27]=([26]-[25])/[25] Additional Sales [28]=Case data Percentage improvement [29] = Exhibit 4 Additional units in year [30]=[24]*[27]*[28]*[29] Additional units [31]=cumulative[30] Panel E: Calculation of inventory savings Target DSI Reduction [32]=Case data Percentage improvement [33] = Exhibit 4 DSI Reduction in year [34]=[32]*[33] DSI Reduction [35]=cumulative[34] 2005 1,416 283549 237,308 46241 Can someone check to make sure I inputted this correctly? If I did, we can just change the input for the Exhibit cells and everything else should convert for the other calculations (Waves 200.25 167.60 32.66 16.3% 51 33,158 38 0.25% 12 1,454 292,013 243,662 48,351 200.9 167.6 33.3 16.6% 39 26,035 1,416 83.10% 0.92 11% 25% 0% 0.0 38 12 0% 0 12 Targeted product availability Additional Sales Reduction by 12 days calculations (Waves). Exhibit 3 Simplified Income Statement for the Central Wave 2000 Panel A: Forecasts without the ERP system Units [1] = Exhibit 3 Revenue [2] = Exhibit 3 COGS [3] = [2] - [4] Margin [4] = Exhibit 3 Price per unit COGS per unit Margin per unit [5] = [2] / [1] [6] = [3] / [1] [7] = [4] / [1] Percentage margin [8] = [7] / [5] DSI Inventory [9] = Exhibit 3 [10] = [3]*[9]/365 Panel B: Impact of the ERP system Additional Units [11]=[31] Additional Margin [12] = Exhibit 5 DSI reduction [13]=[35] Panel C: Forecasts with the ERP system Units [14]=[1]+[11] Revenue [15]=[14]*[18] COGS [16]=[14]*[19] Margin [17]=[14]*[20] Price per unit COGS per unit Margin per unit [18]=[6]/(1-[21]) [19]=[6] [20]=[18]-[19] Percentage margin [21]=[8]+[12] DSI Inventory [22]=[9]-[13] [23]=[22]*[16]/365 Panel D: Calculation of additional units Pre-ERP units [24]=[1] Pre-ERP Availability [25] = Exhibit 3 Target Availability [26]=Case data Increase in Availability [27]=([26]-[25])/[25] Additional Sales [28]=Case data Percentage improvement [29] = Exhibit 4 Additional units in year [30]=[24]*[27]*[28]*[29] Additional units [31]=cumulative[30] Panel E: Calculation of inventory savings Target DSI Reduction [32]=Case data Percentage improvement [33] = Exhibit 4 DSI Reduction in year [34]=[32]*[33] DSI Reduction [35]=cumulative[34] 2001 2002 2003 2004 2005 I will get this one - Collin Exhibit 4 Simplified Income Statement for the North Wave 2000 Panel A: Forecasts without the ERP system Units [1] = Exhibit 3 Revenue [2] = Exhibit 3 COGS [3] = [2] - [4] Margin [4] = Exhibit 3 Price per unit COGS per unit Margin per unit [5] = [2] / [1] [6] = [3] / [1] [7] = [4] / [1] Percentage margin [8] = [7] / [5] DSI Inventory [9] = Exhibit 3 [10] = [3]*[9]/365 Panel B: Impact of the ERP system Additional Units [11]=[31] Additional Margin [12] = Exhibit 5 DSI reduction [13]=[35] Panel C: Forecasts with the ERP system Units [14]=[1]+[11] Revenue [15]=[14]*[18] COGS [16]=[14]*[19] Margin [17]=[14]*[20] Price per unit COGS per unit Margin per unit [18]=[6]/(1-[21]) [19]=[6] [20]=[18]-[19] Percentage margin [21]=[8]+[12] DSI Inventory [22]=[9]-[13] [23]=[22]*[16]/365 Panel D: Calculation of additional units Pre-ERP units [24]=[1] Pre-ERP Availability [25] = Exhibit 3 Target Availability [26]=Case data Increase in Availability [27]=([26]-[25])/[25] Additional Sales [28]=Case data Percentage improvement [29] = Exhibit 4 Additional units in year [30]=[24]*[27]*[28]*[29] Additional units [31]=cumulative[30] Panel E: Calculation of inventory savings Target DSI Reduction [32]=Case data Percentage improvement [33] = Exhibit 4 DSI Reduction in year [34]=[32]*[33] DSI Reduction [35]=cumulative[34] 2001 2002 2003 2004 2005 Exhibit 5 Simplified Income Statement for all Waves Combined 2000 Panel A: Forecasts without the ERP system Units Revenue COGS Margin Inventory Panel B: Forecasts with the ERP system Units Revenue COGS Margin Inventory Panel C: Incremental Pre-Tax Cash Flows from the ERP system Incremental Change in Units Incremental Revenue Incremental COGS Incremental Margin Cumulative Inventory reduction (show as negative if a reduction) Year-to-year change in inventory reduction (show as negative) 2001 2002 2003 2004 2005 Exhibit 6 Capital Expenditures and Depreciation 1999 2000 2001 Capital Equipment Software licenses Capital Expenditure Depreciation by asset 1999 Assets 2000 Assets 2001 Assets 2002 Assets Depreciation Assume software licenses are depreciated along with capital expenditures 2002 2003 2004 2005 2006 2007 Exhibit 7 Incremental Operating Expenses 1999 Whirpool Employees Consultants Ongoing Operational License maintenance Task force Implementation Costs Other Expense Savings Incremental Operating Expenses [1]=Case data [2]=Case data [3]=Case data [4]=Case data [5]=Case data [6]=[1]+[2]+[3]+[4]+[5] [7]=Exhibit 6 [8]=[6]-[7] 2000 2001 2002 2003 2004 2005 2006 2007 Exhibit 8 Incremental Cash Flows and Valuation Using NPV, IRR, MIRR and PI Assumptions Tax rate Cost of Capital Revenue COGS Operating Expenses Depreciation EBIT Taxes at 40% EBIAT Cash Flow Adjustments: Plus Depreciation Minus CAPX Working Capital (Inventory reduction) Cash Flow Discount Factor Discounted Cash Flow Net Present Value Internal Rate of Return MIRR Profitability Index 40.0% 9.0% Source Exhibit 5 Exhibit 5 Exhibit 7 Exhibit 6 1999 2000 2001 2002 1.000 0.9174 0.8417 0.7722 Exhibit 6 Exhibit 6 Exhibit 5 - 2003 2004 2005 2006 2007 0.7084 0.6499 0.5963 0.5470 0.5019

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Personal Finance

Authors: Jeff Madura, Hardeep Singh Gill

4th Canadian edition

134724712, 134724713, 9780134779782 , 978-0134724713

More Books

Students also viewed these Finance questions