Microsoft Office Tutorials and References
In Depth Information
USING SOLVER ON A NEW PROBLEM
When you finish examining the Answer Report, save your file and then close it. To close the workbook,
click the File tab and then click Close (see Figure D-43).
FIGURE D-43
Closing the Excel workbook
USING SOLVER ON A NEW PROBLEM
A common problem in manufacturing businesses is deciding on a product mix for different items in the same
product family. Sensuous Scents Inc. makes a premium collection of perfume, cologne, and body spray for
sale in large department stores and boutiques. The primary ingredient is ambergris, a valuable digestive
excretion from whales that is harvested without harming the animals. Ambergris costs more than $9,000 per
pound and is very difficult to obtain in large quantities; Sensuous Scents can only obtain about 20 pounds of
ambergris each year. The other ingredients
deionized water, ethanol, and various additives
are available in
unlimited quantities for a reasonable cost.
You have been asked to create a spreadsheet model for Solver to determine the optimal product mix that
maximizes Sensuous Scents
net income after taxes.
Setting up the Spreadsheet
The sections in this spreadsheet are different from those in the preceding trucking problem. You will create a
Constants section, a Bill of Materials section for the three products, a Quantity Manufactured section that
contains the Changing Cells, a Calculations section to calculate ambergris usage, manufacturing costs, and
sales revenues per product line, and an Income Statement section to determine the net income after taxes,
which will be the Optimization Cell.
AT THE KEYBOARD
Start a new file called Sensuous Scents Inc.xlsx and set up the spreadsheet.
Spreadsheet Title and Constants Section
Your spreadsheet title and Constants section should look like Figure D-44. A discussion of the section entries
follows the figure.
 
Search JabSto ::




Custom Search