Introduction to the Planners Lab CIS510

               Successful financial planning is a core critical success factor for every organization. The Planners Lab (PL) is not solely for financial planning, but that is its primary focus. Example applications include strategic planning, financial planning, capital budgeting, balanced scorecards, project investment analysis, merger and acquisition analysis and sales forecasting. This document provides an introduction to the basic features of the Planners Lab and assumes no prior knowledge of this software.

The basic components of the Planners Lab (PL) are (1) an algebraically-oriented model-building language and (2) easy to use options for visualizing model output and answers to what-if and goal-seek questions; that is, to analyze results of changes in assumptions. The combination of these components allows business managers and analysts to build, review and manipulate the assumptions that underlie a decision making scenario.

To illustrate, let’s keep things simple and assume that the company is a small store that sells only one kind of notebook computer. Its price in 2007 was $1350.00 and 300 units were sold. We will build a PL model to make financial plans for this company through 2012.

When you launch the Planners Lab, you get a screen like that in Figure 1. Models are written in an algebraic-like language and may be written in the native language of the user. Models are arranged into hierarchically organized nodes shown in the left window. Node names are defined by the user and are initially blank. The model we will use will have three nodes: Revenue, Expenses and Profit. To create the Revenue node in the leftmost window, single click new node 1, then double click and type Revenue to name the node. To add a node, click Model Design so the node goes under its level (the same level as the Revenue node) and click the Add button at the bottom of the screen. Then name this node Expenses as before. Repeat to add the Profit node.

Figure 1. Initial screen for entering a new Planners Lab model.

Nodes are initially blank, like the cells in a new Excel spreadsheet would be. The user must define the equations in the model. The equations for Revenue are shown below and in the large window on the right, in the screen shot in Figure 2. These have been typed in by the user (although you can copy them from a word processing document and paste them into a PL node). The narrow window at the top right indicates that the columns are to cover years 2007 through 2012. The default is 2008 to 2010, but these have been changed by the user. Line 1 in the model indicates that Sales Price starts out at 1350 in 2007 and the phrase PREVIOUS * Inflation Rate means that prices in subsequent years will be the previous year’s price times the inflation rate.

1. Sales Price = 1350, PREVIOUS * (1.0 + Inflation Rate)
2. Units Sold = 300, PREVIOUS * (1.0 + Sales Growth Rate)
3. Total Revenue = Units Sold * Sales Price
4. Sales Growth Rate = TRIRAND(-0.05, 0.10, 0.15)
5. Inflation Rate = 0.05

Figure 2. Equations in the Revenue node.

The inflation rate is set to 0.05 in line 5. It is assumed to be that for all the years in the model. Similarly, Units Sold starts at 300 and increases by the Sales Growth Rate. In line 4, Sales Growth Rate is defined to be a random variable with a triangular distribution with values ranging from -5.0% to +15% with an expected value of 10%. Normally distributed variables are also supported.

Here are the equations in the Expense node (which is not shown):

1. Unit Sales Expense = 100, PREVIOUS * 1.10
2. Unit Cost = 1000, PREVIOUS * (1.0 + Inflation Rate IN Revenue)
3. Total Cost = (Unit Sales Expense + Unit Cost) * Units Sold IN Revenue

The only thing new here is use of the IN keyword in line 2 to indicate that the variable Inflation Rate is found IN the Revenue node. Planners Lab keywords are always in all capital letters. Variable names are case sensitive so, for example, Unit Cost is not the same as Unit cost. To complete the model here are the equations in the Profit node:

1. Net Profit = Total Revenue IN Revenue - Total Cost IN Expenses
2. Net Profit After Taxes = 0.80 * Net Profit

For visualizing model output, the PL provides a ready ability for managers to “play” with assumptions that reflect alternative views of the future in an engaging, visual manner. To view output and perform What If analysis, the user clicks on the Playground button in the bottom right of the screen. Output can be viewed in tabular form similar to that of spreadsheets (Figure 3). The table is originally blank and is populated by clicking on a node in the left window, a list of the variables in the node appears in a pop-up window and some or all of the variables may be selected and dragged onto the table. All the variables in the model have been dragged to the table in Figure 3.

Figure 3. Tabular display of all the variables in the model.

A more engaging, “imagistic“ view with trend lines for Net Profit After Taxes, Sales Growth Rate and Unit Sales Expense is shown in Figure 4. Net profit is a goal variable in this scenario and Sales Growth Rate and Unit Sales Expense are What If variables. Goal variables do NOT appear on the right hand side of any equation in the model, What If variablesl do. Notice that the expected value is used for the random variable Sales Growth Rate. Randomness is treated with risk analysis as described later.

Figure 4. Trend lines for Net Profit After Taxes, Sales Growth Rate and Unit Sales Expenses.

