Could you please answer this question ?

ACC 309: EXCEL Project: THE DAILY GRIND CASE

Spring 2016: Due April 21

This case is designed to enhance your ability to use EXCEL in a consulting environment. Complete the case using EXCEL. Every required computation should have the correct cell reference or formula needed. DO NOT JUST COMPUTE THE AMOUNT NEEDED AND INPUT. The ideal solution would have a data table and references in your cells used for solutions for case requirements. Your solution should be complete, correct, and prepared in good form. No partial credit will be awarded for an incomplete and/or inaccurate solution. When complete email me the completed solution as an attachment to <..n do not submit through blackboard.>

The Daily Grind

Twelve months ago, David opened a coffee shop, The Daily Grind, in Mercy Hospital?s former gift shop. David was confident that he had the knowledge to make a success of this new business. He produced a quality product that people needed, had priced the product to be very competitive, and had a great location in a high-traffic area of the hospital.

Material Costs

The Daily Grind uses a specialty brand of Kona coffee beans costing $8 per pound. Each pound of coffee beans produces 256 ounces of coffee. Coffee is sold in three sizes: a small cup (8 ounces), a medium cup (12 ounces) and a large cup (16 ounces).

The cups needed to serve the coffee cost $.05 for the small, $.06 for the medium and $.07 for the large. Lids cost $.03 per cup are the same regardless of the size of the cup. Sleeves cost an additional $.04 per cup. On average, sugar and cream cost $.02 per cup for small, $.03 for medium and $.04 for large cups.

Labor Costs

The Daily Grind is open 12 hours per day, 7 days a week (365 days per year), and is staffed with three employees during the morning shift (7-11), two employees from 11 until 3, and three employees from 3 to 7. Labor is a fixed cost, because the employees are paid regardless of whether coffee is sold. David works 60 hours per week, on average, and is paid a salary of $30,000 during the first year of operations. Fringe benefits for David, including health insurance and payroll taxes, accounted for an additional $10,000 of costs for the company. Part-time employees work an average of 24 hours each week and are paid $9 per hour. Payroll taxes and other costs average about $1.00 per hour for part-time employees. As shown in the following table, part-time employees worked from 656 hours to 727 hours each month.

MONTH	PART-TIME Employee Labor Hours
January	722
February	656
March	727
April	705
May	727
June	705
July	727
August	727
September	705
October	727
November	705
December	727

Overhead Costs

During the first year of operations, the hospital charged rent of $2,000 per month. As part of the rental cost, the hospital provided furniture and fixtures for the shop, as well as nightly cleaning services. David leased a drip coffeemaker, refrigerator, coffee grinder, scale, and cash register for $150 per month. David paid directly for his utilities (electricity and water). The costs of electricity include the costs of heating and cooling the shop, as well as the cost of running the electric appliances (refrigerator, coffeemaker, etc.). For the first 12 months of operations, utility expenses were as follows:

MONTH	Utility Expense
January	$472
February	$510
March	$524
April	$460
May	$440
June	$460
July	$452
August	$430
September	$535
October	$570
November	$580
December	$600
Total	$6,033

Selling and Administrative Costs

David incurred $200 a month in accounting fees and spent $500 on various promotional and advertising materials during the year. He also paid $1,000 for liability insurance.

Sales Revenue

During the first year of operations, David set the shop?s prices to be slightly lower than their competitors?. The Daily Grind sells a small coffee for $1.25, medium for $1.65 and large for $1.95. Sales revenue for the first year was as follows:

MONTH	SALES IN CUPS OF COFFEE
January	9,300 cups*
February	9,800
March	10,850
April	9,500
May	9,300
June	9,000
July	8,800
August	8,600
September	11,000
October	11.620
November	12,000
December	12,400

*Coffee sales average 10% small, 50% medium and 40% large.

Case Requirements:

