===============================================================================
You have received this Profit Plan Newsletter as a licensed
user of Profit Plan® -- Your Vision of Tomorrow®.
===============================================================================
PROFIT PLAN PLANNING TIPS - June, 2002
===============================================================================
June Contents:
Importing "Foreign" Financial Data
Functional Cosmetics -- After Copy/Paste
Profit Plan v2001 Update Released in May
Magic Keys For
Special Situations
[Ctrl]+[F12] -- Reverse Numeric Signs
[Ctrl]+[F11] -- Decumulate Selected Data
[Ctrl]+[F10] -- Accumulate Selected Data
Projects and Economic
Order Quantities - (Cont.)
Adjusting Economic Order Quantity for Holding Costs
What if Vendor Prices Change???
TIP: How to Compare Scenarios Easily
Accounts Payable Impact from the Project
What is an "Economic Reorder Point" anyway?
Upgrading to Profit Plan v2001
Topics Coming in the July Issue
===================================================================== top
Importing "Foreign" Financial Data
=====================================================================
Most accounting packages include a method of exporting or "printing" financial statements to disk. Typically this process includes a set of optional output file types to be used for storing the exported report on disk. These types might include ".doc", ".txt", ".rtf", ".csv" and/or ".prn" file types, to name a few.
If any of these "export" (or print) options are available in your accounting package, the data in the resulting files can be converted for relatively easy pasting into Profit Plan. By way of illustration, we will show you how to easily import data from a Windows based edition of Business Works, a MAS 90 accounting product. The process for your accounting system will vary but probably follow a similar logical process.
Step 1 -- Export the Income Statement
Use the Business Works export process to produce an Income Statement file and save it in your "My Documents" folder. For example, export the June '02 Income Statement and save it as "IS0602". By default, Business Works will save the income statement report as a "rtf" (rich text format) file.
This file format is convenient, as the resulting file can be read by most text processing products available in Windows, including Wordpad, Word Perfect, and MS Word. But to facilitate copying the report data by column, we need to use a product that will convert the data in the "rtf" file into a table of rows and columns. MS Word can do this.
NOTES: If your accounting package provides the option of including ALL accounts in the financial statement, rather than just those that are currently non-zero, use that option. This will make it easier to match up the account lines on the export report with those in your Profit Plan chart of accounts.
If your accounting system can export a ".csv", "Lotus/123", or ".prn" file, use that type for the export. The resultant data file can then be imported directly into an Excel table. So for these file types, use Excel, rather than MS Word in Step 2 below and skip Step 3.
Step 2 -- Import into a Windows text processor
Now open MS Word and open the exported income statement. (This would be the IS0602.rtf file in the "My Documents" folder, in this example.)
Step 3 -- Convert data to tabular format. (Example below is for MS Word 2002.)
Now the report numbers will be in their own columns and can easily be selected one at a time or as partial columns for pasting into a Profit Plan Assumption Sheet.
Step 4 -- Arrange Desktop (computer screen) for easy copy/paste into Profit Plan
Step 5 -- Selectively Copy/Paste the data.
You will notice that pasting data from the Windows clipboard brings along some of the MS Word formatting as well as the actual numbers. So the pasted background will probably be white, commas may disappear, etc. But these cosmetic effects are easy to fix up after all the data has been copy/pasted. So ignore cosmetics until all the data has been transferred.
The next topic will show you how to fix up the sheet cosmetics when finished.
===================================================================== top
Functional Cosmetics -- After Copy/Paste
=====================================================================
After using copy/paste to transfer data from another Windows product into
Profit Plan, you will typically need to do a little cosmetic surgery to
recover from the formatting transferred in during the copy/paste process.
This is easy. Just use the fact that Profit Plan uses the first cell selected
as the default "template" when applying cosmetics to selected
data.
As an example, suppose you have just selectively pasted financial data into most cells in the June 2002 column of the Monthly Assumptions and Data Entry Sheet. This will have left the pasted cells with a white background, perhaps with a different font, and perhaps also without commas in the pasted numbers. To fix all this...
SUMMARY: When selecting data, the FIRST cell selected acts as a "default template" for the entire area selected. So first select the cell that has the font, pattern or numeric format you want to apply over the entire selected area. Then just a few clicks will apply these SAME attributes to the rest of the selected area effortlessly.
===================================================================== top
Profit Plan v2001 Build 1351 Update Released
in May
=====================================================================
The most recent update to Profit Plan v2001 was released in late May. This is still available to our Profit Plan v2001 (only) clients directly from the Internet. Feel free to download a copy at your convenience from our Update.2001 web site.
You will find full particulars about how to install and activate this latest edition on the site.
This release is FREE to anyone who purchased or upgraded to Profit Plan v2001 after March, 2002, and to those with a currently active Profit Plan Technical Support Policy. For others, a very modest update fee is involved.
This Update edition is Profit Plan v2001 Build 1351. To insure you are running the very latest in planning software, check your copy's version number by selecting About Profit Plan from its Help menu.
===================================================================== top
Magic Keys for Special Situations
=====================================================================
While Copy/Paste or other generic imports often work just great, in some cases the incoming report data itself may be in a somewhat unexpected format. For instance, while recently importing data from a trial balance, it was noticed that the report showed all revenue values as negative! In another situation, the accounting system quite nicely produced a Monthly Income Statement including a column for each month in the fiscal year. The only catch was that each month's data was actually showing the Year-to-date result, rather than the specific month's value only. A method was needed to convert this accumulated year-to-date data to Monthly totals.
Profit Plan v2001.1348 and later has a set of special functions that can be used to help in these or similar situations. These are not on any menu and are not mentioned in the documentation. However, you just might find one of these quite handy from time to time.
[Ctrl]+[F12] -- Reverse Numeric Signs top
To reverse the sign of selected numbers, hold down the [Ctrl] key and press [F12]. The sign of each number in the selection is reversed; 100 becomes -100, -10 becomes 10, etc.
NOTE: The "+" sign above is NOT pressed. It is used above to indicate that a pair of keys must be pressed; i.e., the [Ctrl] key PLUS the [F12] key simultaneously.
[Ctrl]+[F11] -- Decumulate Selected Data top
To "decumulate" (compute the difference between periods) for data in adjacent columns, select the values. Then hold down the [Ctrl] key and press [F11].
For example, if the periods from January through March for a specific account line contain the values 100, 150 and 225, then first selecting the February and March values and then pressing [Ctrl]+F11 would replace those two values with 50 (=150-100) and 75 (=225-150). Essentially, each selected value is "decumulated" by replacing its original value with that value LESS the value found in the cell to the immediate left.
This function can be used to convert data that has been imported from a monthly year-to-date financial report into simple monthly data instead.
[Ctrl]+[F10] -- Accumulate Selected Data top
To accumulate the data on one or more rows over adjacent columns, select the area containing the data to be accumulated. Then hold down the [Ctrl] key and press [F10].
For example, if January through March data for a specific account on the Assumptons Sheet is 100, 50, 75, the Year-to-Date values for February and March can be computed by first selecting these two periods and then pressing [Ctrl]+[F10]. The values for these two months will be recomputed, working from left to right, as 150 (=100+50) and 225 (=150+75). These new "accumulated" values REPLACE the originally selected values of 50 and 75 originally found in the February and March cells.
This function can be used to "undo" decumulations or to convert monthly data into the equivalent year-to-date values for those months.
===================================================================== top
Projects and Economic Order Quantities - (part 4)
=====================================================================
Last month we began expanding our Garden Shed Product Line project to include the impacts that this new product line would have on inventory. This required thinking through just how we would purchase and stock the garden sheds, given a relatively long lead time and high cost per unit. Initially we tried modeling an inventory rule that said we would simply order one week's inventory every week. Then we modified the order quantity using common "economic order quantity" theory. By the end of that attempt, however, we observed that our "EOQ" approach to ordering stock just didn't seem all that economical. Increasing the order size to an economic order quantity should have decreased the order size and resulted in a cost saving. But we simply seemed to be using up more cash!
Today, we adjust our model to explicitly include the cost of processing and holding our new product line more completely.
We hope you have followed along with this project plan thus far. Each step in the project builds upon the last, so it will become increasingly difficult to follow if you missed the last three 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.
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 - phase3.zip model and open it up in Profit Plan now. (You will need Profit Plan v2001 Build 1350 or later to load it properly.)
Adjusting Economic Order Quantity for Holding Costs top
Last issue, we estimated a garden shed's annual unit holding cost as $72. We computed this by multiplying our "average" unit cost of $600 by our 12% cost of capital. Then we inserted that cost into the EOQ portion of the formula we derived last month to calculate our safety stock plus average purchase order balance on hand. Today we will modify the EOQ portion just slightly so that it will automatically adjust EOQ based upon the cost of garden sheds. Then the model will automatically adjust the level of garden shed inventory whenever the cost of garden sheds changes.
To automatically adjust the economic order quantity for stock on hand, proceed as follows:
What if Vendor Prices Change??? top
Our model can now tell us what happens if our vendor changes the unit price for garden sheds. Let's say that the vendor decides on a price increase of $60. Let's adjust our model's unit cost by this amount and see what falls out...
Adjusting "Average Unit Cost - Sheds" for a price increase
TIP: How to Compare Scenarios Easily top
As in most business situations, changing one factor changes many others. Earlier we edited our "Inventory - Garden Sheds on Hand (units)" formula so it would react to cost changes. Now we have also changed our Unit Cost value to include a potential vendor price increase. So now we would like to see what the overall impact would be.
The easiest way to compare this type of "what if" scenario is simply to run two copies of Profit Plan simultaneously on your PC. Then you can look at both the before and after results right on the screen.
Arranging the Desktop To View Two Scenarios
It is now easy to compare before and after results from two different scenarios.
First let's compare "Gross Profit - Garden Sheds" values. After scrolling so this value is visible in each copy, we see that the assumed 10% increase in garden shed unit cost dropped the gross profit from sheds from $280 to $241 (thousand), for a decrease of 13.9%. This is a good thing to keep in mind. Do not assume that a change in vendor cost will have a similar percentage effect on gross profit.
Now look at "Inventory- Garden Sheds on Hand (units). The increase in unit cost increased the unit holding cost in the EOQ formula, resulting in a small decrease in average units placed per order. But this did NOT decrease the average "Inventory - Garden Sheds on Hand ($)" value. The EOQ formula reacts to changes in unit value by the square root only, so much of the increase in cost reaches the average inventory value also. In our case the value increased from $18,009 to $19,455, for an overall 8% increase, even with the reduction in units stocked.
Next let's consider the "Inventory - Garden Sheds on Order($)" planning item once more. Notice that this value has gone from $47k to $52k simply due to the 10% increase in unit price from the vendor. This is because the 45 day lead time required for this product has us ordering frequently to avoid high balances on hand. But is there really a cost to be born for these items on order? In our model there is, because we assumed our overseas vendor is requiring prepayment of each order. Since we are going out-of-pocket with every order, we are reflecting this cash requirement in our "Inventory on order" line. But what if we could get Net 30 terms instead?
Accounts Payable Impact from the Project top
First, let's assume we can negotiate a new agreement with our vendor and obtain Net 30 day terms, instead of the old prepaid requirement. Let's see what this can do for us.
Reducing the Inventory - Garden Sheds on Order($) cost
When we prepaid our orders, we accounted for this undelivered inventory through the "Inventory - Garden Sheds on Order($)" planning account. There was no impact on our Accounts Payable from the garden sheds, since they were always prepaid.
Now that we are assuming new terms with no prepayment, we need to be sure we consider how much we will owe on the resulting inventory instead.
Moving into the Liabilities and Equity section, let's now include the possible impact of garden sheds on "Accounts Payable". This account's current assumption is simply that the ending balance increased by $24,000 over the year. In reality, however, we know that our accounts payable will be impacted by the garden sheds if we are not paying for every order when stocked. And in fact we are now assuming we have Net 30 day terms. So we need to add this impact into our project.
Adding a new A/P - GARDEN SHEDS planning item.
This account will carry the new accounts payable impact of buying garden sheds on terms.
Now enter the estimate for the garden shed payable...
NOTE: The above approach gives a reasonable approximation of accounts payable on an annual basis. But obviously we have glossed over considerations of initial inventory buildup and seasonality factors that may affect the monthly cash flow. Still the approach is good enough for strategic planning on an annual basis.
Summary - Project Plan to-date top
So what is the overall impact of moving from 100% prepaid to Net 30 day terms with the garden shed vendor and absorbing a 10% vendor price hike? Let's compare the model now with that from Phase 3 saved after the last newsletter issue.
First notice that our Cash is now $301 at the end of 1997, rather than the original $264 in our model as of last issue, even though we are now assuming a 10% increase in the unit cost of garden sheds. So how did we finance this increase and end up with cash in the bank?
First we already know our stock on hand has increased slightly, even after reducing our order quantities, by roughly $1.5k. But now we have eliminated the $47k impact of always prepaying for our orders. In addition, we now have $17.4k worth of financing from our garden shed vendor. The net result is that even with a 10% hike in vendor price, we can put a net of at least $36 in our pocket! So the moral of the story is...
When faced with a price increase, try to negotiate better payment terms to offset the impact. You might actually end up in a better cash position!
If you would like to compare your solution so far against ours, feel free to download a copy of Johnson Supply Project - phase 4.zip now.
===================================================================== topTheoretically there is a cost trade-off between the cost of holding extra safety stock and the cost of stocking out because the reorder point (safety stock level) was too low when the replenishment order was placed. In reality, however, it is usually quite difficult to derive a reasonable estimate of the "cost of stock out." If it is a rare occurrence, there may be no cost at all; the customer simply waits a bit and forgives the delay. If occurring on a more frequent basis, however, stock outs may cost both the sale and the customer.
Because of the difficulty in establishing a realistic cost of stocking out, most firms strive for a "level of service" instead. For instance, it may be felt that the customer base would be perfectly happy if the stock they order is on-hand when ordered 95% of the time.
If order history is relatively plentiful, a bit of basic statistical analysis might be applied to find the average and standard deviation of demand during reorder cycles. Then one could establish a reorder point at 1.96 standard deviations above the average and be confident that the item will be in stock for 95% of the demand during restocking.
Because order cycle data can be rather scarce and/or difficult to analyze in any meaningful way, one approach might be to place an order when the inventory reaches twice the demand level expected during the order lead time requirement. This is essentially what was done in this sample. Roughly two weeks stock was ordered every two weeks, with an extra 10.5 days of stock held on average to cover fluctuations of up to 75% of the expected demand.
Finally, let us note that this sample project doesn't actually use a classical "reorder point." Due to the long lead time requirement (45 days) and the relatively high cost of the item, we have opted for a constant order cycle instead. Rather than waiting until the inventory falls to enough to satisfy 45 days of demand and then ordering, we order every two weeks an amount equal to the economic order quantity plus whatever extra will be needed to replace any shortfall in safety stock that might exist at the time of ordering.
===================================================================== topFor those of you who are not yet using Profit Plan v2001, you probably have figured out that it really would be a lot easier if you were. And if you are worrying about updating now and then finding out a new version has just been released, don't waste a lot of time over it.
Yes, Profit Plan v2003 will arrive. But so will Christmas. And we keep our upgrade costs quite modest. So why not make life a little easier on yourself and upgrade now? You know you should.
Simply visit our no-hassle Profit Plan v2001 Upgrade Order Form and upgrade today.
===================================================================== 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 July Issue
=====================================================================
In July, we will continue our planning example. We now understand the concepts of "Economic Order Quantities" (EOQ) and "safety stock". Next month, we will finish up the question of how much Johnson should place in an order. Then we will address "Economic Reorder Points" (ERP) to see if our "safety stock" approach makes sense. And we still need to create an "Accounts Payable - Misumi" so we evaluate just what it would be worth if we could negotiate better terms with them.
There is still a lot left to figure out! We'll learn a bit more next month.
====================================================================
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