Question

Jasmine Lee owns a catering company that serves food and beverages at exclusive parties and business functions. Lee’s business is seasonal, with a heavy schedule during the summer months and holidays and a lighter schedule at other times.

One of the major events that Lee’s customers request is a graduation party. She offers a standard graduation party and has estimated the cost per guest for this party as follows:

Food and beverages ................................................	$17.00
Labor (0.5 hour @ $10.00 per hour) ........................	5.00
Overhead (0.5 hour @ $6.38 per hour) ...................	3.19
Total cost per guest.................................................	$25.19

This standard graduation party lasts three hours and Lee hires one worker for every six guests, which is one-half hour of labor per guest. These workers are hired only as needed and are paid only for the hours they actually work.

Lee ordinarily charges $39 per guest. She is confident about her estimates of the costs of food and beverages and labor, but she is not as comfortable with the estimate of overhead cost. The $6.38 overhead cost per labor-hour was determined by dividing total overhead expenses for the last 12 months ($220,500) by total labor-hours (34,560) for the same period.

Monthly data concerning overhead costs and labor-hours for the past 12 months appear below:

Month  Labor Hours Overhead

January	1,500	$11,500
February	1,680	12,500
March	1,800	13,000
April	2,520	16,000
May	2,700	18,000
June	3,300	18,000
July	3,900	24,000
August	4,500	26,500
September	4,200	25,000
October	2,700	17,500
November	1,860	14,000
December	3,900	24,500
Total	34,560	$220,500

Lee has received a request to bid on a 120-guest graduation party to be given next month by a well-known entrepreneur. (The party would last the usual three hours.) She would like to win this contract because the guest list for this charity event includes many prominent individuals that she would like to land as future clients. Lee is confident that these potential customers would be favorably impressed by her company’s service at the event.

Required:

1. Use Excel’s regression analysis tool to determine the variable overhead cost per hour and the total fixed overhead cost per month. Write the total cost formula (equation) for the overhead cost. Also identify the R-square value. Explain what the R-square value tells you about your total cost formula. (If you cannot add-in the Analysis tool pack for some reason, you can find the same information by either using a different statistical package like SAS, SPSS, Minitab or Stata, or you can use individual functions within Excel to determine the slope (Slope function), the Y-intercept (Intercept function), and the R-squared (RSQ function)).

2. Estimate the contribution to profit of a standard 120-guest graduation party if Lee charges her usual price of $39.00 per guest. (In other words, by how much would her overall profit increase? Remember that fixed overhead cost will not be affected by Lee’s accepting this order.) Hint: find the contribution margin in dollars.

3. How low could Lee bid for the upcoming graduation event, in terms of a price per guest, and still not lose money on the event itself? In other words, find breakeven dollars on a per unit basis.

4. The individual who is organizing the upcoming graduation event has indicated that he has already received a bid under $35 per guest from another catering company. Do you think Lee should bid below her normal $39 per guest price for the event? Why or why not? Explain.

Answer 1

Requirement 1: Determine the variable overhead cost per hour, fixed overhead cost per month and R-square value using the slope, intercept and RSQ functions of Excel as follows

Variable overhead cost per hour (slope) = $4.98

Monthly fixed overhead cost (intercept) = $4028.08

The R-square value = 0.97

Total cost formula for the overhead cost is

                                    Y = $4,028.08 + $4.98X

The R-squared value of 0.97 indicates that 97% of the variation in dependent variable of overhead costs is explained by independent variable of direct labor hours.

Requirement 2: Compute contribution to profit as follows

Particulars	Amount	Amount
Sales revenue (120 guests × $39 per guest)		$4,680.00
Variable costs:
Food and beverages (120 guests × $17 per guest)	$2,040.00
Labor (120 guests × 0.50 hour × $10 per guest)	$600.00
Overhead (120 guests × 0.50 hour × $4.98 per guest)	$298.80
Total variable costs		$2,938.80
Contribution to profit		$1,741.20

Requirement 3: Compute break-even dollars per unit as follows

Particulars	Amount
Food and beverages costs	$17.00
Labor costs   (0.50 hour × $10 per guest)	$5.00
Overhead costs (0.50 hour × $4.98 per guest)	$2.49
Minimum price per guest	$24.49

Requirement 4:

If she places the bid at her normal price of $39 per guest, she may lose the contract to her competitor and therefore she should bid for the catering contract at an amount slightly below competitors’ bid of $35 per guest. Any bid price above the minimum price of $24.49 per guest would contribute to the profits of the firm.