• Calculate the cost of coffee beans per ounce of coffee sold.
• Calculate the cost of cups, lids, sleeves, cream and sugar per each size of coffee sold, and in total.
• Calculate the total labor costs for the year.
• Prepare an income statement for the last year. You can assume that there are no inventories on hand at the end of the year.
• Determine whether the costs incurred are fixed, variable or mixed based on sales volume.
• Use regression analysis to calculate the monthly fixed and variable components of the utility expenses. Use cups of coffee sold as the independent variable and utility expense as the dependent variable. Round your answers to two decimal places.
• Discuss any other factors that may impact the volume of coffee sold during the year.
• Calculate the contribution margin for each product and the weighted average contribution margin.
• Assume the sales mix given in the problem. What is the breakeven point in terms of the total number of cups of coffee sold based on your weighted average contribution margin from number 8 above. How many small, medium and large cups based on your breakeven computation.
• David is considering adding a new 20 ounce product for the coming year and discontinuing sales of the small cup. The new cup, lid and sleeve cost the same as the present large cup. The cream and sugar cost will rise to $.06 per cup. The selling price of the new product will be $2.40. David anticipates that the new sales mix would be 50% for the medium, 30% for the large and 20% for the new 20 ounce size. Assume the material, labor and overhead costs remain the same. How would this change in sales mix affect the company?s breakeven point?
• David would like to increase sales in the second year so that he can raise his salary to $50,000 (not including $15,000 of fringe benefits) while reducing his workload to 40 hours per week with two paid weeks of vacation during the year. Reducing his workload will require increasing the number of hours worked by part-time employees by 1,080 hours for the year. Assume the introduction of the extra-large cup and the new sales mix as discussed in requirement 10. What level of annual sales would be required in order for David to reach his goals?
• Prepare a graph for the utility expense as a line graph. Prepare a graph for the monthly sales as a bar graph. Insert these into your excel worksheet.
• Write a memo to David and discuss whether you think he will be able to reach his goal in the second year. Give him some alternatives (what if?s) that might move him closer to his goal of less hours and more pay. In other words, what salary and work load would allow the business to show a profit? Be very specific about what would work or not work and why.

