Reference no: EM13566914

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.