Posts Tagged ‘small business forecasting’

Forecast Friday Topic: Simple Regression Analysis (Continued)

June 3, 2010

(Seventh in a series)

Last week I introduced the concept of simple linear regression and how it could be used in forecasting. I introduced the fictional businesswoman, Sue Stone, who runs her own CPA firm. Using the last 12 months of her firm’s sales, I walked you through the regression modeling process: determining the independent and dependent variables, estimating the parameter estimates, α and β, deriving the regression equation, calculating the residuals for each observation, and using those residuals to estimate the coefficient of determination – R2 – which indicates how much of the change in the dependent variable is explained by changes in the independent variable. Then I deliberately skipped a couple of steps to get straight to using the regression equation for forecasting. Today, I am going to fill in that gap, and then talk about a couple of other things so that we can move on to next week’s topic on multiple regression.

Revisiting Sue Stone

Last week, we helped Sue Stone develop a model using simple regression analysis, so that she could forecast sales. She had 12 months of sales data, which was her dependent variable, or Y, and each month (numbered from 1 to 12), was her independent variable, or X. Sue’s regression equation was as follows:

Where i is the period number corresponding to the month. So, in June 2009, i would be equal to 6; in January 2010, i would be equal to 13. Of course, since X is the month number, X=i in this example. Recall that Sue’s equation states that each passing month is associated with an average sales increase of $479.02, suggesting her sales are on an upward trend. Also note that Sue’s R2=.917, which says 91.7% of the change in Sue’s monthly sales is explained by changes in the passing months.

Are these claims valid? We need to do some further work here.

Are the Parameter Estimates Statistically Significant?

Measuring an entire population is often impossible. Quite often, we must measure a sample of the population and generalize our findings to the population. When we take an average or standard deviation of a data set that is a subset of the population, our values are estimates of the actual parameters for the population’s true average and standard deviation. These are subject to sampling error. Likewise, when we perform regression analysis on a sample of the population, our coefficients (a and b) are also subject to sampling error. Whenever we estimate population parameters (the population’s true α and β), we are frequently concerned that they might actually have values of zero. Even though we have derived values a=$9636.36 and b=$479.02, we want to perform a statistical significance test to make sure their distance from zero is meaningful and not due to sampling error.

Recall from the May 25 blog post, Using Statistics to Evaluate a Promotion, that in order to do significance testing, we must set up a hypothesis test. In this case, our null hypothesis is that the true population coefficient for month – β – is equal to zero. Our alternative hypothesis is that β is not equal to zero:

H0: β = 0

HA: β≠ 0

Our first step here is to compute the standard error of the estimate, that is, how spread out each value of the dependent variable (sales) is from the average value of sales. Since we are sampling from a population, we are looking for the estimator for the standard error of the estimate. That equation is:

Where ESS is the error sum of squares – or $2,937,062.94 – from Sue’s equation; n is the sample size, or 12; k is the number of independent variables in the model, in this case, just 1. When we plug those numbers into the above equation, we’re dividing the ESS by 10 and then taking the square root, so Sue’s estimator is:

sε = $541.95

Now that we know the estimator for the standard error of the estimate, we need to use that to find the estimator for the standard deviation of the regression slope (b). That equation is given by:

Remember from last week’s blog post that the sum of all the (x-xbar) squared values was 143. Since we have the estimator for the standard error of the estimate, we divide $541.95 by the square root of 143 to get an Sb = 45.32. Next we need to compute the t-statistic. If Sue’s t-statistic is greater than her critical t-value, then she’ll know the parameter estimate of $479.02 is significant. In Sue’s regression, she has 12 observations, and thus 10 degrees of freedom: (n-k-1) = (12-1-1) = 10. Assuming a 95% confidence interval, her critical t is 2.228. Since parameter estimates can be positive or negative, if her t value is less than -2.228 or greater than 2.228, Sue can reject her null hypothesis and conclude that her parameter estimates is meaningfully different from zero.

To compute the t-statistic, all Sue needs to do is divide her b1 coefficient ($479.02) by her sb ($45.32). She ends up with a t-statistic of 10.57, which is significant.

Next Sue must do the same for her intercept value, a. To do this, Sue, must compute the estimator of the standard deviation of the intercept (a). The equation for this estimate is:

