I. Introduction to Planner Lab
The goal of this tutorial is for you to become acquainted with the Planners Lab software for financial and business planning. It is easy 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 many more 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.
2007Sales Volume
Garden furniture sales volume in units
Garden tools sales volume in units
Trees sales volume in units
Annual plants sales volume in units
Perennial plants sales volume in units
Landscaping services sales volume in customers
Materials Expenses
Garden furniture cost per unit
Garden tools cost per unit
Tree cost per unit
Annual plant cost per unit
Perennial plant cost per unit
Manpower cost per landscaping customer
Sales Price
Garden furniture price per unit
Garden tools price per unit
Trees price per unit
Annual plants price per unit
Perennial plants price per unit
Landscaping services price per customer
Profit Before General & Administrative Expenses G&A)
Garden furniture
Garden tools
Trees
Annual plants
Perennial plants
Landscaping services
Total / 950
15,000
2,500
20,000
10,000
500
$ 225
$ 15
$ 35
$ 2
$ 4
$ 35
$ 310
$ 22
$ 55
$ 4
$ 6
$ 75
$ 80,750
$ 105,000
$ 50,000
$ 40,000
$ 20,000
$ 20,000
$ 315,750
II. 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 from one's head based upon opinions.
The profit centers are garden furniture, garden tools, trees, annual plants, perennial plants, and landscaping services. The profits are before employee salaries, rent, utilities, insurance and other general expenses (all together these makeup G&A or General and Administrative). Landscaping services does have salaries 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 take you 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 YEAR are 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 2007 THRU 2010 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 things in such that it makes sense to you and chances are it will be OK. For now go ahead and type in 2007 THRU 2010.
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 "x" 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 remaining columns 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 Growth rate in garden furniture sales 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 way intended. Again it makes perfect sense if you read it. The Growth rate in garden furniture 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:
2007 / 2008 / 2009 / 2010Growth rate in garden furniture sales / 1.0 / .9875 / .9625 / .9375
The word PREVIOUS is a handy for growth 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 unit IN 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)