Excel Profiler Overview
The JMP Add-In for Excel uses the JMP Profiler to visualize models (or formulas) stored in Excel worksheets. The Excel add-in is automatically installed when you install JMP. Profiling in the Excel Add-In is a two-step process:
1. Click the Create/Edit Model button (Excel 2007 through 2016) to enter information about the model that JMP needs. This needs to be done only once per model. For more information, click Help in the Create/Edit Model window.
2. Click the Run Model button (Excel 2007 through 2016) to launch the JMP Profiler and run the Excel model. For more information, see “Run the JMP Profiler”.
Notes:
• The Preferences, Data Table, Graph Builder, and Distribution buttons are not needed to profile an Excel model. For more information about these features, see the Import Your Data chapter in the Using JMP book.
• A JMP ribbon is added to Microsoft Excel when the add-in is installed. If there is no JMP ribbon, install the add-in by double-clicking the JMP setup.exe file, selecting Modify, selecting Excel Add-In, and clicking Next.
Example of an Excel Model
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.
Figure 9.2 Demand Model in Excel
About the Demand.xls Sample Import Data
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.
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.
Create the Model in Excel
1. Select Help > Sample Data Library and navigate up one level to the Sample Import Data folder.
2. Double-click Demand.xls to open the file in Microsoft Excel.
3. In Microsoft Excel, click the JMP ribbon.
4. Click the Create/Edit Model button 
.
.The name of the worksheet is displayed in the Model and Model Name fields.
The Inputs and Outputs fields are populated with data from the worksheet.
5. Enter Customer Demand in the Model Name field and click Apply.
The Model field is updated.
6. Select Air Freight in the Inputs box and then click the down arrow button 
.
.Air Freight is moved to the bottom of the list because you want it displayed last in the Profiler.
7. Click OK.
The Excel model is saved to the worksheet.
Notes:
• 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.
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.• You must define the entire model on one worksheet. A model cannot reference cells on another worksheet.
Run the JMP Profiler
Once you create the model using the Excel Add-In, you can run it in the JMP Profiler. From the Excel Add-In, perform the following actions:
1. In Microsoft Excel, click the JMP ribbon.
2. Click the Run Model button.
3. Select the model that you want to run.
4. Click Profile in JMP.
5. Use the JMP Profiler to simultaneously see the effect of all inputs on the output. You can also simulate a range of input combinations to see the resulting range of output values.
Figure 9.3 Example of the Profiler Using Excel Models
Note: To ensure that your original Excel worksheet is not altered, JMP runs a hidden copy of Excel in the background that controls all of the Profiler calculations.
Use Linear Constraints
Within the JMP Profiler, you can alter the linear constraints in order to restrict the model input values. You are prompted to save the constraints to the Excel workbook. After constraints are saved to the Excel workbook, whenever the model is profiled from the Excel Add-In, the constraints are incorporated.
1. From the red triangle menu next to Prediction Profiler, select Alter Linear Constraints.
2. Click Add Constraint.
3. Type in the constraining values.
4. Click OK.
5. From the red triangle menu next to Prediction Profiler, select Save Linear Constraints.
You are prompted to save the constraints to the Excel workbook.
6. Click Yes.
Note: When you save the .xls file, you might see a compatibility error. If so, click Continue to save the file.
The workbook opens in Excel. When you run the model, the constraints are reflected in the JMP Profiler. For more information about linear constraints, see “Linear Constraints” in the “Introduction to Profilers” chapter.
Tip: To delete a linear constraint, set all constraint values to zero.
Resolution of Profile Lines
The Default N Levels option on the red triangle menu next to Prediction Profiler affects the resolution of the profile lines. Note the following information:
• This option defaults to 17 when the Profiler runs a model stored in Excel.
• This option defaults to 41 when the model is stored directly in JMP.
If the same model is stored in both Excel and JMP, then the profile lines can appear differently when the models are profiled. Increasing this value causes the Excel Profiler to run slower.
Use the Excel Profiler from JMP
After you have defined model input and outputs in an Excel file, you can profile the model from within JMP.
1. Select Graph > Excel Profiler.
2. Locate the Excel file containing the model and then click Open.
3. If the Excel file contains multiple models, you are prompted to select the model that you want to profile.
Note that the Excel Profiler is also scriptable, as follows:
Excel Profiler( "path to workbook", <"model name"> ) ;
If more than one model exists, and no model is specified, a window with the list of available models appears. For more information about scripting the Excel Profiler, see the Extending JMP chapter in the Scripting Guide.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.