Session 1b

1. Session 1b

2. Overview • Spreadsheet Conventions • Copying, Pasting, Reporting • Introduction to Solver Decision Models -- Prof. Juran

3. Spreadsheet Conventions • Basic Idea: Customer-friendly • Easy to Read • Easy to Audit • Easy to Adapt • “Parameterization” • “Dragability” Decision Models -- Prof. Juran

4. Spreadsheet Conventions • Clear, logical layout • Separation across multiple sections and/or worksheets • Clear headings for inputs, decision variables, and outputs • Formatting for user clarity • Text boxes and cell comments Decision Models -- Prof. Juran

5. Written Reports 0. Conclusions and Recommendations • Done last, appears first 1. Managerial Problem Definition 2. Formulation 3. Solution Methodology 4. Discussion? Appendices? Decision Models -- Prof. Juran

6. Written Reports • Minimal Raw Spreadsheet Elements • i. e. none • Graphical Communication • Equation Editor • Charts, Graphs • Spreadsheet Captures Decision Models -- Prof. Juran

7. Optimization Example: Malcolm’s Glass Shop Decision Models -- Prof. Juran

8. Managerial Problem Definition Malcolm owns a glass-molding machine capable of producing two products: six-ounce juice glasses and ten-ounce cocktail glasses. He needs to decide how many of each product he ought to make each week in order to make the greatest profit. He is limited by the production rate of the machine, demand for one of the products, and storage space. Decision Models -- Prof. Juran

9. Formulation • Decision variables: How many to produce of two products. • Objective: Maximize Profit. • Constraints: • The molding machine can only produce so many glasses in a week. • There is a market limit for 6-oz glasses. • There is a limit on storage space. • Malcolm can’t make negative amounts of either product. Decision Models -- Prof. Juran

10. Formulation Maximize Profit from 6 - oz glasses + Profit from 10 - oz glasses Subject to: <= Total Molding capacity Molding Machine capacity used for 6 - oz + Molding Machine capacity used for 10 - oz 6 - oz glasses produced <= Total Demand for 6 - oz glasses <= Total Storage Space Stora ge Space used for 6 - oz + Storage Space used for 10 - oz 6 - oz glasses produced >= 0 10 - oz glasses produced >= 0 Decision Models -- Prof. Juran

11. Formulation Decision Models -- Prof. Juran

12. Formulation Decision Models -- Prof. Juran

13. A B C D E F G Decision Variables 6-oz 10-oz 1 1 1 2 =SUMPRODUCT(B2:C2,B4:C4) 3 Objective Function 500 450 950 4 = profit 5 Constraints 6 Molding Capacity 6 5 11 60 7 <= Demand for 6-oz 1 0 1 8 8 <= Storage Space 10 20 30 150 9 <= Nonnegativity (6-oz) 1 0 1 0 10 >= Nonnegativity (10-oz) 0 1 1 0 11 >= 12 =SUMPRODUCT($B$2:$C$2,B11:C11) 13 Solution Methodology Decision Models -- Prof. Juran

14. Solver Dialog Box Decision Models -- Prof. Juran

15. Solver Options Decision Models -- Prof. Juran

16. Solver Answer Report Decision Models -- Prof. Juran

17. Communicating Graphically Decision Models -- Prof. Juran

18. Enhancing Charts Decision Models -- Prof. Juran

19. Enhancing Charts Decision Models -- Prof. Juran

20. Conclusions and Recommendations • Make 642 cases of 6-oz glasses and 428 cases of 10-oz glasses. • Earn $5,143 profit. Decision Models -- Prof. Juran

21. Summary • Spreadsheet Conventions • Copying, Pasting, Reporting • Introduction to Solver Decision Models -- Prof. Juran