All she needs to do is plug in her numbers from earlier: her sε = $541.95; n=12; she just takes her average x-bar of 6.5 and squares it, bringing it to 42.25; and the denominator is the same 143. Working that all in, Sue gets a standard error of 333.545. She divides her intercept value of $9636.36 by 333.545 and gets a t-statistic of 28.891, which exceeds the 2.228 critical t, so her intercept is also significant.

Prediction Intervals in Forecasting

Whew! Aren’t you glad those t-statistics calculations are over? If you run regressions in Excel, these values will be calculated for you automatically, but it’s very important that you understand how they were derived and the theory behind them. Now, we move back to forecasting. In last week’s post, we predicted just a single point with the regression equation. For January 2010, we substituted the number 13 for X, and got a point forecast for sales in that month: $15,863.64. But Sue needs a range, because she knows forecasts are not precise. Sue wants to develop a prediction interval. A prediction interval is simply the point forecast plus or minus the critical t value (2.228) for a desired level of confidence (95%, in this example) times the estimator of the standard error of the estimate ($541.95). So, Sue’s prediction interval is:

$15,863.64 ± 2.228($541.95)

= $15,863.64 ± $1,207.46


So, since Sue had chosen a 95% level of confidence, she can be 95% confident that January 2010 sales will fall somewhere between $14,656.18 and $17,071.10

Recap and Plan for Next Week’s Post

Today, you learned how to test the parameter estimates for significance to determine the validity of your regression model. You also learned how to compute the estimates of the standard error of the estimates, as well as the estimators of the standard deviations of the slope and intercept. You then learned how to derive the t-statistics you need to determine whether those parameter estimates were indeed significant. And finally, you learned how to derive a prediction interval. Next week, we begin our discussion of multiple regression. We will begin by talking about the assumptions behind a regression model; then we will talk about adding a second independent variable into the model. From there, we will test the model for validity, assess the model against those assumptions, and generate projections.

Sales Forecasting: Crucial to Small Business Success

May 18, 2010

Forecasting sales is never easy, yet the ability to do so can alleviate a lot of headaches, especially for owners of small or family businesses. Small businesses have lots of the same questions large companies do: how much inventory to acquire/keep? How many workers to staff on Wednesday? How much lift in sales will each $1,000 of advertising expenditure generate? How much will sales change if we adjust the price up/down by $1? Most business owners, with all their other pressing responsibilities, have either given sales forecasting a low priority on their task list, or given up on it entirely. This is unfortunate, since an objective system of sales forecasting can greatly simplify a business owner’s planning, identify areas for improvement, and even enhance the value of his/her business. Today’s blog post explains the various benefits of having a sales forecasting system.

Simplified Planning, Reduced Planning Time

With an objective way to forecast sales, business owners can greatly reduce the time it takes them to plan for inventory purchasing and employee staffing. This is because such a system minimizes the uncertainty of tomorrow by establishing educated guesses based on historical sales. Often, decisions based on these measures are more accurate than those made with unaided judgment. A forecasting system recognizes patterns within the data, so that a business owner can make adjustments for seasonality, trends, and business cycle occurrences. For the most part, if sales on Tuesdays have been averaging $2,000 per day and sales on Wednesdays $3,000 per day, then the business owner knows to schedule more staff on Wednesdays and carry more inventory than on Tuesdays. Just knowing how sales are trending saves the business owner some valuable time.

A sales forecasting system can also help a business owner gauge the impact of seasonality. If he/she finds that sales of her product/service in July average 10% higher than baseline monthly sales, then the owner can plan more effectively for those seasonal variations.

Detection of Opportunities and Problem Areas

Forecasting systems can also alert owners of small businesses to problems and opportunities. Returning to our Tuesday/Wednesday example, the business owner may realize an opportunity to get creative with marketing. If the business is a restaurant, the owner may decide to issue coupons and advertise specials to encourage more diners to come in on Tuesdays. The reduced business on Tuesday may also alert the business owner to a problem. If Tuesday is the restaurant’s slowest day, it may be because there’s a weekly event on Tuesdays that the owner is competing against for patrons, or because the restaurant is short-staffed on Tuesdays and many potential patrons choose not to wait. There could be many reasons, but forecasting can alert the owner to the existence of a problem and the various solutions he/she could try.

