Using reports for "what-if" analysis

ManagePLUS creates reports in its own internal spreadsheet, which provides several advantages. An important one is that it lets you use the report as a starting point for answering management questions...

"What if labor costs rise by 15%? How much will that add to my per-unit cost of producing running shoes?"

"What if corn yields fall by 10% this year? How would that affect my per-bushel cost of production?

When you first prepare a report it is a summary of your actual accounting data. ManagePLUS lets you turn it into into a business projection by changing parts of the report (cells of the report spreadsheet) and seeing how those changes affect per-unit costs and revenues, profit, and other aspects of your business. Because making such changes lets you see what would happen "if" things changed in a certain way, it is often called "what-if" analysis or playing "what-if".

This topic gives you details about using ManagePLUS reports for what-if analysis.

Specifically, the screen shots in this topic are based on data in the ManagePLUS Farm Example, a sample QuickBooks company file installed with ManagePLUS.

ManagePLUS lets you change some cells in a report but not all of them. If important cells weren't protected from editing, you might accidentally erase or overwrite necessary formulas on which the report depends.

To make it easy to see which cells are editable, you can have ManagePLUS highlight them in a different color. This is the default behavior of ManagePLUS, but sometimes you may want to turn off the highlighting while printing reports, and turn it on again for making further report changes. Here are two view of the same cell area, with and without highlighting.

with highlighting:

without:

•	To toggle editable cell highlighting on/off, click on the Highlight editable cells item in the report's Format button menu.

If you want to change the highlighting color, you can do that in the Preferences window.

To use a report for what-if analysis, edit the contents of one or more cells in the report spreadsheet.

The first two approaches let you make selective changes—like just changing one or two digits. The last approach (click in the cell, then begin typing) replaces the cell's contents entirely.

This discards any changes you've made to the cell, and restores the contents it had before you began editing. This only works while you're still editing the cell. If you've moved to a different cell or pressed the Enter key, your changes can't be canceled.

ManagePLUS will save your changes and automatically recalculate the report, updating all other cells which depend on the cell you just edited.

The easiest way to learn about doing what-if analysis is to try it. This example demonstrates the idea.

Here's a fragment of the Expense section from a Profit & Loss with Classes report:

The selected cell (the one with the dark border) shows an expense of $6720 in the Fertilizer:Nitrogen account for the Corn:2007:Jones class.

•	Type 8400 in place of the 6720 (a 25% increase), then press Enter. This increases the nitrogen fertilizer cost for the Corn:2007:Jones class by 25%, and moves down to the next cell.

•	Type 14400 in place of the 11520, then press Enter. This increases the nitrogen fertilizer cost for the Corn:2007:Smith class, also by 25%. The report should then look like this:

•	A 25% increase in fertilizer cost would raise the per-acre cost of nitrogen for growing corn to $84.00 for the Corn:2007:Jones class, assuming fertilizer is applied at the same rate per acre.

•	Every total or calculation related to the spreadsheet cells that were changed, have been updated in the report, including:

•	We could continue making other changes to other editable cells in the report, to see the effects of multiple changes at once.

Some reports include supporting information which you can edit, to see how it affects the report. For example, the Profit & Loss with Classes report shows income and expense by account but also has class breakdowns within each account. The supporting information for these classes can be edited to see their effect in the per-unit expenses, etc., in the report.

Reports which are based on supporting information put a copy of it on supplemental sheet of the report, separate from the main report sheet. If you change information on the supplemental sheet, the main report will be updated to reflect your changes—the same as when you changing editable cells in the main report.

Let's see how to change supporting information in the Profit & Loss with Classes report.

This switches the report's view to the supplemental page. Unless you've turned off highlighting of editable cells they will be appear in a different color, showing which cells are editable. In this illustration, the editable cells are light blue:

•	Change management quantities as shown below for the Corn:2007:Jones class and its parent classes. (Leave other classes unchanged.)

Corn

...

2007

Jones

600

...

350

200

When I change information for a child class, why isn't the parent class information updated automatically?
This is by design. Many situations call for analyzing parent classes on a basis that's unrelated to the one used for child classes. So if you want a child class' information to contribute to a "total" for its parents, you must update parent class information manually, as we have done in this example.

When you are finished making changes, the Corn class area of the supplemental page should look something like this (red arrows indicate the changed cells):

All report results dependent on the Corn class information, have been updated to reflect your changes on the supplemental sheet. For comparison, here's the same Fertilizer:Nitrogen area of the main report we worked with in "What-if" changes: an example, above. The newly changed cells are highlighted in red.

As you can see, fertilizer cost per acre has decreased. Why? Because we increased Corn class acreages without changing the dollar amount spent on fertilizer. With the same dollar amount divided by a larger acreage, the report projects a smaller per-acre cost.

We used fertilizer expense for this example because it has continuity with the other screen shots in this topic and therefore lets you compare the screen shots to see how the main report sheet was affected by making changes in the supporting information. But fertilizer is a direct variable cost of producing corn, so in reality you probably wouldn't project an increase in corn acreage without also projecting an increase in spending for fertilizer too.

A more real-world example of why you'd play "what-if" with supporting information, would be to see how different management quantity levels would affect per-unit costs for fixed- or overhead-expense items like machinery depreciation. Assuming more acres of corn could be produced with the same set of machinery, changing the Corn class acreages would show how fixed machinery cost (depreciation expense) per acre would change with more acres of production.

When ManagePLUS builds a report, it copies the information from the supporting list's window to the report's supplemental sheet. So when you change information on the supplemental sheet of a report you are changing the report's copy of the information, not the "master copy" in the list's window.

In the example above, the Corn class acreage changes would affect only the report where those changes were made. If we were to open another (new) copy of the report, it would be built with information direct from the Classes window; and so, it would not include the acreage changes made in the first report.

Where you change supporting information then, should depend on how persistent you want those changes to be:

•	Changing information in the list's window (e.g., the Classes window) affects all reports you may create for that accounting period, from then on.

•	Changing information in the supplemental sheet of a report affects only that report.

If you want new supporting information to affect all reports make your changes in the list's window, then Refresh any reports that are currently open, to make them use the new information.

If you want the changes to affect only the current report, make your changes on the report's supplemental sheet (e.g., the Class Info tab).