In what could be considered Part 1 of this article, we set up a VBA environment that allows us to run Monte Carlo simulations in Excel for Mac. Here, we’ll take the analysis one step further and use the Solver addin to find specific target values.
If you’ve purchased MS Office for Mac 2011 recently, then the Solver addin was installed automatically along with Excel. To activate it, go into Excel and click on Tools > Addins. Check the box next to Solver, and you’re good to go. If you purchased an early version of Office for Mac 2011, you may need to download and install Solver.
As with Excel for Windows, Solver also offers a premium version of its addin for Mac. I have no experience with the premium version, and I have no basis by which to compare it to, e.g., Matlab, for complex, non-linear problems. As you can see from the webpage I’ve linked, it’s one of those deals where you have to email Solver just to find out how much the premium version costs.
Parenthetically, the fact that Solver makes an addin for the Mac version of Excel means that addins are technically doable on the Mac side. Indeed, there are a few other third-party addins for the Mac version of Office as described here. Nevertheless, it’s unclear whether or to what extent it’s feasible for independent developers and hobbyists to create Mac-version addins.
Fortunately, the basic version of Solver will accomplish everything we’ll want to do today (and considerably more).
The Solver Window
To use Solver, click on the Data tab and then on the Solver button. The Solver window looks like this:
In order for Solver to work, you have to specify one particular cell (the “Objective”) that contains a formula rather than a static value. After that, Solver will begin changing the value(s) of whatever variable(s) you specify to either maximize or minimize the value of the Objective cell, or to make the Objective cell equal a certain value.
You can also limit Solver’s calculation by imposing constraints on the value of one or more variables. We’ll discuss this in more detail below.
Some of the items in the Solver window are self-explanatory, and some items are anything but. To understand more fully requires us to know something about the difference between linear and non-linear functions.
Linear vs. Non-linear Functions
Of course, the quickest way to determine whether a function is linear or non-linear is to assign numbers to all of the variables and plot it. When you do, most non-linear functions will show up as a clearly discernible curve.
But not always. Recall from Part I of this article that the plot of our Monte Carlo simulation looked like this:
As you can see, the points all appear to lie on a straight line. Nevertheless, our Revenue, Income and ROI functions are all non-linear. Here’s how we know.
A function is linear when its rate of change is constant. To illustrate, let’s create a table. Using the same worksheet we created in Part 1, enter 3500 in cell J2, and then in J3 enter the following:
Now drag down to cell J12. You’ll see that we’ve created a list of billable hours that increases by a constant value, 100. In this problem we only simulated one variable. Consequently, if we change the billable hours variable by a constant amount, we can measure whether the change in value for the Revenue function is also constant.
Note that had we simulated more than one variable, to test for non-linearity we’d have to make the rate of change constant for those other variables as well.
Next, Copy cell F2 and Paste Special > Formulas into cell K2. Change E2 in the argument to J2, and then drag down even with column J.
Recall that the formula for change is (new – old)/old. In cell L3, enter the following:
and drag down to L12. You can now see from column L that the change in Revenue from one observation to the next is small, but definitely not constant.
Beginning in column M, repeat this process for Income and ROI. When you finish, you’ll have a table that looks like this:
As you can see, none of our functions are linear, regardless of whether they appear to be straight when we graph them. Also note that all of the functions are decreasing in relation to Billable Hours, some faster than others.
The Minimum and Maximum of a Function
As we discussed above, Solver is designed to minimize or maximize the value of whatever function you specify. Generally speaking, however, only parabolic functions will have an intrinsic minimum or maximum value. For example, if we look at the plot of a convex parabolic function:
it’s easy to see where the minimum value would be located. In the same way, if we look at the plot of a concave parabolic function:
we can readily point out where the maximum value is located. The distinctive feature of a parabolic function is that the change in value is negative in some places on the curve and positive in others. As you may have guessed, the maximum or minimum of a parabolic function is the place on the curve where change, δ, is equal to zero.
Yet in many cases, the shape of the curve is more subtle. In our headcount analysis curve, change is strictly positive all along the curve – at least for the part we’ve plotted – and nowhere does δ = 0. This means we won’t be able to find a maximum or minimum value for the function unless we impose arbitrary constraints on the Billable Hours variable.
Obviously, we know there’s an absolute constraint on the number of hours an associate can bill that’s represented by 24 × 365 = 8760 hours in a year (insert appropriate lawyer joke here). We also know that there’s some practical limit on the number of billable hours an associate will generate that caps off somewhere around 2400 hours per year (except in Manhattan).
At the same time, we know there’s a practical constraint in the other direction that represents the minimum number of hours an associate must bill in order to keep from being fired.
So in reality, our headcount profitability function is already limited to a particular range. All we need to do is specify for Solver the minimum and maximum values for Billable Hours that we want it to consider.
Before we return to the Solver window, we need to set up worksheet cells that contain the functions we want Solver to work on.
Go to cell F2 and Copy, then Paste Special > Formulas in cell B10. Change E2 in the argument to B7. Then copy and paste the formula from cell G2 to cell B11, changing the reference from F2 to B10. Last, copy the formula from cell H2 to cell B12, changing the reference from G2 to B11.
Since Revenue is the only function that is directly dependent on Billable Hours, it’s the Revenue function that we’ll maximize.
In the Set Objective box, make sure it reads $B$10. Since we’re seeking the maximum, click the radio button labeled Max if it isn’t already selected. Then in the box labeled By Changing Variable Cells, you can either use the locator button to the right to select cell B7, or manually enter $B$7.
To include constraints, click the Add button. In the window that appears, designate that cell $B$7 should be <= 4800 (or whatever maximum value you prefer). It should come as no surprise that maximum Revenue will correspond with the maximum number of billable hours you enter.
Then click OK. Next, click Add again, and specify that cell $B$7 should be >= 3600 (or whatever minimum number of hours you want to specify). Click OK. You can ignore the box labeled Make Unconstrained Variables Non-Negative, because we don’t have any unconstrained variables in this example. Our Revenue function is non-linear, so for the solving method choose GRG Nonlinear, which should already be selected as the default.
One final thing – if your argument for Overhead in cell B8 contains a reference to B7, then replace B7 with the number 4000.
When you’ve completed all that, click Solve.
If all goes well, Solver will return a window that looks like this:
Before you decide whether to keep the solution that Solver found, first look over at the changed variables and make sure that the value(s) is/are within the constraints you specified. Then look over at the Objective cell and make a determination as to whether the result is plausible. If both check out, then you can click OK. If not, it’s usually best to select the radio button labelled Restore Original Values before clicking OK.
You should now see the values below in cells B10:B12
Next, to find the number of billable hours that correspond with a particular ROI, first bring back up the Solver window. Change the Objective cell to $B$12. Let’s assume that we want to know the number of billable hours that corresponds with a twelve percent ROI. Start by clicking the radio button labeled Value Of and enter 0.12 in the box to the right. Leave everything else unchanged, and click Solve.
You should see that Solver determined the number of billable hours corresponding with a 12% ROI is 3983.75.
In the same way, if you want to calculate the number of billable hours that corresponds with break-even, leave $B$12 as the Objective cell, and enter 0 in the box to the right. Then click Solve.