Excel 2000 - Solver Parameters

C
EX $32 TUTORIAL 10 USING SOLVER FOR COMPLEX PROBLEMS




3. Use Solver to produce a solution, and then create an answer report.
4. Format the Orders worksheet to give it a professional appearance.
5. Preview the worksheet and make any necessary formatting changes.
6. Save the workbook, and then preview and print its contents.
Case 2. Manufacturing Pontoon Boats at Robbins Pontoon Incorporated Mike Chignell is
the assistant to the director of manufacturing at Robbins Pontoon Incorporated. Robbins
manufactures four different models of pontoon boats: All Purpose, Camping, Utility, and
Fishing. Each of the four models is built on the same boat frame. A topside assembly is
attached to the frame to create each model.
Robbins currently has 135 boat frames in stock and a limited number of the four different
topside assemblies. Mike wants you to determine the mix of models that will generate the
greatest profit, given the available frames and topside assemblies. You'll have to make sure
he manufactures enough of each model to fill the customer orders. Do the following:
I. If necessary, start Excel, open the Pontoon workbook in the Cases folder for
Tutorial. 10 on your Data Disk, and save it in the same folder as Pontoon Boat Order.
~ n t e the new worksheet name, your name, and the date on the Documentation sheet.
r
2. Go to the Orders worksheet and then set up the Solver parameters for this problem
using the following guidelines:
a. You want to maximize the total profit from all models of pontoon boats by changing the quan-
tity to make of each boat model.
b. The optimal solution should include the following limits:

H You cannot make more boats than you have available assemblies for each type.
H You have to sari+ the customer orders for each boat type.
The total number of boats you make cannot exceed the total number of available
frames.
H Make only complete boats.
3. Use Solver to produce a solution and generate an answer report.
4. Modify the answer report worksheet so it contains your name, the date, and the filename.
5. Format the worksheet to give it a professional appearance.
6. Preview the printout and make any formatting changes necessary for a professional
appearance.
7. Save the completed workbook, and then preview and print the completed worksheet.
Case 3. Scheduling Employees at Chipster's Pizza Lisa Avner is the assistant manager at
Chipster's Pizza, a popular pizza place located in Cedar Falls, Iowa. Chipster's is open
every day from 5:00 p.m. to 1:00 a.m. Friday and Saturday are the busiest nights; Sunday
and Wednesday nights are moderately busy; Monday, Tuesday, and Thursday are the slow-
est nights.
Lisa is responsible for devising a schedule that provides enough employees to meet the
usual demand, without scheduling more employees than are needed for each shift. All of
Chipster's employees work five consecutive days and then have two days off. This means
Lisa can schedule employees for seven different shifts-the Sunday through Thursday
shift, the Monday through Friday shift, the Tuesday through Saturday shift, and so forth.