 |
Food & Beverage - Keep It Simple Regression - By Joe Dunbar |
|
Simple linear regression is an excellent analysis tool for sales forecasting, budgeting and cost efficiency studies. Although the name sounds inviting to many, the number of restaurant operators taking advantage of the power of regression is too small. Today, spreadsheet software performs all the complex calculations required to determine the least square coefficients. |
In layman's terms, regression analysis is the process of finding the best straight line through a scatter of data points. Once you take the time to graph your data points, a person with a sharp eye can closely approximate the best fit. The data points in simple regression are plotted on an X-Y graph. The Y axis is the dependent variable and represents the information you are forecasting or analyzing in your model. On the other hand, the X axis contains independent variables.
You may want to plot labor cost as a function of sales. In this example, labor cost depends on sales and is our dependent variable. We would use the Y axis for the labor cost values. Sales would be plotted using the X axis. Most labor cost curves would slope upwards to the right. As our sales increase on the X axis, the labor cost climbs higher. Why would anyone take the time to plot their labor cost as a function of sales? Isn't simple percentage analysis easier and better suited to management meeting discussions?
I believe understanding your fixed labor component is a critical piece of information for any operator. Regression allows you to see both the managerial effectiveness (fixed labor cost) and the staff productivity (slope of the line).
The common formula for the line: Y = a + bX where Y would be our labor cost estimate, a would be our fixed labor expense and b would be the % of sales represented by our variable labor cost. This is a fairly powerful tool and the model requires very little work. A simple list of data points is all you need to feed Excel (Week, Sales, Labor Cost). The formula for calculating the least-square coefficients looks complex and contains Greek alphabet symbols. In reality, its all a matter of finding the best line which a person with a sharp eye can approximate.
Rather than worrying about the mechanics of the formula, trust your eye to show you if the spreadsheet formula worked correctly. Excel has several built-in functions to handle linear regression estimates. The descriptions below are taken directly from Excel's help utility:
INTERCEPT function
Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. The intercept point is based on a best-fit regression line plotted through the known x-values and known y-values. Use the INTERCEPT function when you want to determine the value of the dependent variable when the independent variable is 0 (zero).
SLOPE function
Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line.
The INTERCEPT function will answer our fixed labor cost question and the SLOPE function will answer the % of sales spent on variable labor.
Note: If you find discussions like this interesting, we have a community of members (by invitation only) on the NING social network site. If you would like to join my Food Cost Control Network for the coming year, the cost is $100. Please visit my Food Cost Control blog to sign up. The membership link is on the sidebar (left-hand side of the blog).
Joe Dunbar
Dunbar Associates
P.O. Box 579
Fairfax, VA 22038-0579
800-949-3295
http://www.joedunbar.com
jdunbar401@aol.com
|
|
|
 |
|
 |
|
|
|
Post Release
Get Content
Email News Brief
Saved Articles
Advertising
Handheld
XML/RSS
PDF this article
Post News