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

              PROFIT PLAN PLANNING TIPS - October, 2002

October Contents:

Presentation Charts and Graphs

Variance Reports YTD

Entering New Monthly LOC Payments

Functional Cosmetics -- Make Chart more Readable

Sample Project and EOQ - (part 8)

Topics Coming in the Next Issue

Did you know you can save your charts and graphs to disk and use them for presentations developed in Word, Word Perfect, PowerPoint and other presentation tools. It's easy, and we'll show you how.

For example, suppose we wanted to include the built-in Profit Plan Cash Reconciliation graph from your firm's mode into a presentation we are preparing in MS Word or PowerPoint.  Since these are Microsoft products, we will want to export the graph using a "WMF" (Windows MetaFile Format).  This special Microsoft-devised format essentially stores graphs and charts as a set of individual picture fragments that can be edited and reorganized within Microsoft products, such as MS Word.  

The image to the right was taken from a document page in MS Word, after importing the graph as a "*.bmp" (bit mapped picture) file.  

Note that the picture retains the appearance of the original chart, but can be overlaid with arrows, balloons, etc.

If a "*.WMF" format is used during export, even more can be done after import.  For instance, the background of the chart will be transparent and can be colored to match the document background.  The numbers on the chart can even be directly edited and changed!  (To do so, use MS Word's "ungroup" function on the picture.  Then individual elements can be deleted, moved, or edited.)

NOTE:  PowerPoint presentations do not handle *.bmp files nearly as well as *.wmf files.  Do yourself a favor and use the *.wmf file format when PowerPoint is to be the ultimate destination.

To export the Cash Reconciliation Graph, or any other chart or graph you choose to create in Profit Plan, use the Export option on the Graphic Analyzer's File menu.  Then set Files of Type to *.wmf, if the graph will be imported into a Microsoft product, or *.bmp if you are not sure that the *.wmf file format will be acceptable in your presentation tool.  (The bmp  file format is almost universally acceptable, but is generally not editable, once exported.)

 The *.vtc file format shown in the adjacent screen fragment is a compact format that is generally usable only within the Graphic Analyzer itself.  Use this format if you simply wish to save a chart for reference later without the need to reselect the accounts and time periods that were included in it.

IMPORTANT NOTE:  The Graphic Analyzer has the ability to stay "Always on top" of everything else on the screen.  This is handy, when you want to be able to scroll about on the underlying reports without losing sight of the chart itself.  But it is possible to end up with the Save As dialog box completely concealed behind the "Always on top" chart while you are actually trying to export (save) the chart.  In that condition, the Save As dialog box will end up waiting for your input but you might not even be able to see it.  So, if you can't see the dialog box, press the [Esc] key.  That will close the dialog and get you going again.  Then, select Options from the File menu and clear the Always on Top  check mark.  Now the Save As dialog will be able to stay on top of the chart long enough for you to finish the export!

Many of you use the Monthly Income Statement and Balance Sheet Variance Reports to monitor and manage your operations from month to month. But over the year, these get to be quite large. Today we'll show you a way to link the Annual Variance Reports to the Monthly Variance Reports so there is no need to enter new historical data into the Annual view.

Profit Plan does not currently automatically link the Annual Variance Reports to the associated Monthly Variance reports.  While this is planned for Profit Plan v2003, currently you can accomplish this manually with not too much effort.  So, if you are using the Monthly Variance Reports and want to accumulate the Monthly historical data from the Monthly horizon into the Annual, the following steps will set up the Annual report to compute and display the Annual YTD totals for you. (You can use a similar technique to produce Quarterly YTD Variance Reports if you create a Quarterly View and sum the appropriate Monthly Variance historical columns into the Quarterly historical periods also.)

Here we will show how to set up the Annual Income Statement Variance Report.  The other reports would follow very similar logic.  We will be using the Alpha Plus Inc. sample model here, for an example.  You can find this model in your C:\Profplan folder.

To sum the historical data in the Monthly Income Statement Variance Report into Annual, proceed as follows:

1. From the Reports menu, select Variance Reports / Income Statement.

2. Press [Ctrl][G], to expose the report grid (the row and column headers at least).

3. Note the column letter above the last period in the current forecast year.  For Alpha Plus, which has more than a year of Monthly forecast displayed, the first forecast fiscal year closes in 12/1997.  That corresponds to column X.  Also note that the first historical period is always found in column D, and the historical data column occurs every fourth column.  So the second historical data column is column H, the third is column L, etc.  
   
   Since the Alpha Plus model includes all of the 1998 forecast year in the Monthly horizon, we might as well set up the Annual reports to capture the second year's monthly data also.  So we also jot down that 1998 historical data will eventually begin in column AB and will end in column BT.

4. We can now hide the grid headers again, since we will no longer need them. Press [Ctrl][G].

5. Select Annual from the View / Select Horizon menu to switch to the Annual View.

6. From the Reports menu, select Variance Reports / Income Statement again.

7. In first forecast year's Actual column (D), select (left-click) the first account line.  Then expose the row and column headers by pressing [Ctrl][G], so we can see the row number of this cell at the left of the report.  (In the Alpha Plus model, the first account row is row 11 in this report.

8. Press [Ctrl][L] to unlock the report.  An open padlock appears in the lower right of the Status Panel

9. Now press the [F2] key twice, to open the Cell Text edit window for this cell.  Here we will enter a formula to add the Monthly data for each period of the forecast year for this account.  We will use this edit window to save typing, as the references to the monthly data are somewhat long...

10. In the Cell Text window, enter "=[IncStmt3]Income Stmt Variance!D11", without the quote marks.  This translates to a command to set the current cell equal to the value found in the workbook "[IncStmt3], on the worksheet named Income Stmt Variance, in the cell defined by the intersection of column D and row 11.
    
    Note 1:  The Standard Reports  main tab window in each View horizon, contains exactly one workbook with all reports for that horizon.  The name of the standard reports workbook in all horizons begins with "IncStmt".  It is then appended with a number indicating the specific horizon.  Monthly=3, Quarterly=4, Semiannual=5, and Annual=6.  So the phrase "[IncStmt3] refers to the Monthly Standard Reports workbook.
    
    Note 2:   Each report (sheet) in the standard reports workbook can be referred to using the name of the report, as shown in the Reports tab at the bottom of the workbook.  So "Income Stmt Variance!" refers to the Income Statement Variance report in the workbook.  The "!" sign simply indicates the end of the report name.
    
    Note 3:  To verify that the reference was entered correctly, simply click OK in the Cell Text dialog and press enter.  If entered correctly, the value for the first historical column in the Monthly Income Statement Variance report should now appear.  If not, your typing was incorrect.  In either case, use [F2] to reopen the Cell Text dialog, so we can finish referencing the rest of the historical periods in the fiscal year.

11. To finish the formula, we need to repeat the reference for each monthly period in the fiscal year.  In the case of Alpha Plus, this means we need to refer to every fourth column up to and including column X, according to the notes we made in step 3 above.  To do this, most easily, proceed as follows:

• Enter a "+" after D11.  Then highlight from the "[" to the "+" and press [Ctrl][C] to copy this to the Windows Clipboard.

• Press the [End] key to move to the right end of the formula.  Press [Ctrl][V] to paste the "[IncStmt3]Income Stmt Variance!D11+" phrase to the end of the formula.

• Highlight the "D" in "D11" and replace it with "H", the column letter for the next Actual data period.

• Press the [End] key and repeat the paste and edit sequence once for each remaining historical period.  When finished, remove the final trailing "+" sign.  For Alpha Plus, this process yields:
  
  =[IncStmt3]Income Stmt Variance!D11+[IncStmt3]Income Stmt Variance!H11+[IncStmt3]Income Stmt Variance!L11+[IncStmt3]Income Stmt Variance!P11+[IncStmt3]Income Stmt Variance!T11+[IncStmt3]Income Stmt Variance!X11

12. When finished with the edit, click the OK button on the Cell Text edit window to apply the formula.  The first sales account in the Annual Income Statement Variance report will now automatically include each month's Actual historical data as soon as it is entered in the Monthly Income Statement Variance report.

13. Now we need to apply this same formula to the rest of the monthly income accounts in this report; i.e. those that need to be summed to produce a year-to-date total  To do this is easy if we take advantage of two facts.

• When a formula is copied from row to row, the row numbers in the formula are automatically adjusted.  Thus pasting a formula from row 11 into the same column in row 12 produces the same formula with each row number increased by 1.  So simply coping the formula we just created in row 11 into row 12 will produce exactly the formula we need for that row.  In fact we can copy that same formula to any row in this report with no need to edit it again.

• We can paste a single formula into multiple rows simply by highlighting the receiving cells just before the paste.  The paste automatically adjusts the formula and enters it in each selected cell.

14. To quickly fill in the rest of the Annual Actual column, first copy our new formula in cell D11 to the clipboard by selecting the cell and pressing [Ctrl][C].  Now repeat the following sequence to fill in the rest of the report.  The specific cells below are from the Alpha Plus model.  Yours will differ by row number.

• Select D12 through D23. (Highlight by dragging cursor down the column.)   Then press [Ctrl][V].

• Select D25 through D32. (Notice we skip the total in D33, since it will add the data anyway.)  Press [Ctrl][V].

• Select D35 through D48.  Press [Ctrl][V].

• Select D51 through D57.   Press [Ctrl][V].

• Select D65 through D94.  Press [Ctrl][V].

• Select D99 through D100.  Press [Ctrl][V].

• Select D104.  Press [Ctrl][V].

• Select D108.  Press [Ctrl][V].

• Select D110 through D111.  Press [Ctrl][V].

15. IMPORTANT  CAVEAT -- This technique of summing Monthly data to produce equivalent Annual totals is valid only for income statement data.  Remember that a Monthly balance sheet account shows the balance on hand at the end of the fiscal period, not the total generated during the financial period.
    
    Next month we will consider how to make the Annual Balance Sheet Variance report reflect data entered in the Monthly Balance Sheet Variance report without the need to enter the data twice.

Credit line statements tend to be convoluted.  Figuring out the calculations used for interest, principal and miscellaneous charges can be a bit mysterious. Sometimes even figuring out such basic questions as "how many interest days are there in a year?" can be less than simple. So this month we show you how to handle a couple of scenarios that might come up when entering historical payment information in the Loan module's Actual Schedule during the budget year.

Scenario 1 -- Routine Monthly Loan Fees.

If your monthly payment includes a routine fixed loan processing fee, deducting the fee from the Monthly Payment amount during loan setup in the Base Loan Input sheet will produce the correct amortization schedule for the loan.  And typically the fee itself should be included in a General & Administrative account in the forecast as an overhead expense, rather than as interest.  But what if you are allowed to claim the fee as interest and want to include it as part of the interest payment in the Loan module's Actual Schedule view?

If you have included the routine fee as part of the Monthly Payment during Base Loan Input, the schedule will be amortizing too quickly.  Profit Plan first computes each month's interest accrual, based on the current balance and interest rate.  Then it deducts this interest from the monthly payment and applies there rest of the payment to the principle balance.  If you have included the fee in the payment, and want to treat it as interest, effectively this is equivalent to saying that the actual interest rate is higher than the nominal rate (which may be true.)  But if you have used the nominal (stated) rate, the interest computed will be too low and the principal amortization will be to high by the amount of the fee.  

To correct the balance in the Actual Schedule,  you can use two different techniques.  One is to increase the interest Rate (annual) value on the Actual Monthly Loan Amortization Schedule  until the Ending Balance is as shown on the bank statement.  This will show you what the real interest rate is and also adjust your interest  payment in the Assumption Sheets to more closely reflect the real, rather than the nominal interest rate.  (When entering an interest rate in the Rate (annual) column, be sure to enter it as a decimal value. A rate of 9.75% is entered as 0.0975.)

Scenario 2 -- Multiple LOC Transactions within Payment Period

A line of credit (LOC) may reflect several transactions during the month, with interest and principle calculations reflecting the resulting average daily balances.  As Profit Plan allows only one entry per month, you may need to enter the bank's results from the monthly statement in this situation, as the Profit Plan calculation will be only an approximation and its standard interest calculation must be overridden.

If it will be necessary to enter a new Interest Payment amount, you will need to unlock the Actual Monthly Loan Amortization Schedule first.  To so do, press [Ctrl][L] or use Options / Set Protection / Protect Sheet from the main menu.  Once the open padlock appears, you can then enter each element of the month's transaction as necessary.  Include the:

Actual Payment Date  - This will redistribute interest and principal based on number of days between payments.
Rate (annual)  - This will redistribute interest and principle based on the Actual Payment Date.  If the rate changed at another time during the month, the resulting interest charge will be close but different from that scheduled.
Monthly Payment  -  This will be distributed as soon as entered.
Monthly Borrowing  - This would be total borrowed since the last payment entry.  

Once both this amount and the Monthly Payment have been entered, compare the Ending Balance with that on the bank statement.  If different also include:

Interest Payment - Enter directly from the bank statement.  If significantly different than computed, a discussion with your bank may be justified.
Principal Payment - Once the Interest Payment is entered, this will be computed from the Monthly Payment - Monthly Borrowing - Interest Payment.  If different from that shown on the statement, loan fees may have been charged, such as a "cash advance" fee.  Decide where this should appear (interest vs general and administrative loan fees) and enter accordingly.  If necessary, enter the Principle Payment amount if different from that calculated.

Whatever transpires, be sure that the Ending Balance ultimately agrees with the bank statement and you are satisfied that you understand any fees or charges that have unexpectedly appeared in the statement.

When you are attempting to compare two different accounts that have significantly different orders of magnitude (like Sales and Net Profit after Tax), it is often useful to use different vertical scales (axis) for the two accounts.  This is not difficult, but the process is not particularly intuitive.  We'll take you through the concepts this time, so you can do it yourself later.

As an example, we are going to look at the Gross Sales - Total and the Net Profit after Tax accounts found in the Annual Assumptions & Data Entry Sheet in the Alpha Plus model.

To generate the graph, hold down the [Ctrl] key.  Then click the Gross Sales Total account (line number 29) and the Net Profit After Tax (line 69) account to select each of them.  Finally, select Graph / Selected Data from the main menu to open the graph.  If the graph appears as a bar chart, select Options / Graph Style from the Graphic Analyzer menu and select "2D Line" from the Graph Style Selection window before closing by clicking on the X in its upper right corner.

If the legend (line with each account shown beside it near top of chart) does not appear, select Options /Show Legend, so we know which line is which.  On my copy, Gross Sales is in red and Net Profit after Tax is green.  The Net Profit line is so much smaller than the Sales line that it is very difficult to estimate the net profit values from the graph.  so let's fix this...

Select Options / Graphic Designer, or right-click anywhere on the graph, to open the underlying engine's main menu.  Select Series / series and then click the account name of interest (NET PROFIT AFTER TAX) in our case.  Finally, click the Select button to open theFormat Series dialog.  

In the Format Series window, check the Plot on 2nd Y Axis option found on the Options tab.  Then Click OK.

Now the right-hand Y-axis will be scaled from -200,000 to 1,000,000, a much better fit for the actual net profit anticipated.

Finally, let's see if we can change the format on both the right-hand Y-axis so the numbers take up less room.  To do so,

1. Select Options / Graphic Designer.

2. Select Axis / Labels.

3. Choose Value (2nd Y) Axis.

4. Select the Format Code tab in the Format Axis Label dialog.

5. Change the Code from "#,##0" to "$0, K" and click OK. The trailing comma sets the format so that the numbers are displayed in thousands of dollars, rather than dollars.

6. To change the left axis to display in millions, instead of dollars, we can use a similar technique.  In this case, the format Code to use would be $0,, "M" where the M for millions is set off with quote marks and the two trailing commas indicate rounding to millions

The final result look as shown to the left, after applying the format shown in the adjacent dialog below..

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. In August we began work on the resulting monthly budget in earnest.  By September we had derived Inventory requirements from lead time constraints and forecasted demand.

This month, our serialized project planning topic looks at the impact of near-term inventory planning on accounts payable. We also discuss how to schedule inventory reductions and their anticipated impacts.

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

Estimating Accounts Payable     top

Estimating accounts payable can be confusing.  We all know that accounts payable increases as we purchase inventory and is reduced by payments to our vendors.  But Accounts Payable is a balance sheet account, so it simply measures the total at the end of the day of a specific fiscal period.  the Accounts Payable value tells us what we owe at the end of a given day, but itself has no clues as to how that result occurred.  So how do we estimate the impact a new start up situation (such as for the new Garden Shed line we are planning) will have on our business?

First, by treating the new activity (Garden Shed sales) as a planning item, we have been able to isolate its impacts on sales, revenues, and inventory.  In prior newsletter issues, we have developed a reasonable estimate of anticipated sales.  We know we need to handle a 45 day order lead-time requirement from our vendors.  We know that on the average, an order quantity of two weeks supply will tend to minimize our total purchasing plus holding costs for the Garden Sheds.  And we know we want an extra 10.5 day average safety stock on hand.

By last issue, we had developed our plan to the point that we know what our inventory balances should be at the end of each month, and how much we expected to purchase and consume (sell) in the Garden Shed line. So how does all this translate into Accounts Payable?

To begin, consider that our planning line account Inventory - Garden Sheds on Order (units) begins growing in April, 45 days before our first anticipated sale.  Our intent was to fill the inventory pipeline by ordering every two weeks for the anticipated demand six weeks later (due to shipping lead-time requirements), so we placed our first purchase order in mid-April for sales anticipated during the first two weeks of June.  The second order on May 1st was for sales in the latter half of June, and the third order on May 15th was for sales from July 1 - July 15th.

Since we have a 45-day shipping delay between purchase order and receipt, the April 15th order should reach our doors on June 1.  We have net 30 day terms, so we will pay off the invoice by July 1.  Our second order on May 1 should arrive by June 15th, so our total invoices payable by the end of June should be just those first two orders; the last half of April's orders and the first half of May's orders.  In fact, on the average, the invoices outstanding for our Garden Sheds should equal one-half the orders from each of the prior two months, assuming we pay according to the 30 day net terms provided by our vendor.  (We will.)

So how do we know how much was ordered in the last half of April and the first half of May.  Simple.  Our purchasing rule is to order 45 days ahead of expected sales.  So we ordered stock for the first 1/2 of June in April and the last half of June in early May.  The stock arrived in June to meet demand and is payable by the end of June.  In summary then, our ending payable in June is simply the cost of  sheds we expected to sell in June.  So the net result of our two week inventory purchase cycle, a 45 day stocking cycle, and net thirty day terms is that the Accounts Payable each month is the same as the month's Cost of Goods Sold.

To set this up in our model:

1. Left-click Accounts Payable for January 1997 and open the Assumption Designer.

2. Change the Assumption Type to % of Account and select "20.  COGS - Garden Sheds" as the Account to Use in formula.

3. Enter 100% as the Value (factor), since our payable will equal our COGS.

4. Click Apply Now.  This will handle our steady-state condition.  But we haven't handled the build up of our safety stock. yet.

We said we wanted an extra 10.5 days of safety stock on hand always, so we also need to purchase that stock to sit in inventory for emergencies.  Let's add in our initial safety stock for June.

1. Change the time Horizon Options... to Select specific period only.

2. Change the date in the From Date to "6/97" and press [Enter] so only June is selected.

3. Change the Assumption Type to User Defined and edit the formula in the Formula window to read "=M20(1+(10.5/30))".  By June, we will have purchased and received all of the June demand (M20), expressed as Cost of Goods Sold, plus an additional 10.5 days worth of that 30 day demand.

4. Press [Enter] to see that our Accounts Payable will increase in June from $36(000) to $48,590 due to this extra safety stock.  (If one was concerned about possibly lower initial sales in June, one could reduce or even forego this initial safety stock and keep the extra $12,590 around for other things.)

5. We think we were already slightly pessimistic about sales in June, however, so we will invest the extra money in safety stock in this scenario.  Press Apply Now.

6. Once we have the initial safety stock in inventory, we do not need to purchase it again unless we actually sell it.  It just sits there.  But we will need to increase the amount of safety stock as sales increase, and sell some of it off as sales demands decrease seasonally.  To do so...

• Click up the From Date to July, 1997.

• Change the Time Horizon Options... to Select specific range of dates, so we can handle the rest of the forecast.

• Enter "12/97" in the To Date, to extend the horizon to the end of the Monthly forecast.

• Change the Assumption type to "User Defined".

• At the end of the current formula, add 10.5 days worth of the difference between the demand last month and this month.  This is the amount by which we will need to increase or decrease our order each cycle to maintain the prescribed 10.5 days of safety stock.  The formula should now read:
  
  "=N20*100% + (N20-M20)*(10.5/30)"  Here we used 30 as the average length of a month as close enough.  Remember these are estimates we are building.

• Press [Enter] and note that the Result(if apply) indicates our July A/P will be $205 not the original $161.  Also note that August A/P is currently scheduled to be $97.  We'll see how it changes after we apply the new formula.  

• Click Apply Now.  Interesting!  August A/P dropped from $97 to $74.  But this is what we would expect.  As seasonal sales fall, we should have anticipated the reduced need for safety stock and have reduced our orders to compensate.  Thus the extra stock added as the season builds is converted to sales as the season wanes.

So can we summarize what all this means?  

They say a picture is worth a thousand words, so lets look at a graph of  Sales, Cost of Sales, Inventory, and A/P, all for Garden Sheds.  To do so, hold down the [Ctrl] key and click each account's name to select it.  Then select Graph /  Selected Data and the graph to the right appears.  Hmmm.  Looks strange.  The green Garden Shed on Hand($) peaks at about $125,000 and everything else looks like zero.

Oops, this is an error left over from last issue.  We should have converted the inventory on hand values to $i,000s and forgot to  do so.  Let's fix this now.

1. Select "Inventory - Garden Sheds on Hand($) in Jan 1997 and open the Assumptions Designer.

2. Add "/1000" to the end of the User Defined formula, so that it reads "=I67*H17/1000".

3. Click OK to apply the formula and close the Assumptions Designer.

 We now have inventory in thousands of dollars.  SO let's check out the graphs once more.  

Cash Flow Impact - Costs  

The adjacent chart confirms  that the model is now reflecting our basic assumptions concerning Sales, Cost of Sales, Inventory, and Accounts Payable.  Our inventory is almost "just in time".; i.e. it builds as demand rises and is allowed to drop as soon as a drop-off in demand is anticipated.  Sales, Cost of Sales, and Accounts Payable are well coordinated.  So if all sales were for cash, it appears that we would see a nice cash flow throughout the year from our Garden Sheds, even if seasonal.

We can't do much better than this unless we can improve terms with our vendor, find a closer supplier with similar costs, or somehow get our customers to prepay before sales.   But what will it take to finance all this inventory building and the marketing groundwork?

A quick glance at the Monthly "Cash" account indicates we will be out of cash from February through September!  And Notes Payable will hit $466(000) by June.  Clearly if Garden Sheds are the wave of the future, we had better be able to put together a show and tell to float this new retail line.

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

Next issue we will begin an overall review of our project with an eye towards putting together a show 'n tell that will help us "bank" this project.  In the process, we will try to anticipate the questions we might face and also try to convince ourselves that we really are ready to risk floating the $500,000 loan this project seems to require.  If we are not convinced, the banker sure won't be either.

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