You have received this Profit Plan Newsletter as a licensed user of Profit Plan® -- Your Vision of Tomorrow®.

              PROFIT PLAN PLANNING TIPS - August, 2002

August Contents:

      Technical Support - Free Download

      Build an SIC Industry Data Library

      Functional Cosmetics -- Customize Titles

      Projects and Economic Order Quantities - (part 6)
               Reviewing Horizon Linkage Results
               Reviewing Monthly Sales Accounts
               TIP:  How to find Internally Defined Variables
               Handling Partial Year Sales
               Handling Non-financial Account Types
               Computing Units Sold (by month)
              Monthly Cost of Goods Sold Considerations

      Topics Coming in the Next Issue

      Browsing Old Newsletters

Just a reminder...  Those of you with active Annual Technical Support and Profit Plan v2001can download a free update to your copy of Profit Plan v2001 from our website.  For the small business consultants among you, we do highly recommend that you take advantage of the opportunity.  This will provide you with some special archiving abilities that let you build a library of Standard Industrial Classification (SIC) based Industry Ratios as you create each new client's model!

Download your copy of Build 1351 at your convenience.  Some of the enhancements contained in this Update release, added since Profit Plan v2001's first release, are described in the following paragraph. 

Multiple SIC studies can now be entered without leaving the Industry Data form.  You can select any existing study from within the current model or the Master Index at any time, to allow comparison of the firm with multiple industries and/or years.  An Adjustment to Retained Earnings  can be automatically created and computed to handle prior year historical adjustments added by your CPA.  The Amortization/Depreciation Module can now handle more than 250 assets (up to 16,386).  The QuickBooks import is even smarter.  The main menu and toolbars can be relocated to provide more vertical room on the form.  Monthly data can be pasted directly into the Seasonal Profile. The [F9] key now causes recalculation, rather than Ctrl-X.  Sample sheets have been added to the Alpha Plus model showing how to build a personnel payroll budget and a custom ratios report.  The Variance Reports are only refreshed when you actually use them, avoiding annoying delays while these reports are rebuild.  Many messages have been edited for clarity.  Sheets can be unlocked when acknowledging "protection" messages. And more... 

As you probably know, most businesses simply to not closely fit the standard industrial classification (SIC) categories.  For example, your firm may be a telephone equipment wholesaler but you also do installation and maintenance repair.  Or your firm specializes in environmental consulting but half your revenues come from commercial construction contracts.  And even if the firm fits an industrial classification nicely, there are all those "range" issues.  For example, if the firm had $25M in Sales last year and $25M is the top of one study "range" (usually grouped by Total Sales or Gross or Tangible Assets) and it is the bottom of the next range, which study is the most representative?

Because industry studies often do not clearly fit a given firm, it is convenient to be able to see how the firm stacks up against various study ranges and/or industry classifications.  Ask yourself questions like "Should I expect a significant change in inventory turnover as my firm grows through the $10-$25M sales range into the $25M-$50M range I expect to reach in two years?"  Or, if your industry is in transition, you might wonder "Should I be changing the firm's debt to equity ratio to be more in line with the industry trends?"  Often taking the time to capture and compare different year's industry data, or comparing your firm using a range based on Assets instead of Sales will yield interest differences.  You will be surprised at the wide variations in ratios within the same industry due to different firm sizes, as well as how these ratios tend to change over time.

To facilitate comparison of various industry studies with your firm's ratios, Profit Plan accommodates entry and storage of multiple studies.  In all versions of Profit Plan, a single model can store many more studies than will ever be found that might be relevant to your specific firm.  These studies are physically stored within the model and travel with it when zipped for e-mailing or when stored as a new model via the File / Save As command.  Thus industry data for a model remains available for remote review and when the model was saved and updated for a new scenario.

