Welcome, this video will outline one approach to generate a Goal Seek across multiple columns in Excel.
Here we have a standard financial model calculating down to EBITDA,
which is referencing from our input sheet.
We want to Goal Seek our EBITDA in row 14,
based on a specified EBITDA goal in row 18 – by achieving a uniform 30% margin.
Our changing precedent variable will be Cost of Goods Sold in row 9.
0:00:54.066,0:00:54.033 Our changing precedent variable will be Cost of Goods Sold in row 9.
Go to Visual Basic editor and insert a Module.
0:01:19.066,0:01:19.000 Go to Visual Basic editor and insert a Module.
We want to insert an input box prompt, whenever someone runs the macro,
as it will permit users to exit the macro where necessary.
Next we need to declare the macro’s variables to prevent run-time errors.
The narratives in green are optional and won’t impact the macro –
they merely serve as a guide for the macro user.
Then we want to disable certain key Excel properties, which would otherwise hinder the macro’s performance.
0:04:19.066,0:04:19.000 Then we want to disable certain key Excel properties, which would otherwise hinder the macro’s performance.
This is a key part of the macro; specifying the range of cells to change
for our column Goal Seek –
in this case is row 9.
0:04:48.066,0:04:48.000 in this case is row 9.
Given the cells in row 9 contained formulas –
referencing to our input sheet, it is paramount to change and convert these formula cells to their values;
just like copying and pasting values.
Otherwise the macro won't work.
The i integer will enable the macro to move across the columns of data –
as per the referenced range J9:S9.
As described, cCell references the changing cells in row 9.
0:06:10.066,0:06:10.000 As described, cCell references the changing cells in row 9.
sCell denotes the set cells in row 14 of the Goal Seek macro.
Whilst, gCell are the cells in row 18 dictating the goal values.
0:07:08.066,0:07:08.000 Whilst, gCell are the cells in row 18 dictating the goal values.
We are now ready to insert the code, which will enable the EBITDA goal seek values in row 18 to be reflected in row 14.
This is the code that will trigger the Goal Seek.
0:07:58.066,0:07:58.000 This is the code that will trigger the Goal Seek.
Remember to re-enable those Excel properties, which was initially
disabled
at the top of the macro.
We are now ready to insert a form control button, and insert the Goal Seek macro.
As we see, our multi column Goal seek macro works.