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

              PROFIT PLAN PLANNING TIPS - September, 2002

September Contents:

      Planning Tips Indexed

On-Site Consulting

Building and Maintaining Loans
Understanding the Four Loan Views
Base Loan Input View
Customizing the Initial Schedule
Adjusting the Actual Schedule  
The Schedule Variance Report
Linking Loans to Chart of Accounts

      Functional Cosmetics -- Linking Custom Titles

      Sample Project and EOQ - (part 7)
              General and Administrative Expenses
              Inventory Control - Safety Stock
              Inventory Control - Lead Time Demand
              Inventory Control - Units On Hand
              Inventory Control - Units on Order
              Inventory Control - Inventory Value

      Topics Coming in the Next Issue

In this age of information, it is hard to find the time to read everything we have to, let alone stuff that tends to be less than exciting.  So most of us time manage by skimming a table of contents and filing the topic away in the back of our minds until such time as it becomes important to think about and understand.

The only difficulty with this approach it that it may not be easy to remember where the info was initially seen!  For instance, you may know that the serialized sample model we have been building for several issues included EOQ (economic order quantity) consideration, but have no clue when or where.  So to help, we have added a newsletter index applet at our http://www.profitplan.net/Newsletters web site.

At the top of each newsletter page you will find a small book icon. It opens and closes the left-hand frame that contains the Contents, Index, and Search tabs. Clicking the book icon when the Index is exposed will hide the frame and close the book icon. When the frame is hidden, clicking the closed book will reopen the frame and the book icon.

Now you can browse the master Contents and/or Index to find the topic you want more easily, when you need it.  

Managing your business is critical to your success. Modeling that business is a great way to improve that management. But in many ways modeling your business will be one of the toughest challenges you will ever face. Why? Because it forces you to think through just what drives your operation and what your operations drive. Often, you find yourself asking "Why are we doing that? And why that way? What are the alternatives and how do we figure out what the cost or benefit is from doing any of them?"

Building a plan for your business requires both vision and expertise... The vision to see what is possible and the expertise to plan and build that future.  

All of you have the vision, or you wouldn't be reading this.  But building a plan to implement that view may require expertise you need to acquire.  After all, haven't all of us felt that we could move mountains if only someone would move the foothills out of the way!

We can help provide seasoned management perspective and the in-depth technical expertise needed to translate your goals into concrete steps and financial requirements.  And often you really need only a day of training to grasp the nuances required to convert concepts into bankable plans.

If in need of assistance and truly serious about moving your firm to the next level, call (425) 483-0850 and let's explore the possibilities.

To help you understand how to use the Profit Plan Loans module, today we are going to show how the information flows into and from the various loan Views, and from there to the related accounts on the Assumptions Sheets.  Then we will show you how to handle the type of loan payment data that many of you see in your line of credit's monthly loan statement.

To help you visualize the process, we have included some clippings from the Alpha Plus sample model shipped on the Profit Plan CD.

Loan Views

The Loan Module includes four different views for each loan.  Any of these can be selected from the Views drop-down list at the upper right of the Loans module.

Each view builds upon the prior.

Base Loan Input View

Here one enters the basic input required to create a standard loan schedule of the type selected.  The input varies by type of loan to be generated, but includes the basics needed to create a standard loan without the special conditions that might be experienced with initially discounted rates or deferred payment schedules.  

Using this basic input data, Profit Plan renders an Initial Schedule that can next be modified to meet special conditions.  You enter these special conditions directly in the Initial Input View.

The Refresh button in the Base Loan Input  sheet can be used to remove all of the special conditions you may have entered on the Initial View screen, should the conditions change or you wish to start fresh again.

Initial Schedule

The Initial Schedule begins as a standard amortization schedule, based upon the terms provided in the Base Loan Input.  

For example, in the Alpha Plus sample model, we initially set up the Base Loan Input for a Bank of America Line of Credit to include a $70,000 per month automatic draw.  (Frankly, we initially didn't know exactly how much would be needed, and didn't really care.  We expected to modify the resulting Initial Schedule immediately, anyway.)

