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

              PROFIT PLAN PLANNING TIPS - November, 2002

November Contents:

Adjusted Debt to Equity Ratio

Rolling the Assumptions Forward for New Budget Year

Migrating Variance Report Data to Assumptions Sheet

Functional Cosmetics -- Show/Hide Selected Accounts

Sample Project: Reviewing Project Financials - (part 9)

Topics Coming in the Next Issue

Many banks do not include your subordinated debt (debt junior to theirs) as actual debt in their rendition of the Debt to Equity ratio.  Essentially, they use an adjusted Debt to Equity ratio that treats the subordinated debt as "equity" to reflect the fact that, in a bankruptcy, the bank will be paid before any shareholder or subordinated debt holder.  This month we show you how to improve your Debt to Equity Ratio by reflecting this perspective.

To begin with, let's examine what the Debt to Equity ratio is, and what it is intended to measure.

The following is a paraphrased discussion from Robert Morris Associates' Annual Statement Studies , probably one of the most well known suppliers of industry ratios in the USA.

Debt to Equity:  This is the ratio of Total Liabilities to Tangible Owners Equity. It is intended to express the relationship between capital contributed by creditors and that contributed through equity sources and is thought to reflect the degree of protection provided to the creditors through the owners."

So what is the impact on this ratio if one considers subordinated debt (particularly that due Owners) as equity rather than debt.?

If one considers loans from shareholders as simply equity contributed to the firm, the impact on the Debt to Equity Ratio can be substantial.  If you have subordinated debt in your balance sheet, try this in one of your Profit Plan models:

