Regression analysis, service company. (CMA, adapted) Boh Jones owns a catering company that prepares banquets and parties for both individual and business functions throughout the year. Jones’s business is seasonal, with a heavy schedule during the summer months and tire year-end holidays and a light schedule at other times. During peak periods there are extra costs.

One of the major eventsJones’s customers request is a cocktail party. He offers a stan¬

dard cocktail party and has developed the following cost structure on a per-person basis.

Food and beverages $18.00 Labour (0.5 hour X $12.00 per hour) 6.00 Overhead (0.5 hour X $16.80 per hour) 8.40 Total costs per person $32.40 Jones is quite certain about his estimates ofthe food, beverages, and labour costs but is not as comfortable with the overhead estimate. This estimate was based on the actual data for the past 12 months presented here. These data indicate that overhead expenses vary with the direct labour-hours expended. The $16.80 estimate was determined by dividing total overhead expended for the 12 months by total labour-hours.

Month Labour-Hours Overhead Costs January 2,500 $ 66,000 February 2,700 70,800 March 3,000 72,000 April 4,200 76,800 May 7,500 92,400 June 5,500 85,200 July 6,500 88,800 August 4,500 80,400 CHAPTER 10 September 7,000 90,000 October 4,500 81,600 November 3,100 74,400 December 6,500 87,600 Total 57,500 $966,000 Jones has recently become aware of regression analysis. He estimated the following regres¬
sion equation with overhead costs as the dependent variable (y) and labour-hours as the independent variable (X):
y = $57,925 + $4.71X Required 1. Plot the relationship between overhead costs and labour-hours. Draw the regression line and evaluate it using the criteria of economic plausibility, goodness of fit, and slope of the regression line.
2. Using data from the regression analysis, find the variable cost per person for a cocktail party.
3. Bob Jones has been asked to prepare a bid for a 200-person cocktail party to be given next month. Determine the minimum bid price thatJones would be willing to submit to earn a positive contribution margin.
Excel Application For students who wish to practise their spreadsheet skills, the following is a step-by-step approach to creating an Excel spreadsheet to work this problem.
Step-by-Step 1. At the top of a new spreadsheet, create an “Original Data” section for the data provided for Bob Jones Catering. Create columns for “Month,” “Labour-Hours,” and “Overhead Costs”
in the same format as the table of monthly labour hours and overhead costs shown for Bob Jones Catering.
2. Skip two rows, and create a section, “Regression Output.”
3. Skip two more rows, and create a section, “Regression Diagram.”
4. Estimate the regression equation with overhead costs as the dependent variable and labour hours as the independent variable by carrying out the following steps:

(a) click on the tools menu and choose the “Data Analysis” option*;

(b) in the “Data Analysis” dialog box, click on “Regression” and click “OK”;

(c) click in the “Inptit y Range” box so the cursor is in the box, then use your mouse to highlight the cells in the “Overhead Costs” column;

(d) click in the “Input X Range” box so the cursor is in this box, then use your mouse to highlight the cells in the “Labour-Hours” column;

(e) under “Output options” select “Output Range” and click in the “Output Range” box so the cursor is in the box, then use your mouse to highlight a cell in the “Regression Output” section you created in step 4;

(f) click “OK” to close the regression dialog box.
5. After completing step 4, you will have a variety ofregression statistics in your “Regression Output” section. In this output, there is a column, “Coefficients,” and two rows, “Intercept” and “X Variable 1.” The number in the intercept row of the coefficients col¬
umn is your estimate of the intercept; the number in the x variable row of the coefficients column is your estimate of the slope.
6. Use the chart wizard to create a scatterplot ofthe relationship between overhead costs and labour-hours. Draw the regression line through these points by doing the following: (a)
place the pointer on one ofthe data points on the graph and right click on your mouse and choose “Add Trendline”;

(b) in the “Add Trendline” dialog box, choose “Linear” and click “OK.”
7. After completing step 6, Excel will draw the regression line you calculated in step 6 through the data points. Use the resulting regression plot to answer question 1, and use the slope coefficient that you calculated in step 6 to answer question 2.