Table of Contents Chapter 1 Introduction Special Products

Table of Contents Chapter 1 (Introduction) Special Products Break-Even Analysis (Section 1. 2) Power Notebooks Make or Buy Example 1. 2 – 1. 7 1. 8 This slide provides an example of a make or buy decision, where modeling can be illustrated. This would lead into a discussion about developing a model, culminating in either a spreadsheet model, an algebraic model, and/or a graph of the costs of the two alternatives. Advertising Problem (UW Lecture) 1. 9 – 1. 22 An illustration of the management science approach to a problem. At the University of Washington, this is the very first lecture in the core MBA class on management science. While it includes some advanced topics (Solver, nonlinear objectives, etc. ) it can be taught entirely on the spreadsheet in a very intuitive way, and has proven to be a good introduction to the power of Solver. The next several lectures then would need to “back up” and cover more of the fundamentals of linear programming, modeling, the Solver, etc. Mc. Graw-Hill/Irwin 1 © The Mc. Graw-Hill Companies, Inc. , 2003

Special Products Break-Even Analysis • The Special Products Company produces expensive and unusual gifts. • The latest new-product proposal is a limited edition grandfather clock. • Data: – – If they go ahead with this product, a fixed cost of $50, 000 is incurred. The variable cost is $400 per clock produced. Each clock sold would generate $900 in revenue. A sales forecast will be obtained. Question: Should they produce the clocks, and if so, how many? Mc. Graw-Hill/Irwin 2 © The Mc. Graw-Hill Companies, Inc. , 2003

Expressing the Problem Mathematically • Decision variable: – Q = Number of grandfather clocks to produce • Costs: – Fixed Cost = $50, 000 (if Q > 0) – Variable Cost = $400 Q – Total Cost = • 0, if Q = 0 • $50, 000 + $400 Q, if Q > 0 • Profit: – Profit = Total revenue – Total cost • Profit = 0, if Q = 0 • Profit = $900 Q – ($50, 000 + $400 Q) = –$50, 000 + $500 Q, if Q > 0 Mc. Graw-Hill/Irwin 3 © The Mc. Graw-Hill Companies, Inc. , 2003

Analysis of the Problem Mc. Graw-Hill/Irwin 4 © The Mc. Graw-Hill Companies, Inc. , 2003

Management Science Interactive Modules • Sensitivity analysis can be performed using the Break-Even module in the Interactive Management Science Modules (available on your MS Courseware CD packaged with the text). – Here we see the impact of changing the fixed cost to $75, 000. Mc. Graw-Hill/Irwin 5 © The Mc. Graw-Hill Companies, Inc. , 2003

Special Products Co. Spreadsheet Mc. Graw-Hill/Irwin 6 © The Mc. Graw-Hill Companies, Inc. , 2003

Special Products Co. Spreadsheet Mc. Graw-Hill/Irwin 7 © The Mc. Graw-Hill Companies, Inc. , 2003

Make or Buy? • Power Notebooks, Inc. is a manufacturer of notebook computers. They are trying to decide whether to purchase the LCD screens for their computers from an outside supplier or to manufacture the screens in-house. • The screens cost $100 each from the outside supplier. • To set up the assembly process required to produce the screens in-house would cost $10, 000. They could then produce each screen for $75. Question: For what range of production volume is each alternative best? Mc. Graw-Hill/Irwin 8 © The Mc. Graw-Hill Companies, Inc. , 2003

An Advertising Problem • Parker Mothers is a manufacturer of children’s toys and games. One of their hottest selling toys is an interactive electronic Harry Potter doll. • Some data: – – • Unit Variable Cost: Unit Selling Price: Sales Force Salary: Fixed Overhead: $48 $65 $9, 000 $23, 000 Parker Mothers has analyzed past data for the Harry Potter doll (and other similar toys), and determined that sales are affected by a number of factors: – – – the season (e. g. , more at Christmas, more when a new Harry Potter book or movie is released, etc. ), the size of the sales force devoted to the product, the level of advertising. Question: What should the advertising budget for the Harry Potter doll be? (Proposal: $10, 000) Mc. Graw-Hill/Irwin 9 © The Mc. Graw-Hill Companies, Inc. , 2003

Predicting the Sales Level • After performing a statistical regression analysis, they estimate that sales for the quarter will be approximately related to the season, sales force salary, and advertising budget, as follows: • Seasonality Factors: – – • Q 1: Q 2: Q 3: Q 4: 1. 2 (publication of new Harry Potter book) 0. 7 0. 8 1. 3 (Christmas and expected release of new Harry Potter movie) Effect of Advertising / Sales Force: Mc. Graw-Hill/Irwin 10 © The Mc. Graw-Hill Companies, Inc. , 2003

Spreadsheet for Quarter 1 Mc. Graw-Hill/Irwin 11 © The Mc. Graw-Hill Companies, Inc. , 2003

Trial Solutions Mc. Graw-Hill/Irwin 12 © The Mc. Graw-Hill Companies, Inc. , 2003

The Excel Solver Mc. Graw-Hill/Irwin 13 © The Mc. Graw-Hill Companies, Inc. , 2003

The Optimized Solution Mc. Graw-Hill/Irwin 14 © The Mc. Graw-Hill Companies, Inc. , 2003

Four Quarters Spreadsheet Mc. Graw-Hill/Irwin 15 © The Mc. Graw-Hill Companies, Inc. , 2003