Accounting, Financial, Taxation

One is sometimes called upon to make sales forecasts or verify those made by the sales and marketing departments. One of the better approaches for doing this is to extend the past history of sales volume forward into the periods being projected. Though this method of prediction is like trying to drive a car by looking in the rear view mirror, it is still one of the best tools available, as long as it is supplemented by detailed conversations with the sales staff to see what is really happening in the marketplace.

There are two formulas provided by Excel that result in forecast information:

The first, and simplest, is the TREND command. This one superimposes a trend line on an existing set of time-sequenced data points to arrive at an expected sales level for a specified future period.

To illustrate the command, we return once again to the income statement shown earlier in my previous post [Financial Statement Proportional and Ratio Analysis with Excel Spreadsheet]. We will use a new worksheet within the same spreadsheet, called Trend, and reference in it all of the monthly sales figures from the previous income statement. This is shown in below figure, along with a graph that shows the added trend line.

In the above worksheet, we already know all sales data points from January through August, and want to calculate a trend line that extends an additional month to give us a prediction for September sales. Accordingly, in the table of months and historical sales figures noted in the Trend worksheet, there is an additional cell next to the “August” sales period. In that cell we enter the following formula:

TREND(B4:B11,A4:A11,A12)

Though it looks complicated, this is a relatively simple command. The trend line is based on the data points contained in cells B4 through B11 for the date ranges contained in cells A4 through A11. The date for the period to be forecast is noted in cell A12. The formula generates a number that is the extension of the trend shown by the previous data elements and will deposit this number in the B12 cell.

Another way to state the formula is to ignore the dates and just ask for the next number in sequence. The formula, based on the previous example, looks like this:

TREND(B4:B11, ,{9})

Under this variation, we are using the same set of data points, but ignoring the dates (hence the two commas in the formula with no data in between), and a number in brackets which represents the trend for the ninth number in the sequence of data elements. Since the original set of data only included eight data elements, this will be the next revenue figure after the last month of actual data. If the requested trend were for the month of December, the number in brackets would change to {12}, since this would represent the twelfth data point in the series.  However, to add a trend line overlay to the presented data, click on the completed graph, move the cursor to the revenue line on the chart, and press the right mouse button. Then click on the Add Trend line option, and pick from six available types of trend lines that can be added to the graph.

The second type of forecasting tool provided by Excel is REGRESSION ANALYSIS. This is a powerful tool for determining the trend line that best fits a disparate set of data, and is most useful when dealing with a set of numbers that are widely scattered, and show no apparent pattern.

In essence, the method determines the trend line that minimizes the sum of all squared errors between all data points and the line. Rather than delve into the formula for this method, it is easiest to plot the data elements and proceed immediately to a graph, on which Excel will superimpose a regression trend line.

In the above figure, the second half of the presentation includes the regression analysis. In it, we have plotted twenty data items for twenty periods that are wildly different from each other, and have no apparent pattern. The first step in the analysis is to create a graph. Then use the same steps just described for the TREND analysis to add a trend line to the chart. The result is shown in the bottom half of the above figure, where we find that there is a slight upward trend line to the data used to compile the regression trend line.

Of the two methods presented, the TREND formula is of the most use, for one will find that most data being analyzed in the financial arena has a lengthy and steady trend line of data. Only for the most unusual analyses, involving wildly disparate data items, will the regression analysis be necessary.

The following list shows some of the trend lines that you may consider and try when measuring a company’s operations using the TREND formula in excel spreadsheet:

1. Trend of amount of utilized storage space. This trend can spotlight many contributing problems, such as obsolete inventory, returned goods, scrapped parts, and excess finished goods.
2. Trend of cost of distribution channels. This analysis should include the net margins earned on each sales channel.
3. Trend of cost of freight. A company may extend into new geographical regions without considering the cost of shipping product into those areas. This analysis should be conducted by region to spot such problems.
4. Trend of cost of sales calls. In particular, one should compare the relationship between the sales received from high-volume customers and the cost of making sales calls to them. It is common to find a few high-maintenance customers who are not worth the sales effort from a cost-benefit perspective.
5. Trend of design cycle iterations required. Increases in the number of design iterations indicate serious problems in the design process, while a drop in the trend indicates good design management (though it can also indicate that an insufficient number of product reviews are being made).
6. Trend of direct labor rates. This can be used in comparison to a market survey to see if company pay rates are varying from those offered by other firms. After a layoff, it can change suddenly if people whose pay varies substantially from the mean have been laid off.
7. Trend of engineering change notices issued. A jump in the number of change orders can signal the presence of quality problems, as well as a likely increase in inventory, as some components are rendered obsolete.
8. Trend of gross margins. Investigate margins by both product and volume.
9. Trend of inventory, bill of material, and labor routing accuracy. These three items require very high levels of accuracy in order to operate a production planning system. Any drop in these trends will likely result in production snafus.
10. Trend of number of product options per product family. Changes in this trend can refer to a deliberate attempt at product proliferation as a marketing strategy (if it increases) or a rationalization of engineering tasks (if it declines).
11. Trend of overhead capitalization. If the amount of overhead shifted into a capital account, such as inventory, is changing, this may signal a deliberate attempt by management to alter the reported level of earnings.
12. Trend of pricing. Track the sensitivity of sales volume to changes in pricing.
13. Trend of product returns. A sudden change in this trend can signal a quality problem in the product, or that the distribution pipeline is full.
14. Trend of ratio of overhead to production labor. Overhead can balloon rapidly, and this is a prime early indicator of the problem.
15. Trend of return on shareholders’ equity and return on assets. A significant decline in these measures signals reduced cash flow, more difficult borrowing covenants, and a host of other problems, such as reduced margins and increased expenses.
16. Trend of sales quotas. This trend can be compared to actual sales per salesperson to see if the quotas are unrealistically high or low.
17. Trend of sales volume. Examine sales volume by both territory and product.
18. Trend of utilized plant capacity. This is a good indicator of the need for additional production shifts, increased maintenance, or more facilities.

Profit variance analysis, often called “gross profit analysis“, deals with how to analyze the profit variance that constitutes the departure between actual profit and the previous year’s income or the budgeted figure. The primary goal of profit variance analysis is to improve performance and profitability in the future. On this post, I am going to talk about profit variance analysis in more detail (as simple as it can be), such as; types of standard in profit variance analysis,  how to calculate profit variance for both single and multi product, and nice case example to get a better understanding of how profit variance analysis

Profit, whether it is “gross profit” or “contribution margin”, is affected by at least three basic items: sales price, sales volume, and costs. In addition, in a multi product firm, if not all products are equally profitable, profit is affected by the mix of products sold.

The difference between budgeted and actual profits is due to one or more of the following:

Changes in unit sales price and cost, called “sales price” and “cost price variances“, respectively. The difference between sales price variance and cost price variance is often called a “contribution-margin-per-unit variance” or a “gross-profit-per-unit variance“, depending on what type of costing system is being referred to—that is, absorption costing or direct costing.

Contribution margin, however, is considered a better measure of product profitability because it deducts from sales revenue only the variable costs that are controllable in terms of fixing responsibility. Gross profit does not reflect cost-volume-profit relationships. Nor does it consider directly traceable marketing costs.

Changes in the volume of products sold summarized as the “sales volume variance“ and the “cost volume variance“. The difference between the two is called the “total volume variance“.

Changes in the volume of the more profitable or less profitable items referred to as the “sales mix variance“. Detailed analysis is critical to management when multi products exist. The volume variances may be used to measure a change in volume (while holding the mix constant), and the mix may be employed to evaluate the effect of a change in sales mix (while holding the quantity constant). This type of variance analysis is useful when the products are substituted for each other or when the products that are not necessarily substitutes for each other are marketed through the same channel.

Now, let’s go for further details….

Types Of Standards In Profit Variance Analysis

To determine the various causes for a favorable variance (an increase) or an unfavorable variance (a decrease) in profit, we need some kind of yardstick to compare against the actual results. The yardstick may be based on the prices and costs of the previous year, or any year selected as the base period. Some companies summarize profit variance analysis data in their annual report by showing departures from the previous year’s reported income.

However, one can establish a more effective control and budgetary method rather than the previous year’s data. Standard or budgeted mix can be determined using such sophisticated techniques as linear and goal programming.

How To Calculate Profit Variances For Single-Product Firms?

Profit variance analysis is simplest in a single-product firm, for there is only one sales price, one set of costs (or cost price), and a unitary sales volume. An unfavorable profit variance can be broken down into four components: a sales price variance, a cost price variance, a sales volume variance, and a cost volume variance.

The sales price variance measures the impact on the firm’s contribution margin (or gross profit) of changes in the unit selling price. It is computed as:

Sales price variance = (Actual price - Budget price) × Actual sales

 

If the actual price is lower than the budgeted price, for example, this variance is unfavorable; it tends to reduce profit. The cost price variance, however, is simply the summary of price variances for materials, labor, and overhead. (This is the sum of material price, labor rate, and factory overhead spending variances.) It is computed as:

Cost price variance = (Actual cost - Budget cost) × Actual sales

If the actual unit cost is lower than budgeted cost, for example, this variance is favorable; it tends to increase profit. We simplify the computation of price variances by taking the sales price variance less the cost price variance and call it the gross-profit-per-unit variance or contribution-margin-per-unit variance.

The sales volume variance indicates the impact on the firm’s profit of changes in the unit sales volume. This is the amount by which sales would have varied from the budget if nothing but sales volume had changed. It is computed as:

Sales volume variance = (Actual sales - Budget sales) × Budget price

If actual sales volume is greater than budgeted sales volume, this is favorable; it tends to increase profit. The cost volume variance has the same interpretation. It is:

(Actual sales - Budget Sales) × Budget cost per unit

The difference between the sales volume variance and the cost volume variance is called the “total volume variance“.

How To Calculate Variances For Multi Product Firms?

When a firm produces more than one product, there is a fourth component of the profit variance. This is the sales mix variance, the effect on profit of selling a different proportionate mix of products than that which has been budgeted. This variance arises when different products have different contribution margins. In a multi product firm, actual sales volume can differ from that budgeted in two ways. The total number of units sold could differ from the target aggregate sales. In addition, the mix of the products actually sold may not be proportionate to the target mix. Each of these two different types of changes in volume is reflected in a separate variance.

The total volume variance is divided into the two: the sales mix variance and the sales quantity variance. These two variances should be used to evaluate the marketing department of the firm. The sales mix variance shows how well the department has done in terms of selling the more profitable products, while the sales quantity variance measures how well the firm has done in terms of its overall sales volume. They are computed as:

Profit Variance Calculation Case Example

The controller of the Royalla Publishing Company prepared the following comparative statement of operations for 20XA and 20XB.

The controller was very pleased with the performance of the company in 20XA. Analyze the decline in gross profit between 20XA and 20XB by calculating:

a. Sales price variance
b. Cost price variance
c. Sales volume variance
d. Cost volume variance
e. Total volume variance (sales volume variance ? cost volume variance) or (sales mix variance + sales quantity variance).
f. Sales mix variance
g. Sales quantity variance

e. Total volume variance = sales volume variance ? cost volume variance = $112,500F ? $75,000U = $37,500F, which is broken down into the sales mix variance and the sales quantity variance as follows:

f. Sales mix variance = 0 since we have only one product in this problem.
g. Sales quantity variance

The decline in gross profit of $28,500 can be explained as:

The decrease in gross profit is thus accounted for: 141, 000U ? 112, 500F = 28, 500U

Following on my previous post about Cost/Benefit Analysis Of ERP Implementation, here is the more chocolate. The example! To illustrate the process, let’s create a hypothetical company with the following characteristics:

Annual sales: $500 million
Employees: 1000
Number of plants: 2
Distribution centers: 3
Manufacturing process: Fabrication and assembly
Product: A complex assembled make-to-order product, with many options
Pre-tax net profit: 10 percent of sales
Annual direct labor cost: $25 million
Annual purchase volume (production materials): $150 million
Annual cost of goods sold: $300 million
Current inventories: $50 million

Now, let’s take a look at its projected costs and benefits both for a combined ERP/ES implementation and then for an ERP only project.

First, a warning!:

Beware! The numbers that follow are not your company’s numbers. They are sample numbers only. They may be too high or too low for your specific situation. With that caution, let’s examine the numbers. Below figure contains our estimates for the sample company. Costs are divided into one time (acquisition) costs and recurring (annual operating) costs . . . and are in our three categories: C = Computer, B = Data, A = People.

Note that we have not tried to adjust the payout period or the rate of return for the obvious tax consequences of expenses versus capital.

This is for simplicity (but also recognizes that the great majority of the costs are current expenses and that expenses considered as capital investment represent a relatively small number). You may want to make the more accurate, tax-sensitive calculation for your operation. These numbers are interesting, for several reasons.

First, they indicate the total ERP/ES project will pay for itself in seven to eight months after full implementation.

Second, the lost opportunity cost of a one-month delay is $1,049,250. This very powerful number should be made highly visible during the entire project, for several reasons:

1. It imparts a sense of urgency (“We really do need to get ERP and ES implemented as soon as we can”).

2. It helps to establish priorities (“This project really is the number two priority in the company”).

3. It brings the resource allocation issue into clearer focus. Regarding this last point, think back to the concept of the three knobs from my previous post—work to be done, time available in which to do it, and resources that can be applied. Recall that any two of these elements can be held constant by varying the third.