ACC 309: EXCEL Project: THE DAILY GRIND CASE Spring 2016: Due April 21 This case is designed to enhance your ability to use EXCEL in a consulting environment. Complete the case using EXCEL. Every required computation should have the correct cell reference or formula needed. DO NOT JUST COMPUTE THE AMOUNT NEEDED AND INPUT. The ideal solution would have a data table and references in your cells used for solutions for case requirements. Your solution should be complete, correct, and prepared in good form. No partial credit will be awarded for an incomplete and/or inaccurate solution. When complete email me the completed solution as an attachment to steven.r.jackson@usm.edu. Do not submit through blackboard. The Daily Grind Twelve months ago, David opened a coffee shop, The Daily Grind, in Mercy Hospital's former gift shop. David was confident that he had the knowledge to make a success of this new business. He produced a quality product that people needed, had priced the product to be very competitive, and had a great location in a high-traffic area of the hospital. Material Costs The Daily Grind uses a specialty brand of Kona coffee beans costing $8 per pound. Each pound of coffee beans produces 256 ounces of coffee. Coffee is sold in three sizes: a small cup (8 ounces), a medium cup (12 ounces) and a large cup (16 ounces). The cups needed to serve the coffee cost $.05 for the small, $.06 for the medium and $.07 for the large. Lids cost $.03 per cup are the same regardless of the size of the cup. Sleeves cost an additional $.04 per cup. On average, sugar and cream cost $.02 per cup for small, $.03 for medium and $.04 for large cups. Labor Costs The Daily Grind is open 12 hours per day, 7 days a week (365 days per year), and is staffed with three employees during the morning shift (7-11), two employees from 11 until 3, and three employees from 3 to 7. Labor is a fixed cost, because the employees are paid regardless of whether coffee is sold. David works 60 hours per week, on average, and is paid a salary of $30,000 during the first year of operations. Fringe benefits for David, including health insurance and payroll taxes, accounted for an additional $10,000 of costs for the company. Part-time employees work an average of 24 hours each week and are paid $9 per hour. Payroll taxes and other costs average about $1.00 per hour for part-time employees. As shown in the following table, part-time employees worked from 656 hours to 727 hours each month. MONTH January February March April May June July August Septembe r October November December PART-TIME Employee Labor Hours 722 656 727 705 727 705 727 727 705 727 705 727 Overhead Costs During the first year of operations, the hospital charged rent of $2,000 per month. As part of the rental cost, the hospital provided furniture and fixtures for the shop, as well as nightly cleaning services. David leased a drip coffeemaker, refrigerator, coffee grinder, scale, and cash register for $150 per month. David paid directly for his utilities (electricity and water). The costs of electricity include the costs of heating and cooling the shop, as well as the cost of running the electric appliances (refrigerator, coffeemaker, etc.). For the first 12 months of operations, utility expenses were as follows: MONTH January February March April May June July August Septembe r October Utility Expense $472 $510 $524 $460 $440 $460 $452 $430 $535 $570 November December Total $580 $600 $6,033 Selling and Administrative Costs David incurred $200 a month in accounting fees and spent $500 on various promotional and advertising materials during the year. He also paid $1,000 for liability insurance. Sales Revenue During the first year of operations, David set the shop's prices to be slightly lower than their competitors'. The Daily Grind sells a small coffee for $1.25, medium for $1.65 and large for $1.95. Sales revenue for the first year was as follows: MONTH January February March April May June July August Septembe r October November December SALES IN CUPS OF COFFEE 9,300 cups* 9,800 10,850 9,500 9,300 9,000 8,800 8,600 11,000 11.620 12,000 12,400 *Coffee sales average 10% small, 50% medium and 40% large. Case Requirements: 1) Calculate the cost of coffee beans per ounce of coffee sold. 2) Calculate the cost of cups, lids, sleeves, cream and sugar per each size of coffee sold, and in total. 3) Calculate the total labor costs for the year. 4) Prepare an income statement for the last year. You can assume that there are no inventories on hand at the end of the year. 5) Determine whether the costs incurred are fixed, variable or mixed based on sales volume. 6) Use regression analysis to calculate the monthly fixed and variable components of the utility expenses. Use cups of coffee sold as the independent variable and utility expense as the dependent variable. Round your answers to two decimal places. 7) Discuss any other factors that may impact the volume of coffee sold during the year. 8) Calculate the contribution margin for each product and the weighted average contribution margin. 9) Assume the sales mix given in the problem. What is the breakeven point in terms of the total number of cups of coffee sold based on your weighted average contribution margin from number 8 above. How many small, medium and large cups based on your breakeven computation. 10) David is considering adding a new 20 ounce product for the coming year and discontinuing sales of the small cup. The new cup, lid and sleeve cost the same as the present large cup. The cream and sugar cost will rise to $.06 per cup. The selling price of the new product will be $2.40. David anticipates that the new sales mix would be 50% for the medium, 30% for the large and 20% for the new 20 ounce size. Assume the material, labor and overhead costs remain the same. How would this change in sales mix affect the company's breakeven point? 11) David would like to increase sales in the second year so that he can raise his salary to $50,000 (not including $15,000 of fringe benefits) while reducing his workload to 40 hours per week with two paid weeks of vacation during the year. Reducing his workload will require increasing the number of hours worked by part-time employees by 1,080 hours for the year. Assume the introduction of the extra-large cup and the new sales mix as discussed in requirement 10. What level of annual sales would be required in order for David to reach his goals? 12) Prepare a graph for the utility expense as a line graph. Prepare a graph for the monthly sales as a bar graph. Insert these into your excel worksheet. 13) Write a memo to David and discuss whether you think he will be able to reach his goal in the second year. Give him some alternatives (what if's) that might move him closer to his goal of less hours and more pay. In other words, what salary and work load would allow the business to show a profit? Be very specific about what would work or not work and why