Question

Unformatted Excel Assignment Cost Behavior (30 points) Benny Bear owns a catering company that serves food and beverages at exclusive parties and business functions. Benny'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 Benny's customers request is a cocktail party. He offers a standard cocktail party and has estimated the cost per guest for this party as follows Food and beverages Labor (0.5 hours @ 10.00 per hour) $5.00 Overhead (0.5 hours 18.63 per hour) $9.32 Total cost per guest $17.00 $31.32 This standard cocktail party lasts three hours and Benny 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. Benny ordinarily charges $45 per guest. He is confident about his estimates of the costs of food and beverages and labor, but is not as comfortable with the estimate of overhead cost The $18.63 overhead cost per labor-hour was determined by dividing total overhead expenses for the last 12 months by total labor-hours for the same period. Monthly data concerning overhead costs and labor-hours appear below Labor Overhead hours expenses 1,500 S44,000 1,680 S47,200 1,800 $48,000 2,520 $51,200 2,700 $53,600 3,300$56,800 3,900$59,200 4,500 S61,600 4,200 $60,000 2,700 S54,400 1,860 $49,600 3,900 $58,400 34,560 $644,000 Month January February March April May June July August September October November December Total Benny has received a request to bid on a 120-guest fund-raising cocktail party to be given next month by an important local charity. The party would last the usual three hours. He would like to win this contract because the guest list for this charity event includes many prominent individuals that he would like to land as future clients. Benny is confident that these potential customers would be favorably impressed by his company's services at the charity event. Required Using the linear regression [Excel Spreadsheet. Data Analysis → Regression], estimate the contribution margin [- revenue variable cost] of a standard 120-guest cocktail party if Benny charges his usual price of $45 per guest. In other words, how much would his overall profit increase by serving the 120-guest cocktail party? INSTRUCTION: 1. Given the information above, encode the raw data in a tab. You may name the tab "Data". In addition, you should type all other relevant data in this ato 2. With the encoded data, generate the regression result on a new tab. You may name the tab "Regression result". 3. With the regression result and other relevant data, calculate the required contribution margin on a new tab. You may name the tab "Answer".

Answer 1

nsto esscanneanneh L rre ารใ Pee ฝ [TG.tt.60! :s CamScanner

to ms uy Ca ow melod 300o 17,60ol/35s0 uvs x Vavfa 6,600- usoo x5-86) 61,600- 26,370 Scanned wih 35,230, CamScanner

Slep 2 ste Tolal vaxfablas ence Omou Scarned w CamScanner