XLSolverPractice2001

# XLSolverPractice2001 - Excel & Solver: Hands-On Modeling...

This preview shows pages 1–8. Sign up to view the full content.

Excel & Solver: Hands-On Modeling Practice Exercises E XCEL R EVIEW 2001-2002

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

View Full Document
The Excel modeling problems here are similar to problems you’re likely to encounter in Fuqua’s Decision Models course. You can create Excel models on your own to solve these problems, or use the notes and tips included here as a guide. Also see the accompanying Excel workbook named MorePractice.xls available online at this URL: http://faculty.fuqua.duke.edu/~pecklund/excelreview/ExcelReview.htm
Contents P a g e Blue Ridge Hot Tubs. ..................................................................... 1 Wood Walker. ................................................................................. 5 Electro-Poly Corporation . ............................................................. 8

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

View Full Document
1 Blue Ridge Hot Tubs The Problem Blue Ridge Hot Tubs, Inc. sells two models of hot tubs: The Aqua-Spa and the Hydro-Lux. The company purchases prefabricated fiberglass hot tub shells and installs a common water pump and the appropriate amount of tubing into each hot tub. The number of hours it takes to install each model, the tubing required, and the profit for each of the two models is described in the table below. Profit Aqua Spa 9 12 \$350 Hydro-Lux 6 16 \$300 The company expects to have 200 pumps, 1,566 hours of labor, and 2,880 feet of tubing available during the next production cycle. The company can sell all the hot tubs it makes. Create a spreadsheet model to determine the optimal number of Aqua-Spa and Hydro-Lux hot tubs to produce in order to maximize profits. Modeling notes and tips follow.

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

View Full Document
2 Blue Ridge Hot Tub Modeling Notes & Tips I. Arrange the Data in the Spreadsheet (See the “Blue Ridge Basic” Worksheet) Below is an illustration of the data we know from the problem arranged in a logical way, with: = space for the decision variables (number to make; here zeros), and = a label for information we want to know (e.g.- maximum total profit). Note that the constraint information (which is very important) is part of this worksheet. II. Add Formulas to the Model (See the “Blue Ridge Formulas” Worksheet.) Remember to use cell references (not typed-in values) when creating formulas in order to keep the numbers in the model easy-to-change. Total Profit Blue Ridge Hot Tubs Total Profit: Aqua-Spa Hydro-Lux Number to make: 00 Unit profit: \$350 \$300 Constraints: Used: Available: Pumps required: 11 2 0 0 Labor required: 9 6 1,566 Tubing required: 12 16 2,880 Total Profit: =B7*B8+C7*C8 Aqua-Spa Hydro-Lux Number to make: Unit profit: 350 300 Constraints: Used: Available: Pumps required: 1 1 =\$B\$7*B11+\$C\$7*C11 200 Labor required: 9 6 =\$B\$7*B12+\$C\$7*C12 1566 Tubing required: 12 16 =\$B\$7*B13+\$C\$7*C13 2880
3 ±² Figure out the total profit formula and put it in place. ±² Number to make X unit profit for each model, with the results added together. Constraints ±² Figure out formulas to represent the constraints. ±² These formulas go in the “Used” column, for pumps, labor, & tubing. ±² Note: Create the first formula (for pumps) and copy it down the column (but be sure to use absolute addressing to refer to number-to- make).

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

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 12/20/2011 for the course BUS 101 taught by Professor Ecklund during the Spring '01 term at Duke.

### Page1 / 15

XLSolverPractice2001 - Excel & Solver: Hands-On Modeling...

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

View Full Document
Ask a homework question - tutors are online