Reduced Costs, Increased Revenues, Increased Employee Morale

In trying to project sales, a business owner can make two very different mistakes – under and over predicting, – each with its own undesirable consequences. Usually, these mistakes occur when the business owner’s forecasts are due largely to “gut” or other subjective means. When an owner under predicts sales, he/she may not order enough inventory or schedule enough staff. As a result, the business may run out of inventory and not be able to fulfill orders, resulting in reduced sales and lower customer satisfaction. The inadequate staffing can also increase waiting times, which also lowers customer satisfaction. When an owner over predicts sales, he/she is likely to order too much inventory and/or schedule too many workers, which results in large quantities of unsold inventory and excessive labor costs. Moreover, there are both carrying and opportunity costs associated with excessive unsold inventory. Also, inaccurate predictions can adversely affect the morale of a business’ labor force. Frequent overstaffing due to over prediction can result in bored employees, while frequent understaffing due to under prediction can lead to burned-out employees. Either way, employee morale takes a hit. With an objective forecasting system in place, small businesses can minimize the impact of both over and under prediction.

Enhanced Business Valuation

Cash flow is the lifeblood of every business, not to mention the driver of their value as going-concerns. When entrepreneurs buy existing businesses, they want to know how much cash their generating. All things equal, those businesses that generate more cash command higher sales prices than those that generate less. In the absence of an objective forecasting system, discovery of a business’ true valuation can become problematic. Buyers may demand a discount on the price of a business to compensate for the lack of sales certainty; sellers would have no concrete way to justify the price they seek. A forecasting system greatly shortens the value discovery process and makes it less cumbersome and subjective.

In addition, lenders often make decisions based on cash flows and valuation. A forecasting system can possibly increase your likelihood of getting a loan, and also the amount of funding you seek.

ForecastEase Takes the Pain Out of Forecasting

Having a system in place to forecast sales can make your business more successful and your life easier, more enjoyable, and richer. With ForecastEase, Analysights examines your sales and builds models that generate forecasts that will help you with your planning so that you can spend more time on the strategic elements of your business. Once the models are developed, we create a simulator in Excel that lets you build scenarios painlessly and effortlessly. And the models – depending on the fluctuations of the business – are durable, not needing to be updated all the time. Click here to learn more about ForecastEase or call Analysights at (847) 895-2565.

Introducing Analysights’ Small Business Solutions

May 11, 2010

I’m pleased to announce that Analysights has developed a line of solutions designed to provide high-quality marketing research services to small businesses at affordable rates. Much like large corporations, small businesses need to forecast sales, analyze and monitor their marketplace, and understand what their customers think and where improvements must be made. However, most small businesses don’t have the budget that larger ones do to get the insights they need.

Now they don’t have to!

Small businesses can now get customized marketing research services for a flat price! Analysights has introduced three lines of small business solutions: SurveySimple, ForecastEase, and PlanPro.

SurveySimple is our small scale survey solution, which includes initial consultation, questionnaire design, survey deployment, data collection for 100 to 300 responses, and analysis and reporting with recommendations. You can choose from a Silver, Gold, or Platinum package, depending on the number of people surveyed and/or the length and complexity of the questionnaire. Find out more about SurveySimple.

ForecastEase is a customized sales forecasting solution for small businesses. We use your past sales data to build a forecast model that will help you predict what sales will be in the both the short- and long-term. We then provide you with an Excel spreadsheet powered with the model, so you need only plug in a few numbers to get estimates of upcoming sales, making it easier for you to schedule employees, order supplies and inventory, and make plans with more certainty. You can have sales forecasts made on a daily, weekly, monthly, or quarterly basis. ForecastEase also has a flat price, depending on the periodic basis chosen. Find out more about ForecastEase.

PlanPro is geared towards any small business or entrepreneur preparing a business or marketing plan. The “Market Analysis” is a critical, but often difficult, section of a business plan to create. Analysights takes the drudgery of the Market Analysis section off your hands. We will consult with you and then research your market, examine industry’s trends, competition, and regulatory environment, develop projections for the next couple of years, and provide you with the findings. For a small fee, we will even write the Market Analysis section of your business plan for you. Find out more about PlanPro.

With Analysights’ Small Business Solutions, the question is no longer a matter of “can you afford to do marketing research,” but of “can you afford not to?”