I have a large, complicated Excel spreadsheet. It’s an economic model of sorts that is designed for people to easily review, so it contains about twenty different sheets with a wide variety of parameters, expansions, and tables, all calculated automatically from a sheet where you enter about 40 parameters to configure the model.
The output of the model can be summarized as just one number, we’ll call it “Price”.
I would like to be able to plot this model output (Price) as a function of a single input parameter (call it “Demand”) automatically. Currently I have to create a separate spreadsheet (Demand vs. Price), manually enter each input Demand value into the parameters sheet, and manually record the corresponding Price output into the separate spreadsheet, then plot it.
Is there any way to do this automatically in Excel?
One thing I think might help - Excel VBA could definitely automate the population of your demand versus price spreadsheet. You can set up a loop, tell it at what demand intervals you want to test, and have it record all the values for you. This is a sample of what it might look like, as nearly as I can figure out at this point in the evening:
dim row as integer, price as double, demand as double
for row = 1 to 20
price = row * 5
ParametersSheet.Cells(2, 4).value = price
Application.Calculate
demand = outputsheet.cells(5, 6).value
PlotterSheet.cells(row + 1, 2) = price
PlotterSheet.cells(row + 1, 3) = demand
next
That would test price values from 5 to 100, at intervals of five. It assumes that price has to be written into row 2, column 4 of ParametersSheet, and the demand read out of row 5, column 6 of outputsheet. Then the values are written into columns 2 and 3 of PlotterSheet
The Application.Calculate statement makes sure that all of the calculations in your model are updated for the new price value.