Too often in the past, companies have assumed their only option is to increase the time. They assumed (often incorrectly) that both the work load and resources are fixed. The result of this assumption:

A stretched-out implementation, with its attendant decrease in the odds for success.
Making everyone aware of the cost of a one-month delay can help companies avoid that trap. But the key people really must believe the numbers. For example, let’s assume the company’s in a bind on the project schedule. They’re short of people in a key function. The choices are:

1. Delay the implementation for three months. Cost: $3,147,750 ($1,049K x 3).

2. Stay on schedule by getting temporary help from outside the company (to free up the company’s people to work on ERP and ES, not to work on these projects themselves). Cost: $300,000.

Few will deny $300,000 is a lot of money. But, it’s a whole lot less than $3,147,750. Yes, we know this is obvious, but you would be amazed at how many companies forget the real cost of delayed benefits.

So far in this example, we’ve been talking about costs (expenses) and benefits (income). Cash flow is another important financial consideration, and there’s good news and bad news here.

First, the bad news: A company must spend virtually all of the $8 million (one-time costs) before getting anything back. The good news: Enormous amounts of cash are freed up, largely as a result of the inventory decrease.

The cost/benefit analysis for the total effort projects an inventory reduction of $10 million (10 percent of $25 million raw material and work in progress and 30 percent of $25 million in finished product). This represents incoming cash flow. (See the worksheet for details.)

The company does have negative cash flow in year 1 since most costs occur (as with virtually every project) before savings materialize. However, while the cumulative cash position is still negative at the end of year 2, the project will have generated over $5 million of cash for that year. By year 3, you are generating cash in a big way.

How many large projects has your company undertaken that have no cash impact in the second year with full savings in the third? We bet not many. For our example company, ERP and ES appear to be very attractive: An excellent return on investment (193 percent) and substantial amounts of cash delivered to the bank.

ERP Only

The major difference between doing ERP and ES together or doing just ERP is the enhanced speed and accuracy of information flow when using an ES. Every decision from forecasting to sales to production will be more accurate and faster and will thus generate added benefits.

However, you can still have an impressive change in your business with ERP even with a non-integrated information system. Presumed that the ERP project would fund one of several attractive supply chain software packages available but this would be a standalone assist to the forecasting/planning effort. There may be some added costs if ES comes after ERP due to the need to connect the ERP wiring to ES. However, this cost should be relatively small compared to the rest of the project.

Here’s a familiar question: “Does size matter?” In terms of the payout, not as much as you might think. For a very small company, the challenge usually is resources. There are simply too few people to add a major effort such as this without risk to the basic business. Too often, small companies (and, to be fair, large ones also) will hire consultants to install ES and will ignore the ERP potential. These companies are usually very disappointed when they realize the costs have not brought along the benefits.

Large, multinational companies should be able to allocate resources and should find that the benefits are even more strategic. The problem with larger companies is trying to get all parts of the company, worldwide, to adhere to a common set of principles and practices. If pulling together all aspects of the company is difficult, it is recommended that the project be attacked one business unit at a time. The impact for the total company will be delayed but the more enlightened business units that do install the total project will see rapid results.

Here are a few final thoughts on cost/benefit analysis (in the case you are attempting to implement ERP and are on the stage to calculate the cost/benefit):

1. What it has been trying to illustrate here is primarily the process of cost/benefit analysis, not how to format the numbers. Use whatever format the corporate office requires. For internal use within the business unit, however, keep it simple—two or three pages should do just fine. This is a format I found to be most helpful for operational and project management purposes.

2. We’ve dealt mostly with out-of-pocket costs. For example, the opportunity costs of the managers’ time have not been applied to the project; these people are on the exempt payroll and have a job to do, regardless of how many hours will be involved. Some companies don’t do it that way. They include the estimated costs of management’s time in order to decide on the relative merits of competing projects. This is also a valid approach and can certainly be followed.

3. Get widespread participation in the cost/benefit process. Have all of the key departments involved. Avoid the trap of cost justifying the entire project on the basis of inventory reduction alone. It’s probably possible to do it that way and come up with the necessary payback and return on investment numbers. Unfortunately, it sends exactly the wrong message to the rest of the company. It says: “This is an inventory reduction project,” and that’s wrong. We are talking about a whole lot more than that.

4. We did include a contingency to increase costs and decrease savings. Many companies do this as a normal way to justify any project. If yours does not, then you can choose to delete this piece of conservatism. However, it is encouraged the use of contingency to avoid distractions during the project if surprises happen. Nothing is more discouraging than being forced to explain a change in costs or benefits even if the total project has not changed in financial benefit.