a manufacturing depot where keeping a clean working environment is important. Cleaning the depot is the responsibility of the maintenance department. Two of the resources needed to clean the depot are labor and cleaning supplies. The cost driver for both resources is square feet cleaned. Depot cleaning laborers are paid the same wages regardless of the number of times the depot is cleaned. Cleaning supplies is a variable cost. The 40,000 square foot plant is thoroughly cleaned from four to eight times a month depending on the level and stage of manufacturing. For the most recent month, June, the depot was cleaned four times. The June cost of labor was $28,000 and cleaning supplies used cost $7,500. The manufacturing schedule for the next quarter (July through September) indicates that the depot will need to be cleaned five, six, and eight times respectively In Excel 1. Prepare a table that shows how labor cost, cleaning supplies cost, total cost, and total cost per square feet cleaned changes in response to the square feet cleaned. What is the predicted total cost of depot cleaning for the next quarter? 2. Suppose Harpers can hire an outside cleaning company to clean the depot as needed. The charge rate for cleaning is $6,100 per depot cleaning. If the outside cleaning company is hired, Harpers can lay off the workers who are now cleaning the depot and will spend nothing for cleaning supplies. Will Harpers save money with the outside cleaning company over the next quarter? Prepare a schedule that supports your answer. 3. Create a line chart that reflects the Total Cleaning Costs and Square Feet (Times Cleaned) for the two options (Harpers vs Outside Company). Must be properly labeled and on one chart. 4. At what level of activity (SqFt Cleaned) will the two options be equal?

the attached document has the answer for question 1, 2, and four. I just need help with question number 3. How to make a line chart for these 2 options with excel

Expert Answer

anushaganswered this Was this answer helpful? 0 0 2,633 answers

Case Study Fixed and Variable Cost Behavior Harpers Inc. has a manufacturing depot where keeping a clean working environment is important. Cleaning the depot is the responsibility of the maintenance department. Two of the resources needed to clean the depot are labor and cleaning supplies. The cost driver for both resources is square feet cleaned. Depot cleaning laborers are paid the same wages regardless of the number of times the depot is cleaned. Cleaning supplies is a variable cost. The 40,000 square foot plant is thoroughly cleaned from four to eight times a month depending on the level and stage of manufacturing. For the most recent month, June, the depot was cleaned four times. The June cost of labor was $28,000 and cleaning supplies used cost $7,500. The manufacturing schedule for the next quarter (July through September) indicates that the depot will need to be cleaned five, six, and eight times respectively In Excel 1. Prepare a table that shows how labor cost, cleaning supplies cost, total cost, and total cost per square feet cleaned changes in response to the square feet cleaned. What is the predicted total cost of depot cleaning for the next quarter? 2. Suppose Harpers can hire an outside cleaning company to clean the depot as needed. The charge rate for cleaning is $6,100 per depot cleaning. If the outside cleaning company is hired, Harpers can lay off the workers who are now cleaning the depot and will spend nothing for cleaning supplies. Will Harpers save money with the outside cleaning company over the next quarter? Prepare a schedule that supports your answer. 3. Create a line chart that reflects the Total Cleaning Costs and Square Feet (Times Cleaned) for the two options (Harpers vs Outside Company). Must be properly labeled and on one chart. 4. At what level of activity (SqFt Cleaned) will the two options be equal? Number of times depot is cleaned 4 5 6 8 $ $ $ $ Labor cost 28,000 28,000 28,000 28,000 Cost of cleaning supplies 7,500 9,375 11,250 15,000 Total cost 35,500 37,375 39,250 43,000 Number of sq. feet cleaned 160,00 200,00 240,00 320,00 Total cost per sq. feet cleaned 0 0 0 0 0.2219 0.1869 0.1635 0.1344 2. Next quarter: Analysis as to whether depot cleaning should be outsourced: Month July August Septemb er Number of times depot is cleaned 5 times 6 times 8 times Total Costs $ $ $ Harpers Inc 37,375 39,250 43,000 Outside Company 30,500 36,600 48,800 Saving ( loss) in outsourcing 6,875 2,650 (5,800) Quarte r $ 3,725 Yes, Harpers Inc. will save $ 3,725 over the next quarter if cleaning of the depot is outsourced to Outside Company. 4. The two options will be equal at Fixed cost / Contribution margin per cleaning = 28,000 / ( 6,100 1,875*) = 6.62722 times or 6.62722 x 40,000 = 265,089 sq. ft. * Cost of cleaning supplies per cleaning = $ 7,500 / 4 = $ 1,875