Linking to Excel Inserts

How to link Assumptions to Excel Inserts

Overview

As described in the Excel Inserts help sections, you can connect custom Excel sheets to your generated Model, either by creating Outputs from your Model, or by linking Assumptions to your Inserts - this page deals with the second of these.

Generally, we advise against linking Assumptions to Excel Inserts if it can be avoided. If your inputs are produced by a dynamic Excel model, you will not be able to control this model from within the Model Editor, and there's usually a way to produce whatever inputs you need in Models!

Linking to Insert inputs

Assumptions are linked to Inserts via Named Ranges. You will need to create a Named Range in your Insert that represents your input data, and then tell Models how to link to that Named Range.

The following example illustrates this process.

In this example, we have some input data for the New members per month Variable that we want to take from a custom Excel sheet, as shown below. The input data is in a 3x2 table, with one Membership type per row, and one Region per column. This input sheet is contained in an Excel Insert that we have already created, called Volume Input. To make the input data accessible from Models, we must assign a Named Range to it: in this case, we have given it the name Input_NewMembersPerMonth.

Section of an Excel spreadsheet, used as an Excel Insert in Models. A range of cells to be used as an input has been assigned as an Excel Named Range
Excel Insert with input data

To link our data to this input, we select and Edit the relevant Assumptions, as shown below.

Screenshot of the Assumptions list, with several assumptions selected, highlighting the Edit button in the preview panel
Editing Assumptions

This opens an Assumptions dialog. First, we change the source type to Link to Excel Insert (1), which results in several additional steps being shown. In the Link to Insert step, we choose the Volume Input Insert (2), and the Input_NewMembersPerMonth Named Range (3).

Assumptions dialog: Linking to Excel Insert

Once you have selected a Named Range, you then need to give Models information about the shape of the data in that Named Range. You do this by matching the layout of data in the input workbook to the displayed table in Models. In the example below, we drag the Region Dimension to the Horizontal section (4), which results in the correct table configuration (5).

Screenshots showing how to manipulate Dimensions to correctly link to Excel Insert data inputs
Assumptions dialog: Matching shape of input data

Finally, we need to enter some default values for the data (6), so that Models can calculate its values when it does not have access to the Excel Insert (Models will not extract data from the input workbook when re-calculating the Model). Note that in the example below, we have arranged the table with Region on the horizontal axis so that we can copy and paste data directly from the input workbook.

Screenshot showing Default Values page of Assumptions dialog. The user has entered values to use when the Excel Insert input is not available.
Assumptions dialog: Entering default values

Last updated