Calc2Code Tips of the Day

Day 2

Today's first tip shows you how to use Calc2Code to create a two dimensional table in Excel to perform what-if analysis. the spreadsheet profit.xls contains a spreadsheet which calculates the profit for a business. The revenues are driven by the price of the company's single item, and the expenses are a funcion of the number of workers employed. Although it is a rather simple model, it's difficult to do all the math in your head, and a spreadsheet is a great way to calculate the profit and try to make sure the price and employees are chosen wisely.

Here's the spreadsheet profit.xls:

The demand goes up as the price goes down, and the number of units sold is the lower of the demand, and the number of units produced (which is itself a function of the number of workers).

Seeing how the profit changes as the price and the number of workers changes can be done inside this spreadsheet by changing C3 and C6 over and over again, but this is tedious. instead, we will use Calc2Code to turn all the calculations into a single function. This is how to set it up in the configuration screen:

There are two drivers to the function, C3 and C6,and the profit is in C18. We close this, push the builder button, and a few moments later we have a function called Profit.

Now, we can create proft2.xls and use this function to quickly build a table that demonstrates the relationship between profits, prices and staffing:

Of course, we could use Solver to find the optimal combination that maximizes profit, but the table gives us a better feel for how much each worker is worth or whether changing the price a little makes a big difference.

Taken together with yesterday's tips, we can see a theme emerging: Calc2Code enhances Excel by encapsulating the logic of multiple cell calculations into a single formula. And as we saw with the commission examle yestersay, even long single cell formulas can be helped by Calc2Code since breaking them up into many smaller formulas leads to fewer errors and faster development and testing.

Our next tip demonstrates a different aspect of Calc2Code. Click here to view it.