It’s a trivial matter to create line charts and other graphs. The window above the line charts in Figure 4 shows icons for the various charts that are currently available in the Planners Lab. From left to right, the icons represent charts for goal variable trend lines, goal variable bar charts, tables (which may contain either goals or What If variables), then 3 icons with question marks (?) on them for What If trend lines, bar charts and tables, respectively.

To create a chart simply drag the icon onto the display window (stage) and a blank chart pops up. Then click the desired node on the Model Tree (which has been closed in Figures 4 to make room for the display), and a list of variables pops up from which you select the variables desired and drag them to the chart. The system automatically chooses scales for the charts.

To perform a sensitivity analysis, you may grab a What If trend line with the pointer and drag it to a desired point, and the lines for the Goal variables will change accordingly. In Figure 5, the user has moved the line for Sales Growth Rate to a point representing 15% growth and Unit Sales Expense levels off at 128 in 2008 and beyond. It may be difficult to see in the screen print in Figure 5, but the pointer has been placed on the point for 2010 on the Net Profit After Taxes line and the small pop-up window above indicates that the base value with the original assumptions was 120,818, and with the new assumptions is 165,005.

Figure 5. Effects on Net Profit After Taxes of expected Sales Growth Rate Increasing to 15% and lower Unit Sales Expenses.

The next three icons in the chart window list that have circular target-like rings on them are for goal-seeking charts. In goal seeking, one variable is chosen as the target and another as the What If variable that will change. The difference is that the line for the target variable is manipulated in goal seeking and the What If variable changes in response to it. In Figure 6, Total Revenue has been chosen as the target variable in the chart on the left. Sales Growth Rate is the What If variable on the right. This scenario assumes that we would like to achieve sales of about 1.1 million by 2012, and the What If chart shows that the Sales Growth Rate would have to be about 21% to achieve that goal. Only one What If variable may be active at any given time in goal seeking.

Figure 6. Goal seeking with Total Revenue as the Target and Sales Growth Rate as the What If variable.

The next icon in the Chart Menu is for an Impact Analyis, which shows the impact of proportional changes in several What If variables on one selected target variable. In an impact analysis, all variables change by the same perctange. Figure 7 shows an Impact Analysis of the effects of a 10% change in Sales Growth Rate, Inflation Rate and Unit Sales Expense on Net Profit After Taxes. The vertical slider bar on the right is used to select the percent change, and the horizontal slider bar at the bottom is used to select the time period. The pointer was over the bar for Unit Sales Expense when this screen shot was taken and the pop-up window above the bar indicates that a 10% increase in Unit Sales Expense results in Net Profit After Taxes dropping from 120,000 to 111,774 (5.64%) drop in Net Profit After Taxes in 2012.

Figure 7. An Impact Analysis of the effects of a 10% change in Sales Growth Rate, Inflation Rate and Unit Sales Expense on Net Profit After Taxes.

The next icon is the typical bell-shaped, normal curve which is used for a risk analysis, when a model contains random variables. Figure 8 illustrates a risk analysis with these assumptions using the random variable Sales Growth Rate.

To create a risk analysis chart, first its icon is dragged onto the stage. Then the desired variable to be viewed is chosen from the appropriate node in the model tree and dragged to the small window at the top of the chart. Here Net Profit After Taxes has been chosen. It is partially a function of Sales Growth Rate, a random variable. Next the Simulate button is clicked and the number of iterations desired is selected (500 is the default). Click ok and the chart is created as shown in Figure 8.

The bar chart on the right has been generated by clicking the Simulate button on the lower right portion of the chart. The slider bar below the chart is set on year 2012. The endpoints on the horizontal axis of the chart show that Net Profit may range from about 74,000 to 140,00 by 2012. This can be moved to any year we choose.

Figure 8. A Risk Analysis with Sales Growth Rate as a Random Variable.

The pointers on the horizontal axis of a risk analysis chart indicate the number of observations falling in that range. For example in Figure 9, the mouse has been used to move the left point to about 87,000 and the right point has been moved to about 122,000. By looking at the percentage above the highest bar, we can see that 90% of the observations lie in this interval, indicating that there is over a 90% chance that Net Profit After Taxes will be between 87,000 and 121,000

Figure 9. The Effects of Moving the Pointers on the Horizontal Axis of the Bar Chart

The final two icons that will be discussed in this tutorial are not for analysis but rather for debugging and documentation. The icon with the hierarchy chart is for a variable tree, such as the one for Net Profit After Taxes in Figure 10. This shows all the variables having a direct or indirect effect on the selected target variable. The final icon is for a Sticky Note which can be dragged onto the display to enter text describing a chart or table. Here the Sticky Note briefly describes what the Variable Tree does.

Figure 10. The Variable Tree for Net Profit After Taxes, with a Sticky Note attached to describe it.

Planners Lab models may be shared in a networked environment in which users are connected only occasionally, and it supports the use of mobile devices. Individuals may maintain their own personal versions of models, or may use a shared version, very useful features in current management era. It is also a simple matter to import and export appropriately formatted Excel™ spreadsheets, as will be explained later.