The only downside to storing the data within each specific model was that it had to be reentered, or copy/pasted into a brand new model when one of our Profit Plan consultants began a model for a firm with the same SIC category and range.  To overcome this for consultants, Profit Plan now has the ability to maintain a central library of industry data.  This allows transfer of the data from any given model into a central repository (in Profit Plan's working folder).  Then, when a new company model is created, the central repository can be checked to see if industry data has already been entered.  If so, it can be copied into the new model with a simple command.  So no more need to enter an industry study's data more than once, even when updating your model from year to year.

The titles in a Profit Plan report can be customized to display handy information about key assumptions that went into producing the report or scenario.  You have probably already figured out the obvious.  Selecting Options / Custom Titles from main menu will unlock the titles area and allow entry of custom titles.  And of course the Font options allow it to be colors, italicized, etc.

What you may not be aware of is that these titles can include formulas; i.e. essentially "copy" data from elsewhere in the model to emphasize the use of a key element that varies between scenarios.  For example, suppose you have forecast Annual Sales at a 20% growth rate in one scenario and at 15% in another.  Wouldn't it be handy if the title would automatically change for you when you changed that assumptions itself?  This is possible, but just a little tricky.  However, the ability to easily tell one scenario from another by title may well make it worthwhile.

The best way to learn how to truly customize titles is by working through an example.  We will do this using the Johnson Supply Project - phase 5.zip model last updated in our prior newsletter.  Please download it now, if you would like to follow along below.  (We will also be using this model as we continue with phase 6 of our project planning serial later in this newsletter.)

After opening the Johnson Supply Project - phase 5.zip model from Profit Plan, select the Assumptions tab and the Annual tab at its lower left.  Next, re-expose the titles area, which we hid several issues ago to save screen space while we were building the Annual plan.  (This step has nothing to do with custom titles.  We simply need to make the titles visible again, so we can work with them.)

1. Select Options / Freeze Titles.
2. Scroll the sheet up until the titles are fully visible.
3. Place cursor (click) on the brown cell below the phase "Account Name". (This is where we will freeze titles again.)
4. Select Options / Freeze Titles and then select No so this "new" frozen title area will remain visible automatically.

Now that the Johnson Supply Project's Annual Assumptions & Data Entry sheet is normally positioned, let's actually proceed with the customization.  To do so, proceed as follows:

1. Select Options / Freeze Titles again, to unfreeze the titles, so we can actually edit in this area of the Assumptions sheet.
2. Click the white cell directly below "Assumptions" in the "Assumptions and Data Entry" title.
3. Enter '="(growth rate:"' (excluding the single quote marks) and press the [Enter] key.  This will cause the phrase "(growth rate: " to appear centered below the Assumptions title.  (We only pressed [Enter] to show you that anything entered after the equal sign and enclosed within double quote marks will simply display as quoted.)
4. Click the title again and press the [F2] key twice to open the Cell Text edit box.  (This works with any editable cell in Profit Plan.)
5. After the right-hand quote mark, add an ampersand surrounded with spaces " & ".  This allow adding a new phrase to the prior one.
6. Follow this with '((I9-H9)/H9)*100)  and click OK.  The new title now shows "(growth rate: 20".  (The formula we just entered is simply the standard way you probably learned to compute a numeric percentage in grade school.  But it specifies the cell addresses where the values for Sales in 1997 (I9) and in 1996 (H9) are stored on the Assumptions sheet.  The values are then automatically retrieved and the result computed.)
7. To finish up our title, press the [F2}key to begin editing the title "in place"; i.e. directly in the cell itself.
8. After the "100", add ' & "%)" ' and press the [Enter] key.  The resulting title will now display as "(growth rate: 20%)".

Of course we could have entered all this in step 3 above by typing in '="(growth rate: " & ((I9-H9)/H9)*100) & "%)"' (without enclosing apostrophes).  But we wanted to show you how each step worked and remind you that the [F2] key can help you when editing. 

This new title will now always be correct, regardless of the values in the sales columns.  Since the result is computed directly from the sales data, changing the forecast will automatically change the title as well.

In summary, you can display contents of cells, or results computed from cells, directly in a title cell.  Simply use " & " to attach all the pieces together into a coherent title using a leading "= "sign, and using double-quotes to include any non-numeric phrases you wish.

Last month we finished our first cut at Johnson Supply's strategic plan and linked it to the Monthly view.  Today we will begin examining the results and convert this into our official Monthly Budget directly on the Monthly Assumptions and Data Entry sheet.  When our budget is complete, we will then use the final results to update our near-term strategic plan again.  When finished, we can then use our Variance Reports to track future results against our budget.

We hope you have followed along with this project plan thus far. Each step in the project builds upon the last, so some portions of it may be difficult to follow if you missed the last four issues. If you did miss them, please detour to the Projects and Economic Order Quantities section of our March issue. There you will find some key concepts... project planning, planning items, non-financial assumption types, the BizPlan "Project Financials" window, and more. Then follow on to the April issue, where we added cost of sales considerations and developed a new Gross Profit for the firm with and without the garden sheds project. In the May issue , we then added basic inventory and applied an EOQ formula to purchasing.  In the June issue, we followed this by considering reorder points and impacts on Accounts Payable.  Finally, in the July issue , we completed our initial strategic plan and linked it to the Monthly Assumptions sheet.

Today we are going to pick up from there. For those of you who didn't actually work through the discussion last month and would like to follow along this month, take the time to download last month's partially complete Johnson Supply Project - phase 5.zip model and open it up in Profit Plan now. (You will need Profit Plan v2001 Build 1350 or later to load it properly.)

Reviewing Horizon Linkage Results                                                                                         top

NOTE: The Options / Link Fiscal Horizons feature translates the Annual strategic plan (forecast) Income Statement data into a set of Monthly assumptions by "spreading" the annual totals into either seasonalized or uniform (1/12th each) monthly values.  The interim months (other than the fiscal year-end month) of the Balance Sheet accounts are not spread directly.  Rather they result from the prior year's ending balances and cash flows generated from the Monthly income statement accounts.  Thus the link provides a good basic start for your monthly budget, but it is not intended to be the final answer.  You need to review each account line in the result and either accept or modify the assumptions generated by the link process.

NOTE:  For convenience, it is best to preselect all accounts before you open the Assumption Designer (where you will fine-tune the forecast).  And you will generally be modifying all forecast periods when changing an account's projections.  So, to begin, click the first forecast date on the horizon.  This will highlight the entire period, effectively selecting all account on the sheet for you.  Then right click and open the Assumption Designer.

To follow along with us, make sure you are on the Monthly Assumptions and Data Entry sheet.  (Click the Assumptions tab and the Monthly tab above it, if not.  Then click the "Jan 1997" column header to select all.  Right-click and select the Assumptions Designer.  When you see the "Warning: This horizon is currently linked to the Annual forecast...", click OK.  This is simply warning us that if we make changes to the links generated from the Annual Assumptions Sheet, the Monthly Assumptions sheet will probably diverge from the Annual forecast.  But this is ok, since our intent is to fine-tune the Annual assumptions using our near-term knowledge about the next few months.

Reviewing Monthly Sales Accounts                                                                                          top

Select the Sales - Other account from the Account(s) to Receive formula... list.  This will deselect all but the forecast for this specific account.  More importantly, the selection process also causes the Assumption Designer's Assumptions frame to reflect the current assumption for the first selected period.  In our case this is Jan 1997.

NOTE:  User Defined will appear as the assumption Type when the Sales - Other account is selected.  The term, 'user defined', simply means that the underlying formula in the selected cell (as displayed in the Formula window) is not one of the standard assumptions.  In this specific case, you, as "user", defined it only in the sense that you requested linkage between the Annual and Monthly views.

The Formula window actually contains the formula "=Annual!$I9*SeasonalPcntM".  This means that the January forecast value is equal to the value found on the Annual assumptions sheet in cell I9 (row 9, column I), multiplied by "SeasonalPcntM".  So what is "SeasonalPcntM"? 

TIP:  How to find Internally Defined Variables:  To find out what a named variables does, when found in the Assumptions Designer, select Assumptions / Formula / Defined Variables from the Assumption Designer's Help menu.  This leads you to the Internally Defined Variables page in On-line Help. 

In our case, scroll down that page to item 21. SeasonPcnt.  Here you will find that this variable represents the actual value from the Seasonal Profile for each month, modified by a balance to go factor.  That factor will insure that the Annual forecast will be achieved over the year, even if targets for the early months in the forecast are missed..  (Note that the "M" in "SeasonalPcntM" means the value for the Monthly horizon.  The same variable for the Quarterly horizon is named "SeasonPcntQ", and the one for the Semi-Annual horizon is named "SeasonPcntS".  (Capitalization is not required with these variables, but it does make them a little easier to read.)

What all this boils down to is simply that the "Sales - Other" account is being forecast based upon the Monthly Seasonal Profile in a way that will insure that the full twelve months will equal the original Annual forecast.  Since this is exactly what we wanted, we simply leave it alone and move to the next account.  NOTE:  Do not click the Apply Now button at this point.  The formula is exactly as desired and comes from the Annual horizon.  So we simply leave it alone.

Handling Partial Year Sales                                                                                                       top

Next, select the Sales - Garden Sheds account from the Account(s) to Receive formula... list. Note that the linkage process has produced a similar forecast assumption formula as that for the Sales - Other account.  But here, we wish to indicate to Profit Plan that in fact sales of sheds will not even start until July.  To do so, we need to set the first six months to $0.  Here is how:

1. Using the down-arrow to the right of the To-date, reduce the ending date to 6/30/1997.  This changes the horizon selected to include only Jan through June, rather than the full year.
2. Select $ Amount from the assumption Type list.
3. Click Apply Now.

If you were looking you would have seen the July 1997 forecast jump from 167 to 277!  This is what the "SeasonPcnt" variable is designed to accomplish.  So even if the earlier months are low, the forecast self-adjusts to reach the Annual forecast total.

Now, suppose that we actually expect sales to begin ramping up in June, to say 50% of what would normally be forecast by the seasonal profile, rather than the zero just entered for June.  How do we handle that specific month?

1. Change the Time Horizon Options... drop-down list to Select specific period only.
2. Click the value in the To date field. (This shortcut resets the selected range of dates to the specific date clicked.  Just what we want here.)
3. Select User Defined from the assumptions Type list.
4. Enter 'Annual!I10*ProfilePcntM*0.5' after the "=" sign.  This will multiply the Annual forecast for this account (found in row (10), column (I) on the Annual assumptions sheet) by the seasonal profile percent for the relevant month (June here) and reduce the result by 50% (0.5).
5. Press the [Enter] key and observe the Result (if apply) window now displays 56.49.  This seems reasonable, so let's use it.
6. Click Apply Now to finish the monthly forecast for this account.
7. Finally, select All forecast periods from the Time Horizon Options... list so we are again set to affect the entire forecast and so the assumption Type and Formula will again reflect the first time horizon.  This is what we will generally want as we select each new account.

What you may have noticed now is that the July forecast  for Sales - Garden Sheds has fallen from 277 to 253, to reflect part of the impact of the 56.5 added in June.  As mentioned before, the Monthly link formulas are designed to equitably distribute adjustments made in earlier periods across the remaining months in the forecast.  So in our case, the rest of the months in the year were adjusted down slightly to reflect the fact that the Annual sales forecast can be achieved with lower sales over the balance of the year.

Handling Non-financial Account Types (like... 
Builder Participation and Penetration plus Unit Sales Price and Cost)                   top

Select the Builder Participation - % account in the Account(s) to Receive formula... list.  Notice that the Type of assumption changes to $ Change.  This is the default assumption generated for most balance sheet accounts and for non-financial level accounts.  If we had entered a value for this account in the Dec 1996 column of the Monthly Assumptions and Data Entry sheet, this value would have carried across the months at that same level until adjusted in Dec 1997 to reflect the Annual sheet's year-end value.  Just to make this clear, let's enter the 5% value we heard about from other dealers during 1996, even though we have no actual "historical" data ourselves.  To do so:

1. Spin down the From date to 12/31/1996.
2. Notice that the assumption Type is User defined and the Formula is '=Annual!$H12%+LUS'

Now that formula was unexpected, I'll bet.  So what does all this mean, since "you" certainly didn't directly "define" it?  Well what the formula means is "set this cell equal to the value found on the Annual assumptions sheet in column H, row 12, which is the 1996 year-end value for the Builder Participation - % account.  The end of the formula; i.e.'+LUS', is a special tag with a value of zero that means the formula is the result of a "L"inking fiscal horizons operation requested by you and accomplished by "US" (Profit Plan developers.)  This special tag identifies the formula as one created by US and therefore one we can modify.  Without that US suffix, Profit Plan will assume the formula really was created by you and will therefore leave it alone even if it thinks something else might be more appropriate.  After all, you know your firm far better than US

Since the formula is designed to retrieve the percentage value from the Annual sheet and since we really used  1996 data to derive it anyway, let's simply switch to the Annual Assumptions and Data Entry sheet and enter the percentages there.  When we return to the Monthly sheet, the account will be filled in for us.

1. Click on the Annual tab at the lower right of the Monthly Assumptions and Data Entry Sheet.
2. Scroll the Annual Assumptions and Data Entry Sheet until you can see Builder Participation - % in 1996.
3. Enter .05 for the percentage figure.
4. Click the % button on the toolbar to display the entry as 5%.
5. Enter .2 for the Builder Market Penetration % in 1996 on the next line down, since we need to adjust it also.
6. Click the % button on the toolbar to display the entry as 20%.
7. Move down to the Average Unit Sale Price in 1996 and enter $1,360.  As above, this price will be carried over the 12/96 column on the Monthly Assumptions sheet and spread uniformly there for us.
8. Move down to the Average Unit Cost - Sheds in 1996 and enter $660.  This will provide a starting Monthly value for us in 12/96 and spread across the rest of the monthly columns for us.
9. Click the Monthly tab at the lower right of the Assumptions window to return to the Monthly Assumptions sheet so we can use the results.

Computing Units Sold (by month)                                                                                              top

For inventory control purposes, we will want to estimate how many garden sheds we will be selling on a seasonal basis.  Then we can use this information to control how much and when we buy.

To compute the estimated number of sheds sold, we will simply divide our anticipated sales dollars by average unit price at which we will be selling them.  To proceed: 

1. Click and then right-click Garden Sheds - Units Sold in Jan 1997.
2. Open the Assumption Designer.
3. Delete the Formula showing and replace with "=1000*".
4. Click "10.  Sales - Garden Sheds" in the Account(s) to Use in formula... list.  The formula becomes "=1000*H10".  (The 1000 is used because our model is using sales dollars in 1,000's, rather than actual dollars.)
5. Enter a divide sign ("/") after the "=1000*H10" in the Formula box so it now reads "=1000*H10/"
6. Scroll down the Account(s) to Use in formula... list and click "16.  Average Unit Sale Price".  The Formula will now be "=1000*H10/H16".
7. Press the [Enter] key.  Note that the Result (if apply) window shows 0.0, as would be expected since we will not actually begin delivering sheds until July.
8. Click OK to apply the formula across the entire forecast for this account and close the Assumptions Designer at the same time.

At this point, we have a forecast indicating roughly 55 unit sales in July, 245 in August, 15 in September, etc.  Notice I said "roughly".  The actual values are more like 54.53, 244.58, etc.  We will NOT round these units to whole numbers of garden sheds, however.  True, in June we will actually sell either 54, or 55, or 56 (or some other number of) actual units, but we must always keep in mind that our forecasts can only be averaged estimates, and are not historical accountings.  Do not waste time thinking your forecast will be more accurate is you forecast only in whole units or round everything to the nearest penny.  The extra effort simply won't improve the actual results. 

Monthly Cost of Goods Sold Considerations                                                                              top

By default, when the Link Fiscal Horizons option is utilized to spread an annual strategic plan (forecast) into a monthly budget, Sales and Cost of Sales account monthly values are computed by spreading the value on the Annual Assumptions Sheet into twelve Monthly Assumptions Sheet values using a seasonal profile percentage.  While this is most often precisely what is desired, this default is not always the best solution.

In our Johnson Supply Project, we are assuming we will not actually experience sales of the new garden shed line until July.  So we really do not want the Cost of Sales spread into the months from January through June, as the default linkage has done.  Go how to generate a more appropriate assumptions?  Easy...

1. Click the COGS - Garden Sheds account in Jan 1997.
2. Right-click and open the Assumptions Designer.
3. Clear the Formula window until only the "=" sign is showing.
4. Select (click) "15:  Garden Sheds - Units Sold" in the Account(s) to Use in formula... list.
5. Enter a times sign (asterisk) after the resulting "=H15" to produce "=H15*"
6. Select "17:  Average Unit Cost - Sheds" so the Formula now reads "=H15*H17"
7. Add "/1000" after the "H17 to produce "=H15*H17/1000".  The 1000 is needed to convert the dollars to thousands of dollars, since the entire original model was developed using $1,000 values, rather than simple dollar values..
8. Press the [Enter] key.  Note that the Result (if apply) window now shows 0.0.
9. Click OK, to apply the formula and close the Assumption Designer.

Voila.  Cost of Goods for Garden Sheds now starts in July and runs at $660 per shed sold, expressed in thousands of dollars.  Looking down at the Gross Profit - Garden Sheds, we see the shed profits beginning to appear in June.  (Prior to June, Gross Profit - Total is comprised only of profits from "Other" sales.)  But by July, we can see the new garden shed line beginning to inflate total gross profits quite nicely.

But, enough of this planning project for this issue!

If you'd like to compare your results with ours thus far, download our Johnson Supply Project - phase 6.zip model.

If you want to review past issues of our Profit Plan Tips newsletter, you can do so by clicking this Newsletter Index link.

In our next issue we will review the rest of the Monthly income statement accounts and begin dealing with EOQ considerations on a Monthly basis, using the Garden Sheds - Units Sold values generated above.  Having these values available will allow you to more finely control your inventory purchasing and scheduling activities.

We will also show you how to link custom titles between reports.  And we will begin exploring the Loans module, so you can learn to use all if its many capabilities more effectively. 

If you want to review past issues of our Profit Plan Tips newsletter, you can do so by clicking this Newsletter Index link.

These newsletters are one more way we at Security Development Corporation can say "Thank you!" for being one of our valued Profit Plan® clients!  We hope you find that each issue of our Profit Plan newsletter contains at least one nugget of information that will help you more effectively plan and manage your business processes.  But if you prefer, click the word cancel to unsubscribe.

Copyright 2002 - Security Development Corp.
                             10406 - 40th Avenue SE
                             Everett, WA  98208  USA
                             tel:  (425) 483-0850
                             fax:  (425) 483-0683
                             sdc@sdc-usa.com