Once the resulting Initial Schedule was available, we then began modifying the draw schedule by looking at the forecasted borrowing requirements on our Monthly Assumptions and Data Entry Sheet.  If funds were needed, we borrowed them plus a little extra.  After all, we are dealing with a forecast.  It is always better to leave a little pad, just in case.

As designed, any field (column value) can be entered at will, except the beginning and ending balances and the computed interest.  These are calculated automatically.  However even these fields can be overwritten by direct input if you really need to do so.   For example, the plan may call for all interest to be forgiven during the month in which the draw occurs, but to resume on a daily interest basis with the 1st of the following month.

In special situations such as above, the normal interest calculation must be replaced with direct input.  To accomplish this, you will need to unlock the interest field for the period(s) in question.  Select them.  Then select Options / Set Protection / Cell Protection from the main menu.  Clear the Locked check box.  Then enter the payment, interest, and principle fields for the period in question, being careful to insure that the interest plus principle entries total the full payment amount entered.

Actual Schedule

The Actual Schedule is created right along with the Initial Schedule. This Actual Schedule is intended to be updated over time as the payments occur.  If all goes according to plan, the actual view will continue to mirror the forecasted Initial Schedule. However, in reality, if nothing else, the dates upon which the loan payments are credited by the bank will tend to vary with resulting minor fluctuations in principal.

It is the Ending Balance column of this report that is used to compute the current portion of long-term debt (CPLTD) and the long term debt (LTD) amounts that appear on the Assumptions Sheets, if you choose to link the loan schedule to them.  Thus, maintaining this schedule in the Loan module also automatically updates the associated accounts on the Assumptions Sheets.

When entering bank statement information after the fact, begin by entering the payment date and total payment amount.  The Actual Schedule should then recompute and reproduce the interest and principle amounts on the lender's statement within pennies, if this is a standard amortizing loan.  However, lines of credit may show a more substantial variance.

When entering payments and draws against a line of credit, you may need to enter both the interest and principal amounts, as well as the payment date and payment total.  This will be particularly true if you make several draws during a month.  The reason is simple.  Within Profit Plan, only one schedule row is allocated for each month.  So there is no way to accurately enter multiple payments within a month.  Rather, you enter the total draws during the month and totals of the payments. Profit Plan then nets the draws against the principal payments, but does not charge interest on the new money drawn until the beginning of the next payment cycle.  

To handle a situation in which interest accrues from the date of draw, unlock the interest payment cell for the period in question.  Then enter the interest amount directly; making sure that interest, principal and total payment amounts agree appropriately.

If you are setting up a new loan schedule and need to handle special interest situations, make the adjustments on the Initial Schedule.  Later, as you track the loan's performance, make such adjustments on the Actual Schedule.  This will allow you to see the impact of these differences in the Schedule Variance report.

Schedule Variance

After the budget is finalized and time progresses, this view can be used to actually track the loan against the monthly bank statements.  Here, the payment data from the Actual Schedule is compared with that on the Initial Schedule.  Any variances between these can then be easily reviewed and reconciled.

Glance at this report to see the cumulative impact early and/or late payment dates and varying payment amounts are having on the loan.  This report has been know to highlight some unexpected charges and fees that were buried in the fine print and never noticed in reality.

Linking the Loan to Specific Accounts

Up to 255 different loan schedules can be created in this module.  None affect the actual financial plan until linked to specific accounts on the Chart of Accounts.  

To the left of the View Selection List are three drop-down lists for selecting accounts.  Each list includes the names of all accounts in the Chart of Accounts with the appropriate account type.  For example, all the interest accounts will appear in the Interest window.  To assign a loan to associated accounts, simply choose the appropriate account(s) from the drop-down lists.  In the example above, all debt is assigned to the account called "Total Long Term Debt", within the particular model shown here.  This particular chart of accounts did not include a "current portion of long-term debt account, and so the "None" was selected.  And, finally, the interest generated in the Actual Schedule is being fed to the account with the name "Interest Expense" in this chart.

