===============================================================================
You have received this Profit Plan Newsletter as a licensed
user of Profit Plan® -- Your Vision of Tomorrow®.
===============================================================================
PROFIT PLAN PLANNING TIPS - July, 2002
===============================================================================
July Contents:
Using the Cash Reconciliation Chart
Functional Cosmetics -- "Zooming" Charts
Projects and Economic
Order Quantities - (part 5)
TIP -- How to Review a Strategic Plan
TIP -- Use the Notepad to Remember Planning Ideas
TIP -- Using the "Always On Top" Feature
Add an Accounts Receivable - Garden Sheds planning item
Estimate Accounts Receivable from Ratio Analysis Report
The Easy Part -- Performing the Annual to Monthly Link
Topics Coming in the Next Issue
===================================================================== top
Using the Cash Reconciliation Chart
=====================================================================
With WorldCom, Enron and others in the news, a bit of concern is being expressed over the use of concepts such as EBIDTA (earnings before interest, deprecation, taxes and amortization) and "free cash flow". Today we will show you one of Profit Plan's tools that could have gone a long way towards detecting and avoiding Worldcom's looming cash crunch. It is called the Cash Reconciliation chart and is located on Profit Plan's main Graph menu.
To make sure everyone is on the same page during the following discussion, we have imbedded a scaled version of the Cash Reconciliation chart that you will find in Profit Plan's "Johnson Supply" model in the html version of this newsletter. For those who are receiving the text version of this newsletter, you can view it directly. Open the Johnson Supply model (it should be in your Profit Plan folder). Then select View / Monthly from the main menu. Next, select Graph / Cash Reconciliation from the main menu. Finally double-click the caption of the Graphic Analyzer as a quick way to maximize the Cash Reconciliation chart.
The Cash Reconciliation chart combines four key elements which contrast results of operations performance with anticipated short-term debt needs and cash balances. Using these, one quickly sees potential issues with cash flow and anticipated debt requirements. If WorldCom investors had a copy of this, there would have been little question of about the firm's viability. Capitalizing expenses would have kept the Net Profit line in good shape, but would have immediately shown up in the countervailing balance sheet accounts. The cash outflows being capitalized would have shown up in the short-term borrowing and/or cash lines, even if the net profit looked quite healthy.
The Cash Reconciliation chart uses the left-hand vertical axis for plotting Cash and Net Profits and the right-hand vertical axis for plotting Sales and Short-term Borrowings. These four account lines are derived by summing all account lines of the appropriate account types into each of these summary total accounts. (These account type totals can also be found in the "Summary Totals" financial report on the Standard Reports tab.)
The "Cash" account line is the "Cash and Equivalents - Total" summary total. It includes all checking, savings and money market account types. Essentially, this line represents the total of all highly liquid assets available to the firm at the end of each month.
The "Short-term Borrowing" line includes all notes payable type accounts. All other current payable types, including the current portion of long-term debt, Accounts Payable, accrued expenses, and income tax payable items are excluded. Essentially, then, this line represents only short-term "bank debt". The other account types here are either considered "junior" to short-term bank debt, or are "fixed" and already considered in the Cash account computation for this chart. Thus the "Short-term Borrowing"line focuses primarily on the issue of short-term credit line requirements.
The "Sales" line shows total "Gross Sales" before discounts, allowances, and commissions. This line represents the "best" the firm can do even if there are NO expenses. Thus Sales focuses on the total potential source of operating cash available to the firm.
The Net Profit line is the accountant's concept of income, including effects of depreciation and amortization. Most interestingly in the Johnson model, cash flow is actually positive in November, after net profit has become net loss! (This is not unusual in highly seasonal industries with significant leads and lags in collections and/or payments of obligations.
Take the time to check out Cash Reconciliation for your own firm. For example, compare the Sales vs. Short-term Borrowing lines. Does borrowing precede Sales, as one might expect for many industries who must build inventory, plant seed, etc? If Net Profit moving in a different direction than Cash, is the explainable through investment activity, delays in collections, etc?
If anything looks unusual, in the Cash Reconciliation chart, be sure you can explain it. If you can't, either the model needs some account type review or you haven't fully thought through your plan. Go back to the Assumptions Sheets and take another trip down the chart of accounts looking for relationships.
===================================================================== top
Functional
Cosmetics -- "Zooming" Charts
=====================================================================
A few issues ago, we mentioned that the main View / Zoom menu
provided a dialog for expanding or shrinking the screen presentation of
most reports you may be viewing. Nice for tired eyes.
The Graphic Analyzer also has a "Zoom" option, but the concept is entirely different. Rather than changing the size of the chart, this zoom enhances focus by changing the vertical "scale" of the chart. Essentially, the Zoom (scale) menu option changes the point where the horizontal (time) axis crosses the vertical axis of the chart.
For example, the Johnson Supply Monthly "Quick Ratio" chart is normally drawn with the vertical axis having a range of values from 0 at the bottom to 3.0 at the top. To obtain more vertical detail than this, one can use the Graphic Analyzer's Options / Zoom (scale) menu function. This choice rescales the vertical axis so its values begin with the lowest value in the data and extend to slightly above the highest value found. With the Johnson Supply "Quick Ratio" chart data, this causes the scaling of the vertical axis to begin with a value of 0.69 (the lowest value in the data) and end at 2.6 ( the next value on the scale above the highest actual value in the data.) The result is the same vertical distance now displays a smaller range of data and hence shows more detail within that range.
In summary, the Zoom (scale) simply adjusts a chart's vertical scale so the usual "0" point is replaced with the smallest data in the data. The result is greater detail, due to a more finely drawn vertical axis scale. (Zoom has no effect on a chart that includes both positive and negative data values, since the chart must include 0. In this case, the chart is always drawn to the finest scale available automatically.)
===================================================================== top
Projects and Economic Order Quantities - (part 5)
=====================================================================
Last month we nearly finished up our first cut at adjusting Johnson Supply's strategic plan to include all of the significant impacts of adding a new garden shed retail line. This month we will do a final review of that plan, adjust as necessary, and include some tips to help you analyze and track your own models. And eventually, we will get around to linking the new planning items we have created into the current Monthly 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. Finally, in the June issue, we followed this by considering reorder points and impacts on Accounts Payable.
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 4.zip model and open it up in Profit Plan now. (You will need Profit Plan v2001 Build 1350 or later to load it properly.)
How to Quickly Review a Strategic Plan top
The best way to review the assumptions in a forecast is via the Assumption Designer. To facilitate this, it is easiest to load all accounts at once. To do so,
TIP -- Use the Notepad to Remember Planning Ideas top
For most of us, it is easier to "do" than to document. Still, sometimes it is really worth taking the time to at least jot down the basic idea behind a less than obvious assumption. Frankly, it is not unusual to end up saying "What was I thinking!" six months down the road.
Here is a quick way to help preserve today's great thoughts for posterity:
NOTE: If you are entering Notepad notes while working in the Assumption Designer, you might find that Notepad opens up "behind" the Assumption Designer, making it difficult to read. This is something you can control!
TIP -- Using the "Always On Top" Feature top
By default, the Assumption Designer floats in front of the main portion of Profit Plan so you can scroll the Assumptions Sheet and review reports without losing track of your assumptions. When set to maintain this condition, the Designer is said to be "Always On Top". But sometimes this feature gets in the way of other things you want to do, like working in Notepad or some other application. If so, simply turn this feature off.
To let the Assumption Designer fall behind other forms while you do something else, select the Assumption Designer's Options menu. Then clear the checkmark beside its Always On Top menu item. Later, set the checkmark again to restore the "Always On Top" condition.
The Assumption Designer has its own icon that appears in the Windows system tray at the bottom of the screen while open. Should the Assumption Designer fall behind the main Profit Plan form or another application, simply click this icon to pull the Assumption Designer back to the top again.
Now back to the review discussion we started awhile ago...
Add an "Accounts Receivable - Garden Sheds" Planning Item top
In earlier issues, we concentrated on the Sales, Cost of Sales, Inventory and Accounts Payable impacts of the new garden shed line. And frankly, we don't anticipate much impact of the garden sheds on our basic General and administrative costs. We have enough slack capacity to handle the anticipated growth. So let's begin our final review here by scrolling down the "Accounts to Receive formula..." list to the balance sheet section of the listing.
Arriving at the Cash account, we se that the model is using a "Minimum Cash/Notes Payable" assumption type with a Value (factor) of 0. We know this will automatically adjust the Cash balance up if we are making money, or draw it down towards 0 (the Value set) if we need cash. Fine. Let's move on.
Accounts Receivable as a "$ Change" assumption type with a value of 98. Hmmm. This means that this account is being forecast to increase by a fixed amount of $98(000) dollars in 1997. Now that might be completely reasonable for the non-garden shed sales. But a previously calculated fixed figure like this clearly could not have anticipated the impact of our new garden shed line on our receivables. So we really should plan for that anticipated impact explicitly. What to do?.
Well, we could simply adjust our current $98 increase in Accounts Receivable (A/R) to include the garden shed receivables. But if garden sheds are going to directly impact our receivables, we really should plan for that anticipated impact explicitly! So let's leave the current A/R account alone. It already is forecasting the impact of non-garden shed sales. All we really need to do is add a line for the impact of the garden sheds.
To do so:
Now we are back on the Assumptions Sheet and need to come up with a reasonable estimate for the garden shed receivables.
Estimate Accounts Receivable from Ratio Analysis Report top
The most common way to forecast Accounts Receivable is by estimating the average number of days it takes to collect on sales, rather than looking at A/R as an average percentage of sales. The reason is that we want to lag collections by the average length of time it takes to actually receive the money. Let's see how this works.
Click on Reports / Ratio Analysis, to open the Annual Ratio Analysis report. In the Asset Management Ratios, notice that Johnson Supply's "Collection Period" days range from a low of 23 to a 30 days. And the current (non-garden shed) forecast is set at 25 days.
Now, since we anticipate that our garden shed sales will simply show up as additional line items on our already existing customers' invoices, we could simply forecast the same collection period of 25 days for garden sheds that was used for the non-garden shed items. But right above the Collection Period ratio we notice that the 25 day collection period is equivalent to a receivable turnover of 14.8 times. And looking to the SIC column at the right of this, we see the industry average accounts receivable turns only 9.1 times per year. So, just for a sanity check, let's think about this collections issue a bit more.
Knowing that the Ratio Analysis report displays only the median industry values, lets take a look at the detailed industry data to see if we just how far over the median we are. After all, even the best can not beat the averages forever and we are now dealing with a new sales line.
To check the data, open the Industry Data form via the main Input menu. Scroll down to line 33 and take a look at the RMA "Sales/Receivables" data. Here we find that only 25% of the industry exceeds a turnover rate of 11.5. And on line 35, we see that the lower quartile (bottom 25%) of reporting firms manage to turn over receivables less than 6.7 turns per year.
Now Cancel the Industry Data form and return to the Ratio Analysis report. Here we again see that our turnover has ranged form 12.0 to 15.8. Can we continue to run this much above the industry? Particularly with a new line of products?
Well, perhaps yes, given our current customer base and great long-term relationships. But even so, maybe we want to hedge just a little. Noting that our 1995 turnover was 12, (or effectively a 30 day collection period), let's go with a more conservative 30 day collection time, at least initially. After all, new sales lines often bring unanticipated issues that may tend to slow collections.
So, for now:
OK. Inventory was handled earlier and fixed assets shouldn't be affected unless we grow more than planned for now. So let's reopen the Assumption Designer with the current liabilities selected, since these are the only accounts we have not yet reviewed.
To do so:
Note that the "Notes Payable - Bank" account is forecast to have a $0 change in 1997. This is reasonable if we don't plan to draw on our line of credit with the bank. We can simply say we expect no change, as has been done here. BUT we know that Profit Plan MUST be given a Minimum Cash/Notes Payable assumption type assigned to exactly one of the liability accounts in any model. This allows Profit Plan to automatically borrow from that account if new cash is needed in the forecast. So if "Notes Payable - Bank" (the typical choice) is not to carry this assumption type, let's make sure there is one assigned to one of the other liability accounts.
Scrolling down, we find that the Notes Payable Shareholder account has been assigned this assumption type, as we can see in the Assumption Type dropdown box. But what is this???
The above message will appear saying we can not use the Notes Payable Shareholder account for this account type! Yet there it is already assigned in the model. So how did this possible?
The answer is that our Johnson Supply model actually was created in 1997, just like the dates in it imply. And back then Profit Plan would allow any current liability account to act as the automatic source of short term borrowing. Since this model's scenario intended to have the shareholders, rather than the bank, contribute funds when needed and get paid off if possible, the "Notes Payable Shareholder" account was assigned the Minimum Cash/Notes Payable account. But we were a bit sloppy about the account type assigned to "Notes Payable Shareholders. We should have used an Other Notes Payable account type back then. Now Profit Plan is telling use we really need to use an appropriate account type for that account if we want the account to be used for automatic draws in the forecast.
To update the Johnson Supply model to use a more appropriate account type:
Now reselect the "Notes Payable Shareholder" account. No more messages. We are now using an appropriate account type for the shareholder account. According to the Value (factor) field, the $24(000) shareholder note is to be paid down to $0 from cash flow, it possible. Fine. Just what we want to happen.
Now, scroll back up the list and look at the Accounts Payable (from non-garden shed activities) account. Here we find that it is being forecast with a fixed "$ Change" assumption, much like the Accounts Receivable forecast for non-garden shed items. This might be fine for the specific current level of business activity that has been forecast for the non- garden shed sales line. But one should beware of using such a fixed valued assumption with an account that really SHOULD VARY with business activity.
The problem with doing something like forecasting a specific $24(000) increase in Accounts Payable, as in this model, is that the account will not react to changes in business volume. No matter what sales level is forecast, the SAME Accounts Payable will be projected. So even if the value is correct in the current projection, it will be wrong under any other sales scenarios.
The same inflexibility issue applies to the Accrued Expense and Taxes Payable forecast assumptions in this model also. These forecasted levels also will not change if we change business activity levels (like sales) in our forecast. But enough for now. We will leave these as forecast. Here we are really simply adding new planning items to the original plan. Since we don't see these affecting our prior forecasts for Accrued Expense or for Taxes Payable in 1997, we will leave the original values alone.
OK. Feeling fairly certain our new strategic plan reasonably represents the future for now, let's close the Assumption Designer. It is time to begin feeding the impact of our new garden shed line into our original monthly budget.
As we added planning items in the Annual Assumptions Sheet, matching account lines were automatically inserted into the Monthly budget for us. Their data values were all initialized to $0 for history and default formulas were inserted for the forecast periods which also generate $0 values initially. With all these zeros, our work thus far has had no real financial impact on our original short-term Monthly plans. It is now time to migrate our new strategic planning results into our original budget, as seen in the Monthly Assumptions and Data Entry sheet.
To begin revising the budget, we start with a "partial financial link"...
The Easy Part -- Performing the Annual to Monthly Link. top
A link between horizons is a somewhat intensive exercise and can produce unanticipated results. So before you link, it is always good practice to save your work. Let's pause and SAVE THE MODEL under the name "Johnson Supply Project - phase 5.fmp".
Now that we have the model safely tucked away on the hard drive, we next feed the new long-range plan into the Monthly horizon. To do so, we will proceed as follows:
After the linking process finished, it is time to take a look at our new results in the Monthly Assumptions Sheet. Yes, we now see the Garden Shed sales spread across the year following Johnson Supply's historical seasonal sales profile as expected. Essentially, the link has inserted formulas for us that spread the Annual plan values into the associated monthly periods using a Seasonal Profile. (See the Input / Seasonal Profile menu option to review Johnson Supply's profile if you wish.)
With this starter work done for us via the Link process, it is now time to concern ourselves with fine tuning the results. We will ant to think about how this first year will phase in. When do we actually begin selling, when do we stock, how will we handle short-term inventory management issues, order points, etc.
But, enough for now! Next month we will review the results and see what opportunities we can find for fine tuning!
If you'd like to compare your results with ours, download our Johnson Supply Project - phase 5.zip model.
===================================================================== top
Browsing Old Newsletters
=====================================================================
If you want to review past issues of our Profit Plan Tips newsletter, you can do so by clicking this Newsletter Index link.
===================================================================== top
Topics in the Next Issue
=====================================================================
In our next issue we will review the results of our linkage and begin fine tuning. In some cases, we will simply wish to override the default linkage results. In others, we will want to adjust for timing that we simply know will occur. In the end, we will have a Monthly Budget that will project our near term cash flows in a manner consistent with our strategic planning and with knowledge of short-term factors and constraints.
====================================================================
top
How to Opt-Out
=====================================================================
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