1.  In Account Setup, click the account representing shareholder debt in the Liabilities section and open the Edit Account window.  (Press [Ctrl][U], right-click and select from the pop-up menu, etc.

2. Change the Account Type of the subordinated debt from its current account type (perhaps Other Notes Payable, or Long-term Debt - Owners) to the equity account type, "Paid in Capital".

3. Switch to the Ratios' report and check out the Debt to Equity ratio.  You will see an improvement!  The debt owed to the Owners has magically moved from Liabilities to Equity.

Of course, this movement affects the ratio because the total debt is now considered less and equity more.  And while the Balance Sheet still balances, the Total Liabilities amount will now be less and the Total Equity amount more than the numbers above these totals would sum to on an adding machine.  

To force the financial reports to retain their old totals while letting the Debt to Equity ratio reflect this new philosophy, one could use "Subtotal" account types on the Total Liabilities and Total Equity account lines of the Assumptions Sheets.  Then new subtotals could insure nothing would appear different than on the printed financial reports, but the ratio would be much improved.

SUMMARY:  The point here is not that one could enhance one's numbers through cosmetic use of subtotals and account types.  Rather there are two lessons involved:  (1)  When your balance sheet doesn't add up automatically, it probably is due to the unintended use of  one or more account types that effectively move the numbers from one section of the accounts to another, for financial calculation.   (2)  Ratios used for risk analysis may not be computed exactly the way their name would imply, depending upon the philosophy behind them.

For many of you, the fiscal year-end is the calendar year-end.  So as December 31st approaches, it is time to begin work on your next fiscal year budget, if you haven't already.  This is a three-step process.  (1)  Adjust the Fiscal Horizons,  (2)  Migrate near term historical data into the Monthly Assumptions Sheet from printed financial statements or from the Monthly Income Statement and Variance Reports, and (3) begin developing new forecasts.

There is nothing magic about setting the fiscal horizons for the new year.  But it is best to proceed in baby steps.  Here are some considerations.

1. A maximum of five years (60 months) are available in the Monthly View for both historical and forecast data.  If you already have 60 periods, you need to remove some of the earlier periods before adding the new year.

1. In the Set Up Fiscal Horizons dialog, move your Monthly Historical Horizon Start Period forward 12 months and click OK.

2. You will se a message saying "The number of historical periods has been reduced by 12."  At that point, select Yes to remove the periods and wait until the Monthly horizon has been adjusted.  Then you are ready to add new periods.

2. To add another year to the Monthly horizon:

1. In the Set Up Fiscal Horizons dialog, leave the Monthly Start Period unchanged.

2. Increase the End Period of the Historical Horizon  by 12 months.

3. Increase the End Period of the Forecast Horizon by 12 months

4. Click OK.  The following message will appear.  "The number of historical periods has increased by 12."

5. At that prompt, select No, so that the monthly horizon will be extended 12 months into the future.  This will result in the historical (blue) area growing to include the 12 more months, with the forecast (yellow) area moving to the right, beyond that.

At this point, the historical area of the Monthly Assumptions and Data Entry sheet has been expanded and is ready to receive "Actuals" for this past year.  Since the year really isn't over, we are fudging just a bit, of course.  But the original forecast formulas still exist, so the December "historical" period will still generate numbers, once we have the rest of the year entered. 

When the new Monthly Fiscal Horizon is established, as above, the date heading in the Monthly Income Statement Variance and the Monthly Balance Sheet Variance reports automatically shift to reflect the new time periods.  But the data in the Actual columns are left undisturbed.  Thus the data you entered early in the year as  January, 2002 Actual results still resides in the Variance reports January columns.  But the January column's  heading now is January, 2003.

This seeming anomaly is actually intentional.  Until the Assumptions Sheet rolled forward, there was no place to put this year's monthly historical data.  Now that there is, the remaining task is to remove it from the variance reports and place it in the equivalent Assumption Sheet columns.

This is the process we follow when migrating data from the Monthly Income Statement Variance and Balance Sheet Variance reports, after selecting Monthly from the View / Select Horizon main menu.:

1. Select Reports / Variance Reports / Income Statement.

2. (Optional)  Select Options / Custom Titles from the main menu.  Then check the Allow custom titles and changes to the report body and select Never refresh a customized report and do not warn.  After clicking OK, we will see a message explaining that we can now modify the titles.  While that is not our intent at the moment, click OK again.
   
   The entire point of this optional step is simply to save time.   With the customize options active, Profit Plan will not attempt to recover from each Cut we do in the Actuals column.  This can save significant time in models with large charts of accounts, which can be relatively slow to refresh.  (We will undo this step later, after all data has been migrated and we want the reports maintained again.)

3. Select Reports / Variance Reports / Balance Sheet.

4. Optionally, repeat Step 2, to prevent subsequent recalculations while we migrate data from the Balance Sheet Variance report.

5. Migration:  Working from right to left, copy each Actual column of Balance Sheet data to the equivalent area in the Monthly Assumptions Sheet:

1. Unlock the report by pressing [Ctrl][L].  A small unlocked  padlock will appear in the lower right area of the Status Panel at the bottom of Profit Plan.  

2. Click the word Actual to highlight the most recent column with actual historical data (in blue). Mote the name of the first account line highlighted.  (This will typically be the word "Assets".)

3. Press [Ctrl][X] to cut the data to the clipboard.

4. Click the Assumptions Tab and check that it is also unlocked.  If the unlocked padlock does NOT show in the right end of the Status Panel, press [Ctrl][L] to make it appear.

5. Place the cursor on the corresponding month in the Assumptions Sheet, insuring it is on the same account line as the first line highlighted on the Balance Sheet Variance report.

6. Press [Ctrl][V] to paste the data into the Assumptions Sheet.  If you misplaced the cursor, move to the correct position and do it again.  Then delete the extraneous data previously pasted.
   
   Note:  It is a good idea to Save after each paste.  If you didn't, remember that you can always start up another copy of Profit Plan, load the model from disk, and copy paste between the second copy and the current version.

6. After the Actual columns have been copied, switch to the Income Statement Variance report and repeat the same steps for the Income Statement Variance report
   

7. After the Actual columns of data have been migrated for both reports, return to each report and reverse Step 2 above, so that the reports will again recalculate and automatically reset the associated Cash Flow Variance and Cash Budget Variance report.  When finished, all four of these variance reports will now be ready for new data.

8. Finally, return to the Assumptions sheet to clean up some color and underline anomalies that will have crept in along with the data during the cut and paste:

1. From the main menu, select Options / Reset Assumptions and then select the Refresh account names and layout.  After clicking OK, the normal coloring will be restored.

2. From the main menu, select Format / Subtotal Underlines.  Then click OK.  This will restore any underlines that may have been overwritten during the pasting.  You are now ready for forecasting again.

Most of you know that the Hide Accounts column on the Account Setup tab can hide individual rows on either the Assumptions and Data Entry sheets, the associated formal reports, or both.  But sometimes you just want to print a portion of a report to give to a division manager, and want to exclude all other data without having to set up permanent report settings.  Here's an alternative that works with income statement and balance sheet style reports...

After opening the report you wish to work with, press [Ctrl][G].  This will expose the report's underlying grid layout and make it easier to see which report lines are displayed and/or hidden.

To hide the lines you do not wish to print, simply shift-drag down the left-hand row numbers, or [Ctrl]-click the lines you wish to hide.  After they are selected, chose Format / Adj Rows & Cols from the main menu.  Then click the Row Adjustments tab and then Hide.  If the result is as you wish, click OK.

Once the rows are hidden, proceed to print the remaining exposed lines.

To restore the entire report, simply drag down all the remaining exposed report row numbers from top to bottom.  Then again open the Adj Rows & Cols dialog and Unhide the entire report.  All hidden rows within the selection will now be exposed again.

Finish by clicking OK.  The report is back to normal.

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.  Last month we looked at the resulting Accounts Payable and noted we would probably experience a brief peak summer need of up to $500,000 to finance the project's startup expenses.

This month we will begin thinking about producing a show and tell for our banker.  While we have been with the bank a long time, in this era of consolidation, it is smart to expect old friends to be under new and less friendly constraints.  To do so, we will begin examining our results and tools in the BizPlan module.

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

Planning Outlines (Topical)          top

When we arrive in the BizPlan module, the first thing to note is that a topical planning outline has been prepared for us in the left-hand pane of BizPlan's main window. There we find our "Add Garden Shed Product Line" project, which we created with the first planning item we entered for this project.

When you click on any item in this planning outline, the related topic issue scrolls into the Topic Issues pane immediately to the right of the outline.  If you hover your cursor over the lower edge of this pane, the cursor will turn into a horizontal bar (like an "=" sign with up/down arrows.)  At that point, press the left mouse ear and drag the bottom of the pane down to occupy most of that area of the BizPlan module's window.  Now we can see that the Project Plan in the Topical Issues pane will serve us in thinking through our presentation.  Next month we will actually work through it.

Project Financials Pane           top

Now click on the Project Financials pane.  The purpose of this pane is to collect our planning items on a single sheet so that we can review them directly.  This isolation from the reset of the accounts helps to highlight both those things we have included and those we haven't.  And, since the Project Financials pane is itself a spread sheet, we can run computations there, should we wish.

To begin, lets do some adjusting to emphasize the items we are interested in most.

1. Scroll to the top left of the project window.

2. Place cursor on the "This" at the start of the project description line.  We are going to shrink this column to tighten up the presentation.

3. From the main menu, select Format / Adj Rows & Cols and then the Column Adjustment tab.  Now set the Current Column Width to 0.1 inches and click OK.  The full project description will continue to display, since there is no intervening to the right on this line. But now all the rest of the columns have been pulled much closer to the left of the report.  All  the better for printing later.

4. Since we know that we have projected no impact on our business financials before April '97, we can hide the extraneous earlier columns.  To do so:

1. Click and drag the cursor from Dec '96 to Mar '97.

2. Right-click and select the Adj Rows & Cols option from the pop-up menu.  Then make sure the Column Adjustments  tab has been automatically selected.

3. Click the Hide button and the OK.  Now we have the data range we want.

5. To Freeze the report heading so we know where we are as we scroll the sheet, let's do the following:

1. Place the cursor on the last cell before the April 1997 forecast on the Forecast line (second brown row.)

2. Select Options / Freeze Titles from the main menu.  A comment message will appear.

3. Click OK when the message appears.  We are set up properly to freeze the titles now.

The picture below captures our sample project just after Step 5 above has locked our titles in place.  We are now ready to actively use this module in detail.  But alas, we are also out to time for the month.  Next month we will begin putting flesh on this topic in detail.

Next issue we will put some flesh on our project write-up in the BizPlan module.

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