This document can be used as a Tutorial, a case study, and as an example model. It is used as a Planners Lab teaching tutorial. It can also be used as a software user’s guide. This was prepared by Dr. Jerry Wagner, University of Nebraska at Omaha, email . There will surely be suggestions for improvement and also finding of mistakes. Please send those to Jerry with specific suggestions and specific locations in the document.
Planners Lab™ Teaching Tutorial
Westlake Lawn and Garden
Table of Contents
I.IntroductionPage 2
II.Planners Lab ModelingPage 4
III.Some More Frequently Used KeywordsPage 21
IV.Risk AnalysisPage 28
V.More Chart TypesPage 32
VI.Chart PropertiesPage 32
VII.More StuffPage 34
Making choices is a life long activity. Making financial choices occurs during most of one’s life time. Such choices can vary from choosing between alternative vacations, preparing personal budgets, making personal investments, deciding whether or not to expand a family business, marketing strategies for a large corporation or a 5-year financialstrategy for a Fortune 500 company. The skills and tools needed for intelligent and informed financial choice making have potential value to nearly every human being.
The skills include creative thinking, design thinking, logical thinking, weighing alternatives, making trade offs, analyzing cause and effect relationships, and the ability to see the impact of various factors upon alternative scenarios. Even simple decision making and financial planning are beyond the human minds ability to imagine the cause and effect relationships that might exist. The only way to do this is with a simulation model supported by data visualization.
Simulation modeling and data visualization for such choice making and analysis are used by technical analysts with specialty skills and specialty software. What they do can now be done by anyone with the Planners Lab software.
The Planners Lab lets those with minimum computer skills and little or no simulation or modeling skillsbuild meaningful and useful models. These models are then supported by state-of-the-art visualization tools that are very easy to use. High school students or corporate executives have no difficulty learningsuch tools to build “real world models” with only an hour or so of training followed by practice.
Building such models is a creative process. It sparks imagination, innovation and entrepreneurship. Much like with video games it can become contagious and addictive.
The software is free for schools. An attractive subscription rate is available for businesses, government agencies and consulting firms.
I. Introduction
The goal of this tutorial is for you to become acquainted with the Planners Lab software for financial and business planning. It is easy and play-like while also being intellectually sophisticated. You can entertain alternative assumptions about the future of any business or investment including your own personal budget. The easiest way to get it quick is by hands on with a simple case study. The case used here is for the hypothetical Westlake Lawn and Garden business owned by your parents.
This document is to be used along side your using the actual software on your own computer. After this initial jump start you can explore, discover, and find manymore ways of using the software.
Your parents started Westlake Lawn and Garden eight years ago and have steadily grown the business and made a good living. However,they have run out of ideas on how to accelerate growth and quite frankly they are tired. They want to tap someone else’s energy and imagination. You are the source of energy and imagination that they want to tap. They want you to prepare a financial plan for the business and then take over the business to make it all happen.
Figure 1 gives the budget numbers for 2007. These projections will be included in our Planners Lab model as starting point fixed numbers.
Figure 1
2007
Sales Volume
Garden furniture sales volume in units 950
Garden tools sales volume in units 15,000
Trees sales volume in units 2,500
Annual plants sales volume in units 20,000
Perennial plants sales volume in units 10,000
Landscaping services sales volume in customers 500
Materials Expenses
Garden furniture cost per unit $ 225
Garden tools cost per unit $ 15
Tree cost per unit $ 35
Annual plant cost per unit $ 2
Perennial plant cost per unit $ 4
Manpower cost per landscaping customer $ 35
Sales Price
Garden furniture price per unit $ 310
Garden tools price per unit $ 22
Trees price per unit $ 55
Annual plants price per unit $ 4
Perennial plants price per unit $ 6
Landscaping services price per customer $ 75
Profit Before GeneralAdministrative Expenses (G&A)
Garden furniture $ 80,750
Garden tools $105,000
Trees $ 50,000
Annual plants $ 40,000
Perennial plants $ 20,000
Landscaping services $ 20,000
Total $315,750
- Planners Lab Modeling
Years 2008 through 2010 represent the planning horizon for our model. Remember that 2007 has the fixed budget numbers that can’t be modified. Historical numbers such as for 2007 are not necessary for modeling and forecasting. Often the starting data comes fromone’s head based upon opinions.
The profit centers are garden furniture, garden tools,trees, annual plants, perennial plants, and landscaping services. The profits are beforeemployee salaries, rent, utilities, insurance and other general expenses (all together these makeupG&A or General and Administrative). Landscaping services does havesalaries considered since it is people intensive and outsourced.
Each of these revenue categories is a conglomerate of several items. For example there are many different kinds of trees sold. If you are motivated to flesh out a more complete model you can do so later on.
Once you have built a Planners Lab model you will then want to ask “What If” questions and do other sensitivity analyses. This will help you to see which products have the most potential to impact profits. This will lead you to being creative in finding ways to increase profits from product categories where it matters most.
Conceptually, most financial plans and most business decision making comes down to (1) cash in (2) cash out and (3) the difference between the two. Using this conceptual framework we now want to build a model for Westlake using the Planners Lab software.
Go ahead and launch the software and click on “New” (See Figure 2).This will takeyou to what is essentially a blank board onto which to start building the model.
Figure 2
After launch, notice at the top is a window with the word “Columns” (see Figure 3). This is where you indicate the number of time periods in your model. It could be years, months, quarters or whatever.
Figure 3
The main text box allows two forms, a comma separated list or the starting and ending unit separated by THRU. The boxes below the main field modify the column statement. Currently Days, Weeks, Month, Quarters, and Years are supported.
The columns can be either a “specific time” such as Jan 2007 THRU Dec 2008 or a “general time” such as YEAR 1 THRU YEAR 10. The words THRU and YEARare keywords in the language. The options for time period keywords are the following:
Y, YR, or YEAR
Q, QTR, or QUARTER
M OR MONTH
W OR WEEK
D OR DAY
All the above are keywords or reserved words.
For our example, one option to indicate number and names for time periods is to type 2007THRU2010 into the window. Notice the word THRU. It is a “keyword” and this will be explained a little later. Other options could be 2007-2010 or 2007, 2008, 2009, 2010. As a general rule of thumb throughout the software, type in things such that it makes sense to you and chances are it will be OK. For now go ahead and type in 2007THRU2010.
In the left hand column you will see a blue “box” (see Figure 4). This will end up looking like a tree that contains names of categories into which we will place equations for assumptions and data. Don’t be concerned about the scary word “equations”. It is really simple, intuitive, and natural. It is the way people actually think.
Figure 4.
For this example let’s say the categories are Sales Volume, Materials Expenses, Sales Price, and Profit Before G&A.
Note that you do not have to divide data up into categories. You could just have one general “category” that contains all assumptions and data. However, breaking up the model into small chunks helps when communicating your plans to other people. Since we are adding 4 categories and one already exists (“new node 1”) go ahead and click the “+” button at the bottom 3 times.
Figure 5
Now we have 4 “new nodes” in the category tree under “Model Design”. Double click on the first node (“new node 1”) and type in Sales Volume, the second and type in Materials Expenses, the third and type in Sales Price, and the fourth and type in Profit Before G&A. The completed tree now looks like that in Figure 6.
Figure 6
Eventually you might want to add other nodes. You can also add sub nodes. For example click on one of the above nodes and click the “+” sign at the bottom. If you want to get rid of this node just highlight it and click the “Delete”button at the bottom.
Now inside each category we’ll add variables and data for each variable, i.e., equations. For example a variable for the Sales Volume category could be “Growth rate in trees volume”. Every variable is in the form of an equation with a name on the left hand side of the equation and data and/or computations on the right hand side. For example we could have:
Growth rate in trees volume = 1.15
Let’s say this equation is in a 5 year model. This means that growth rate is going to be 15% each year. A rule: whatever is the last thing that appears in an equation is used for all remainingcolumns or time periods. For here the growth rate in 2008 is 15% and it will be 15% each year. A rule: if a variable changes from one time period to the next just separate the data by commas. For example:
Growth rate in Trees Volume = 1.15, 1.20, 1.25
This means the growth rate in the first period will be 15%, 20% in the second and 25% in the third, fourth and fifth time periods. These few “rules” are easy to remember and they are intuitive.
Figure 7 has equations for each of the nodes, i.e., each category. You could write these equations in any number of ways and enter them in any order. For example rather than using Growthrate ingarden furnituresales you could have used “GR FS”. You call things what you want and what makes sense to you. You write the story in your own words.
There is a “keyword” or “reserved word” in this list of equations called PREVIOUS. Previously we saw the keyword THRU. These words are in all caps, bolded and blue. This indicates they are Planners Lab key words. That simply means they are to be used in the wayintended. Again it makes perfect sense if you read it. The Growth rate in gardenfurniture sales is 1 in 2007 (the first column is fixed), i.e., there is no change since this is the budget already in place. Sales are thought to be declining 2.5% per year so 2008 is 1 minus .025 or .9875 for a growth rate. PREVIOUS - .025 is the last thing that appears in the equation so it is used in all remaining time periods. Therefore the data for this variable are as follows:
2007200820092010
Growthrate in garden furnituresales 1.0.9875.9625 .9375
The word PREVIOUS is a handy forgrowth rates.
Throughout, while using the Planners Lab use your intuition and common sense with a little bit of logic. We say that you should be able to read your models like stories.
Figure 7
Sales Volume node:
Note that the left hand sides of the equations are colored red. That is just to make a model easier to read. Go ahead and read the following equations. It should read just like you are verbally telling someone your plan.
Growth rate in garden furniture sales = 1,PREVIOUS - .025
Garden furniture sales volume in units = 950 * Growth rate in garden furniture sales
Growth rate in tools sales volume = 1.03
Garden tools sales volume in units= 15000, PREVIOUS * Growth rate in tools sales volume
Growth rate in trees volume =1.15
Trees sales volume in units = 2500,PREVIOUS* Growth rate in trees volume
Growth rate in plants sales volume = 1.10
Annual plants sales volume in units = 20000,PREVIOUS* Growth rate in plants sales volume
Perennial plant sales volume as percent of annual plant sales = .50,PREVIOUS* 1.05
Perennial plants sales volume in units = Perennial plant sales volume as percent of annual plant sales* Annual plants sales volume in units
Landscaping growth rate = 2
Landscaping services sales volume in customers = 500,PREVIOUS* Landscaping growth rate
Materials Expenses node:
Cost increase factor for Garden furniture = 1.025
Garden furniture cost per unit = 225, PREVIOUS * Cost increase factor for Garden furniture
Cost increase factor for Garden tools = 1.01
Garden tools cost per unit = 15, PREVIOUS * Cost increase factor for Garden tools
Cost increase factor for Trees = 1.02
Tree cost per unit = 35, PREVIOUS * Cost increase factor for Trees
Cost increase factor for Annual plants = 1.03
Annual plant cost per unit = 2, PREVIOUS * Cost increase factor for Annual plants
Cost increase factor for Perennial plants = 1.03
Perennial plant cost per unit = 4, PREVIOUS * Cost increase factor for Perennial plants
Cost increase factor for landscaping manpower = 1.08
Manpower cost per landscaping unit = 35, PREVIOUS * Cost increase factor for landscaping manpower
Sales Price node:
Price increase factor for Garden furniture = 1.025
Garden furniture price per unit = 310, PREVIOUS * Price increase factor for Garden furniture
Price increase factor for Garden tools = 1.03
Garden tools price per unit = 22, PREVIOUS * Price increase factor for Garden tools
Price increase factor for Trees = 1.01
Trees price per unit = 55, PREVIOUS * Price increase factor for Trees
Price increase factor for Annual plants = 1.04
Annual plants price per unit = 4, PREVIOUS * Price increase factor for Annual plants
Price increase factor for Perennial plants = 1.02
Perennial plants price per unit = 6, PREVIOUS * Price increase factor for Perennial plants
Price increase for Landscaping services = 1.08
Landscaping services price per customer = 75, PREVIOUS * Price increase for Landscaping services
Profit Before G&A node:
Note that another Planners Lay keyword shows up in this node, i.e., the word IN. This is simply a way to refer to a variable(s) in another node(s)that you want to use in this node. Just read it and it will make sense.
Garden furniture profit = (Garden furniture sales volume in units IN Sales Volume * Garden furniture price per unit IN Sales Price) - (Garden furniture sales volume in units IN Sales Volume * Garden furniture cost per unit IN Materials Expenses)
Garden tools profit =(Garden tools sales volume in units IN Sales Volume * Garden tools price per unit IN Sales Price)-(Garden tools sales volume in units IN Sales Volume * Garden tools cost per unit IN Materials Expenses)
Trees profit = (Trees sales volume in units IN Sales Volume * Trees price per unit IN Sales Price)-(Trees sales volume in units IN Sales Volume * Tree cost per unit IN Materials Expenses)
Annual plants profit =(Annual plants sales volume in units IN Sales Volume * Annual plants price per unit IN Sales Price)-(Annual plants sales volume in units IN Sales Volume * Annual plant cost per unit IN Materials Expenses)
Perennial plants profit = (Perennial plants sales volume in units IN Sales Volume *Perennial plants price per unit IN Sales Price)-(Perennial plants sales volume in units IN Sales Volume *Perennial plant cost per unitIN Materials Expenses)
Landscaping services profit =(Landscaping services sales volume in customers IN Sales Volume * Landscaping services price per customer IN Sales Price)-(Landscaping services sales volume in customers IN Sales Volume * Manpower cost per landscaping unit IN Materials Expenses)
Total Westlake profit = Garden furniture profit + Garden tools profit + Trees profit + Perennial plants profit + Landscaping services profit + Annual plants profit
Go ahead and type in these equations for each node. You could also just cut these from this word document and paste them into the Planners Lab. You can change names of variables if you wish. Just be sure you are consistent in how you spell things and what you call things. When finished, click on the “Validate Model” button at the bottom left.
Figure 8
If you made no errors it will tell you that. If you made errors it will tell you and tell you where the errors were made. The most frequent types of errors are misspelled words, i.e., a variable is not spelled the same wherever it is used. Note also that variable names are case sensitive. If you capitalize a word in one place and use it again elsewhere then you need for it to be capitalized again. Again this is very simple but something to remember. Once you make such mistakes you will be more cautious. If you make a mistake it is easy to fix.
Notice the four icons in the upper right hand corner of the stage in the model building mode.
The icon is for dragging and dropping a previouslydeveloped variable name into a new equation.