In all cases, the account name itself has no particular significance to Profit Plan, other than to identify the particular row in the chart of accounts that is to receive the loan schedule output.  To disassociate the loan with an account, simply assign that portion of the schedule to "None" on the drop-down list.

Last month we showed you how to annotate your model by creating a custom title on the Annual Assumptions Sheet.  The process was relatively painless... Unfreeze the title area, unlock the cell to receive the new title, and enter it as simple text or a formula.  

The picture below shows another example of how a custom title might be created.  Here we went one step further than last month by using a special Dollar formatting command to display the 250000 of additional equity found in cell H134 as $250,000.

The Dollar(cell reference,d) function we used above converts whatever number is found in the referenced cell into the standard format of the local currency.  The "d" after the comma in the function represents the number of digits to follow the decimal separator.  (We used 0 here, to show the result in whole dollars.)

Now this is nice, but how do you make this title show up in other reports?  Easy.  All you need to do is reference this title wherever you want the title to appear.  Using the title above as an example, we know that the title is in J4.  (Yes it looks like it starts to the left and continues to the right, but this is because we used the Format / Alignment / Center across cells, from  the main menu to cause it to center itself about the cell J4.)

The only tricky item about referencing a cell from some other workbook in one of the report's title areas it that you need to include both the name of the workbook and the name of the sheet, as well as the cell reference.  For example, to cause the above title to appear in cell J4 of the Income Statement report...

