So I'm new to VBA and trying to automate my financial models. I've got this large complex stock valuation model on excel that reads either string bear, bull, or base from a single cell that has a dropdown that has the options of bear, bull, or base. The model will use different inputs depending on if the user chooses the strings bear, bull, or base, and the model will output different values of the stock respectively. In a separate sheet, I'm trying to display, in 3 separate cells, the output of the model (the value of the stock) in the bear, bull, and base case respectively. I could just make 3 models; a bear, bull, and base case, but I thought that might be too bulky, and I just want 1 dynamic model, instead of 3 models (one for each case). I'm wondering if VBA allows for a "scenario" type of ability in which it sets a specific cell to a hypothetical value and then takes the value of another cell. Basically, I wish to take the hypothetical outputs without actually having to change the inputs of my model.
If that was confusing, let's say I have sheets 1 and 2. On sheet 1 I have a financial model (which we can use x+10 = y as an analogy, where x is the input and y is the output). x can be a bull, bear, or a base, which let's say are 3,1, and 2 respectively. therefore the outputs y will be 13, 11, and 12 based on which of the 3 cases I choose. on sheet 2, im trying to display the outputs of the bear, bull, and base case (11, 13, and 12), without actually changing the model. Of course, a solution is just to make a bear, bull, and base case model, so that each has its own input and output, and I can just display the output from each of those models. But right now I want to do it with just 1 dynamic model, where I can make changes that will automatically translate into sheet 2. I tried scenario analysis, but the pivot chart/summary output is static, like it doesn't automatically update when I change the input values. note that the actual value for bear, bull, and base case can be changed to any number the user wants, so I couldn't just go bull=base+1.
In the photos I attached, in the first one, the highlighted cell is where I can change the string to be bear, bull, or base. In the 2nd photo, it would change the model and the output I'm looking for is the intrinsic value, found on the top right. The third photo shows that on another sheet, I wish to display the bull, bear, and base case intrinsic value results.
If that was confusing, let's say I have sheets 1 and 2. On sheet 1 I have a financial model (which we can use x+10 = y as an analogy, where x is the input and y is the output). x can be a bull, bear, or a base, which let's say are 3,1, and 2 respectively. therefore the outputs y will be 13, 11, and 12 based on which of the 3 cases I choose. on sheet 2, im trying to display the outputs of the bear, bull, and base case (11, 13, and 12), without actually changing the model. Of course, a solution is just to make a bear, bull, and base case model, so that each has its own input and output, and I can just display the output from each of those models. But right now I want to do it with just 1 dynamic model, where I can make changes that will automatically translate into sheet 2. I tried scenario analysis, but the pivot chart/summary output is static, like it doesn't automatically update when I change the input values. note that the actual value for bear, bull, and base case can be changed to any number the user wants, so I couldn't just go bull=base+1.
In the photos I attached, in the first one, the highlighted cell is where I can change the string to be bear, bull, or base. In the 2nd photo, it would change the model and the output I'm looking for is the intrinsic value, found on the top right. The third photo shows that on another sheet, I wish to display the bull, bear, and base case intrinsic value results.
