An Excel model consists of one or more Excel formulas. Each formula must be a function of one or more other cells. This example uses the Demand.xls sample import data located in the Samples/Import Data folder.
The formula in cell B8 is a calculation of the Overall Cost associated with having different amounts of product in stock. The formula, which is shown in the Formula Bar, is a function of four cells:
|
•
|
Amount Stocked is the amount of product in stock.
|
|
•
|
Demand is the customer demand for the product.
|
|
•
|
Air Freight is the cost per unit to ship additional product by air when the demand exceeds the amount in stock.
|
|
•
|
Expiration Cost is the cost per unit of disposing of unused product when the demand is less than the amount in stock.
|
|
•
|
If Amount Stocked is less than Demand, then the company has to ship additional units, at a cost of (Demand-Amount Stocked) × Air Freight. For example, if the demand is 8, but the company has only 6 in stock, then it has to ship 8 - 6 = 2 units at a cost of 2 × 150 = 300.
|
|
•
|
If Amount Stocked is greater than Demand, then the company has to dispose of unused product, at a cost of (Amount Stocked - Demand) × Expiration Cost. For example, if the demand is 5, but the company has 8 in stock, then it has to dispose of 8 - 5 = 3 units at a cost of 3 × 50 = 150.
|
|
•
|
|
1.
|
|
2.
|
Double-click Demand.xls to open the file in Microsoft Excel.
|
|
4.
|
.|
6.
|
.|
7.
|
Click OK.
|
|
•
|
If the fields in the Create/Edit Model window are not populated when you set up your model, click Choose and select the cell that contains the input name. Specify the values and click Apply.
|
|
•
|
You can create more than one model for a worksheet. In the Create/Edit Model window, click the plus button
 next to the Model name. In the Model Name field, change the name if necessary and click the Apply button. You can then change the inputs and outputs as necessary and click OK. |