2001SvelteStepByStep

2001SvelteStepByStep - Excel Modeling Practice The Svelte...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
Excel Modeling Practice The Svelte Glove Problem Step-by-Step With Instructions E XCEL R EVIEW 2001-2002
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Contents Page Number Overview. .............................................................................................................................. 1 Features . ................................................................................................................................ 1 The Svelte Glove Problem . ................................................................................................. 1 Outputs. ................................................................................................................................. 2 Approaching the Modeling Task. ...................................................................................... 2 Identify the Purpose of the Model Identify Given Values (Constants) or Relationships (Formulas) Identify Any Model Constraints Identify Decision Variables to Add to the Model Consider the Formulas That Will Be Required Determining a Worksheet Layout . .................................................................................... 3 Add Documentation Position the Key Model Value Establish Initial Labels and Data Layout Complete the Formulas and Add Any Other Needed Information. ............................ 4 Enter the Initial Formulas Account for the Labor Hour Requirement Write Formuls to Determine Hours Needed Write Formulas to Account for the Stated Constraints Use the Model to Determine the Best Product Mix (By Trial and Error). .................... 5 Use the Model to Determine the Best Product Mix (With Solver). ............................... 6 Solver Notes. ......................................................................................................................... 7 Worksheet Exampless . ........................................................................................................ 8 A Sample Svelte Worksheet Layout and (suboptimal) Solution The Worksheet with Formulas Displayed A Graphical View of a Proposed Product Mix An Example of Excel’s Scenario Manager Report Solver’s Solution to the Problem Download a workbook version from this URL: http://faculty.fuqua.duke.edu/~pecklund/excelreview/ExcelReview.htm
Background image of page 2
1 Overview Svelte Glove Problem is a product mix problem. In this problem, you build an Excel worksheet to determine what mix of products yields the highest profit. A set of production constraints complicates the model. Build the model so it allows a manual determination of the product mix manually. Then apply Excel's Solver to automate the process. Features By completing a Svelte Glove model you’ll get practice with: = building a product-mix model in Excel = creating formulas = copying formulas = using relative and absolute addressing = using the IF function = using Excel's Solver The Svelte Glove Problem The Svelte Glove Company manufactures and sells two products: work gloves and driving gloves. The company makes a profit of $12 for each pair of work gloves sold and a profit of $4 for each pair of driving gloves sold. The labor-hour requirements for the products in each of the three production departments is shown in the table below: Labor-Hour Requirements Cutting 1 2 Sewing 1 3 Finishing 2 3 Each product must be processed by each one of the three departments before it's complete and ready for sale. Reading the table above, for example, each pair of driving gloves must spend two hours in the Cutting Department, three hours in the Sewing Department, and three hours in the Finishing Department before they’re complete. The supervisors of the three departments have estimated that the following numbers of labor hours will be available during the next month: Cutting 800 hours Sewing 1,600 hours Finishing 2,000 hours
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 Outputs Construct a spreadsheet model that: 1) Specifies the number of work gloves and driving gloves to manufacture in the next month.
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 13

2001SvelteStepByStep - Excel Modeling Practice The Svelte...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online