How to change model scenarios or inputs from any worksheet
  • How to change model scenarios or inputs from any worksheet
  • How to change model scenarios or inputs from any worksheet
  • How to change model scenarios or inputs from any worksheet
Originally published: 25/02/2019 08:06
Last version published: 25/02/2019 20:15
Publication number: ELQ-51886-2
View all versions & Certificate
certified

How to change model scenarios or inputs from any worksheet

The attached techniques show how to change inputs and scenarios from any sheet of the financial model without macros.

Description
A typical model consists of several worksheets - Inputs, Revenues, Opex, Capex, Summary financial statements etc. Imagine as well your model has three scenarios - Base, Upside and Downside which you can switch from and to on the Inputs sheet. You set your model to Base scenario and start walking through it. At Capex sheet, you decide you want to look at it under the Upside scenario. So you go back to Inputs, change scenario to Upside, then go back to Capex and wish there was a way to switch between scenarios on every worksheet.

Here is a solution based on Form and ActiveX controls without macros.

Form control objects - list boxes, combo boxes, option buttons - are linked to a chosen cell and change the value in this cell as you change selection. In my example, the drop down box in the left section is linked to cell D9. What's important, if you change the linked cell value manually, the selection in a control form changes as you type (try typing "1", "2" or "3" into cell D9 and see what happens). A connection between a form control and its cell works in both ways!

If you have several controls linked to the same cell, as you change one control selection it causes cell value to change. Then the cell causes other linked controls to change their selection. If you place a control form on every worksheet and link all controls to the same cell on the Inputs worksheet, it will allow you to change model scenarios from any sheet.

Even more importantly, different but close types of controls can be synched with each other, and they work together quite well. In my example I have used a list box, a combo box and a group of option buttons (choose the type you like more). Then I have connected a spinner with two scroll bars (horizontal and vertical). I have also created three inter-linked text boxes to work as input cells.

This Best Practice includes
1 Excel File

Andrei Okhlopkov offers you this Best Practice for free!

download for free

Add to bookmarks

Discuss

Further information

Quickly change inputs, assumptions and scenarios in financial models without having to select a specific dedicated sheet

A multi-sheet (i.e. almost any) financial model

The tool has ActiveX controls which do not function on Mac computers. But other controls do.

Reviews


keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from Andrei Okhlopkov

See all
keyboard_arrow_leftkeyboard_arrow_right

Discussion feed for How To Change Model Scenarios Or Inputs From Any Worksheet

The user community and author are here to help. Go ahead!

  • Ekaterina Belova
    Do I understand it correctly that this technique does not require macros?
    arrow_drop_uparrow_drop_downReply reply


4.4 / 5 (5 votes)

please wait...