Microsoft Office Tutorials and References
In Depth Information
USING SOLVER ON A NEW PROBLEM
Spreadsheet title and Constants section for Sensuous Scents Inc.
Sales Price per bottle
These values are the sales prices for each of the three products.
Conversion Cost per Unit
These values are the direct labor costs plus the manufacturing
overhead costs budgeted per unit manufactured. A conversion cost is often used in industries that
manufacture liquid products.
Minimum Sales Demand
These values reflect the forecast minimum sales demand that you
must supply to your customers. These values will be used later as constraints.
Income Tax Rate
The rate is 32% of your pretax income. No taxes are paid on losses.
Sales, General and Administrative Expenses per Dollar Revenue
This value is an estimate of
the non-manufacturing costs that Sensuous Scents will incur per dollar of sales revenue. These
expenses are subtracted from the Gross Profit value in the Income Statement section to obtain
Net Income before taxes.
Available Ambergris (lbs.)
This value is the amount of ambergris that Sensuous Scents obtained
this year for production.
Cost per lb., Deionized Water
This value is the current cost per pound of deionized water.
Cost per lb., Ethanol
This value is the current cost per pound of ethanol.
Cost per lb., other Additives
Scent products contain other additives and fixatives to enhance or
preserve the fragrance. This value is the cost per pound of the other additives.
Cost per lb., Ambergris
This value is the current market price per pound of naturally harvested
ambergris. Again, no whales were harmed to obtain the ambergris.
The rest of the cells are filled with a gray background to indicate that you will not use their values or
formulas. The section is arranged this way to maintain one column per product all the way down the
spreadsheet, which will simplify writing the formulas later.
Bill of Materials Section
Your spreadsheet should contain a Bill of Materials section, as shown in Figure D-45. The section entries are
explained after the figure. A bill of materials is a list of raw materials and ingredients required to make one
unit of a product.
Bill of Materials section
Deionized Water (lb.)
The amount of deionized water required to make one unit of each
The amount of ethanol required to make one unit of each product
Other Additives (lb.)
The amount of other additives required to make one unit of each product
The amount of ambergris required to make one unit of each product