1. Unfreeze the Income Statement report title area using Options / Freeze Titles from the main menu, assuming the area was frozen.
2. Unprotect the sheet (with [Ctrl][L], or from the Options menu) so you can edit the cell (J4 in this case) where you want the titles to appear.
3. Enter the formula =[ASGrid]Annual!$J$4 in the cell J4 in the Income Statement title area..   If  the resulting display does not appear centered, try moving the formula to the next cell or use the Format / Alignment menu to move the title left, center or right.  Also, if the title appears truncated, make sure the adjacent cell to the right is clear.
4. Relock the sheet ([Ctrl][L] again.
5. Refreeze the report title area.  When asked, select Yes, to restore the original frozen area.

To recap, you can redisplay the contents of any cell by referencing the [Workbook] followed by the SheetName, then an exclamation point (!) and finally a ColumnLetter RowNumber cell reference.  Insert a $ in front of the row and column if you want to be able to move the reference from cell to cell in the receiving sheet without the formula itself also automatically adjusting to a new row or column.

This technique can be used to redisplay the content of almost any cell, not just titles.  (See Profit Plan Help for the names of the other workbooks that contain the various components of a Profit Plan model.)

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.  In the July issue, we completed our initial strategic plan and linked it to the Monthly Assumptions sheet. And last month we began work on the resulting monthly budget in earnest.

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 Supply Project - phase 6.zip model and open it up in Profit Plan now. (You will need Profit Plan v2001 Build 1350 or later to load it properly.)

General and Administrative Expenses     top

Rather than spend much time reviewing the General and Administrative costs, today we will simply note that the Annual-to-Monthly horizon linkage has already assumed that all of the Monthly General and Administrative expenses will be incurred uniformly throughout the year.  While this may not be the case for one or more of the administrative accounts, this same assumption was assumed before the garden shed project was ever added to our financial model.  Based on that, we will simply let this assumption stand for the moment and pass on to near-term inventory control.  Indeed, we can not finish the Net Profit projections adequately anyway until we can assure ourselves that we will not be incurring short-term debt. If we do, this will feed back to the Income Statement in the form of increased interest expenses and potential loan fees.

Inventory Control - Safety Stock      top

Early in our annual planning, we concluded that we would like to maintain a safety stock level of 10.5 days of cost of goods sold.  Since the cost of stock out is rather difficult to price in reality, it was simply felt that if we always had at least two weeks more of stock on hand than we expected to sell in any given week, we were reasonably protected from stock outs and resulting customer flight through an inability to meet customer needs.

With this in mind, we can plan our "Garden Sheds on Hand (units)" account to include an extra inventory balance at the end of any month equivalent to 10.5 days of the next month's demand.

Inventory Control - Lead Time Demand  top

With the safety stock cushion on hand, we still need to order enough to satisfy expected demand between reorder points.  In our case, the garden shed orders require 45 days between order placement and receipt of the order.  We also learned back in the May issue that the economics of ordering and holding these garden sheds produced an average EOQ (economic order quantity) of 23 units per order; effectively two weeks of average demand.  Given this, we concluded that a manageable inventory control methodology would be to review and order stock every two weeks for demand six weeks out.  After eight weeks of such orders, we would have enough in the order pipe line to meet demand comfortably on a continuous basis, particularly with two weeks of safety stock on hand at all times.

So let's put these concepts into our Monthly Budget.  We begin by developing our unit forecasts, which are much easier for most to conceptualize than gross dollar totals.  Once our units are correct, it is an easy matter to convert these to dollars.

Inventory Control - Units On Hand          top

We want our units on hand, on the average, to equal 1/2 the demand between orders, plus our safety stock.  Handling this in the Assumption Designer is easy...

1. Click the Inventory - Garden Sheds On Hand (units) account in Jan 1997.
2. Right-click and open the Assumption Designer.
3. Change the Assumption Type to Exact Days of Account
4. Select (click) the 15:  Garden Sheds - Units Sold account in the Account(s) to Use in formula... list.
5. Enter 24.5, which is the total of 10.5 days for safety stock + 14 days of demand.
6. Click Apply Now to store the resulting formula across the months and close the Assumption Designer.  The resulting formula is almost what we want, but not quite.  The Exact Days assumption type normally uses the current month as the base month, when computing how much is required to be the equivalent of X days.  But in our case, what we really want is that at the end of any month, our inventory balance be equal to 24.5 days of the following month.  With this in mind, we will adjust the dollars associated with these units to reflect that the purchases will actually occur in the prior month.

Inventory Control - Units on Order        Top

1. Click the Inventory - Garden Sheds On Order (units) in Jan 1997
2. Right-click and open the Assumptions Designer.
3. Change the Assumption Type to User Defined.
4. Select (click) 15:  Garden Sheds - Units Sold in the Account(s) to Use in formula... list.  This enters =H15 into the Formula box.
5. Change the H to I so the formula refers to February's unit demand, rather than January's demand.  (If you are not sure how a column letter relates to the column data, check the gray letter to the left of the From Date and To Date labels to get an idea.  For instance, you will see that H refers to January, 1997 in this model.)
6. Add +J15/2 so the formula now reads =I15+J15/2 This means that at the end of January, we will have all of February's demand on order and one-half of March's demand. Ignoring unimportant variations in the lengths of the months, this will give us an average of 45 days of demand on order at the end of any given month.  (The variations in lengths of the months can be ignored because of our safety stock cushion.)
7. Click Apply Now to enter the base formula across the budget.
8. Click the Time Horizon Options... list, set it to Select specific period only and set the From Date and To Date fields to 4/30/1997.
9. Now add another two weeks of June demand to the April order so that we will have the desired two weeks of safety stock on order by the end of April, as well at the rest of the June demand.  The Formula box should now contain =L15+M15/2 + M15/2, or more simply, = L15 + M15
10. Click Apply Now and advance the dates to May.
11. Add the yet-to-arrive safety stock into the outstanding order for May also.  The Formula box should now read =M15+N15
12. Click Apply Now.  The safety stock will arrive in June and become On Hand from then on.
13. Now advance the dates forward to November.  We need to handle the fact that our future-looking formulas are running out of Monthly data.  The Monthly budget terminates in December, but we need to be buying in mid-November for January of the following year. By mid-December we also need to have placed orders for stock to be sold in early February.  To handle this, we will forecast upcoming January and February numbers on the fly, using the Seasonal Profile.
14. Slide the Assumption Designer out of the way and open the Seasonal Profile.  Note there that both January and February sales volumes are expected to run at 3% of the yearly total. Also note that both November and December volumes run at 4% of annual volume.  Thus January volume is 3%/4%=75% of either November's or December's volume. The same is true for February. Using this, we can estimate the not-yet-forecasted January or February values as simply equal to 75% of the already forecasted December value. Thus we can say, for example, that T15 (Jan 1998 garden shed unit demand) should be equal to 0.75*S15 (75% of Dec 1997 unit demand).
15. Cancel the Seasonal Profile and slide the Assumptions Designer back into position (or bring it back to the foreground by clicking it in the Windows System Tray at the bottom of the screen.)
16. Replace the T15 in the Formula box with S15*0.75, to produce =S15+0.75*S15/2. Or, more simply, replace the formula with 1.375*S15
17. Click Apply Now to apply the result to Nov, 1997.
18. Advance the dates to December.  Here we need to have all of January and one/half of February on order.  This is equal to 0.75 * S15 + 0.75*S15/2, or more simply =1.125*S15, so change the Formula box to read =1.125*S15  Then click OK.  We are finished with our on-order unit estimate.

Inventory Control - Inventory Value    Top

As mentioned a bit earlier, we are stocking for future demand always.  At the end of any month, the inventory on hand should equal the amount expected to be used in the following 14 days, plus 10.5 days worth of safety stock.  We calculated those units earlier.  The results are sitting in the Inventory - Garden Sheds on Hand (units) account. Now we just need to apply dollar values to these units and recognize that we must have them on hand by the end of each prior month.

1. Right-click the Inventory - Garden Sheds On Hand ($) account in Jan 1997 and open the Assumption Designer.
2. Select User Defined as the Assumption Type.
3. In the Account(s) to use in formula... window, click 67:  Garden Sheds on Hand (units).
4. Add an "*" in the Formula box and then click 17:  Average Unit Cost - Sheds
5. Edit the results in the Formula box to read =I67*H17.  (We are paying in January dollars for inventory we need to have on hand in February.)
6. Click Apply Now, to insert the formula across the forecast.
7. Choose Select specific period only from the Time Horizon Options window and then click the date in the To Date field.  This will take us straight to December, 1997.  There we need to handle the fact that, at end of December, we need to have two weeks of safety stock, plus two weeks of early January demand on hand.
8. Since our valuation formula has been looking at the demand in the following period, and we have no January 1998 column in the forecast, we need to estimate.  Again we will use the seasonal profile results (as above) and replace the T67 with what we know... We need 24.5 days of January demand on hand, and January runs at 75% of December.  So let's replace T67 with S15*0.75 * 24.5/31.  This gives us January demand at 75% of December and adjusts for the number of days of January demand that we need to have on hand by year-end.  Enter =S15*0.75 into the Formula box.
9. Click OK.  We are finished with the Inventory on Hand.

At this point, the inventory is correctly valued, assuming that the vendor ships inventory on purchase order and only invoices upon receipt. In that situation, the Inventory on Order simply doesn't show financially. And this is completely OK, if the vendor will alway accept returned inventory refused at the loading dock.  So for now, we are going to leave the value of the Inventory on Order ($) at $0.

We will explore more about how our inventory control policy affects our cash flow position next month.

If you wish to check your work to-date against ours, feel free to download our Johnson Supply Project - phase 7.zip file at your leisure.

Next issue we will finish up with our examination of inventory control policy on monthly cash flow, including its impact on Accounts Payable.

We will also explore the world of custom loans further, now that you have a basic grasp of how the Loan module feeds information from schedule to schedule and from the Actual Schedule on to the Assumptions Sheets.

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 better plan and manage your business processes.  But, if you prefer, simply click the word Cancel to unsubscribe from this newsletter.

Copyright 2002 -- Security Development Corp.
10406 - 40th Avenue SE
Everett, WA  98208  USA
tel:  (425) 483-0850
fax: (425) 483-0683
email:  newsletter@profitplan.net