Profilers • Excel Profiler • Example of an Excel Model
Previous
 • 
Next
Example of an Excel Model
An Excel model is one or more Excel formulas. Each formula must be a function of one or more other cells. This example uses the Demand.xls file, located within C:\Program Files\SAS\JMP\<version number>\Samples\Import Data).
Demand Model in Excel
The formula is in cell B8, and is a calculation of the Overall Cost associated with having different amounts of product in stock. The formula can be seen in the Formula Bar, and 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.
The calculations of the formula are as follows:
•
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.
•
If Amount Stocked is equal to Demand, then there is no shipping cost or disposal cost.
•
There is never both a shipping cost and a disposal cost at the same time.
Using the model in Excel, you can get the cost for only a given set of inputs at once. It is difficult to visualize how changing the value of one input affects the output. You can choose a different combination of the inputs to see how the cost is affected, but doing so for many combinations can take a long time.
Use the JMP Profiler to simultaneously see the effect of all inputs on the output. Also, you can quickly simulate a range of input combinations to see the resulting range of output values.
Example of the Profiler Using Excel Models