Using activity analysis, Arnoldson Company has identified the appropriate cost driver for maintenance costs in a factory as the number of machine hours. The maintenance costs have been observed as follows within the relevant range of 5,000 to 8,000 machine-hours.
Month Maintenance Cost Machine Hours
January $7,900 5,600
February $8,500 7,100
March $7,400 5,000
April $8,200 6,500
May $9,100 7,300
June $9,800 8,000
July $7,800 6,200
Required in an Excel spreadsheet:
1. Estimate the cost function using the high-low method. TC = FC + VC *X
2. If you were going to use the visual-fit method to estimate the cost function, what steps will you take?
3. Assume that Arnoldson Company would consider outsourcing their maintenance management to an outside contractor, who has exactly the same costs.
a. The contractor will charge $1.50 per hour based on the recorded machine hours. What is the contractor's monthly breakeven point in machine hours and in dollars, based on the above cost function?
b. Another contractor (Contractor B) has put in a bid for the work. He proposes a flat fee of $5000 per month plus $1.00 per machine hour. Is this a better offer?
c. Contractor C proposes to do the work for a flat fee of $10000.
d. You are in charge of maintenance for Arnoldson Company. Your boss is getting impatient and wants to cut costs. He is expecting an update of your various proposals from contractors and is awaiting a recommendation from you. Explain your recommendation in a brief narrative format on your spreadsheet.