Vehicle Restoration Job Cost Calculation Model Spreadsheet

Required

Rob’s Classic Cars builds and restores classic cars for customers, specialising in Ford Escorts and Ford Cortinas. What began as a hobby has expanded into a growing business with multiple restoration jobs completed each year. While the business is passionate about restoring classic cars to their original specifications, Rob’s Classic Cars, values customer input and will restore to individual specifications and custom modifications.

The job costs of each vehicle depend on the unique combination of vehicle type (make & model), materials, modifications, and labour costs. When quoting a price for a restoration job, Rob’s Classic Cars, assigns direct costs to the job including estimated direct labour hours and direct materials. The business assigns indirect costs (production overhead) based on a single cost driver, direct labour hours, on the view that the work is highly labour-intensive.

Some of the indirect (overhead) costs incurred by Rob’s Classic Cars throughout the year include: manager’s salaries, workshop lease, electricity, water, plant depreciation, indirect materials such as cleaning products, waste disposal etc.

Rob’s Classic Cars are concerned that they have not been quoting accurately AND covering all their production overhead costs in their quotes. The client is requesting that you create a Model Spreadsheet to accurately identify the job costs of restoration jobs commissioned by their customers.

Required:

Create a Model Spreadsheet to identify production (job) costs of vehicle restoration jobs.

Direct labour and materials

At time of quote, estimated direct labour costs are calculated based on an estimate of direct labour hours and an actual hourly labour rate of $85/hr. (In your data section, it is okay to assume all direct labour hours have the same rate eg mechanic, spray painter, auto-electrician hours.) Estimated labour costs to be compared with actual labour costs at completion of job (see below).

Similarly, at time of quote, estimated direct material costs are assigned to the job (and should be compared with actual direct material costs at job completion.) (In your data section, it is best to identify distinct categories of direct costs as different models will have different materials eg chassis materials; mechanical parts; paint; technology extras).

Indirect (Production) Overhead

The business assigns indirect overhead costs based on a single cost driver, direct labour hours. Again, at the time of quoting jobs, the indirect overhead costs are estimated (or budgeted, loosely based on previous year’s figures) and so the allocation of indirect costs is also an estimate and should be compared with the actual indirect overhead figures at the end of the year.

Therefore, the Model Spreadsheet should provide an estimated Job Cost for commissioned jobs based on estimated direct labour and materials and budgeted overhead costs. It should also identify the actual Job Cost based on actual direct labour and materials and actual overhead costs when these are identified at the end of the job/year AND the difference between the two.

The Model should have allowance to test different vehicle models and different customer jobs over time. You are encouraged to keep the Model relatively simple focussing on the marking criteria principles of a good spreadsheet: accuracy, flexibility, efficiency, auditability and usability. Remember, for the spreadsheet you are required to use some specific and additional EXCEL functions as well as sourcing some data externally from the Internet, and identifying any data assumptions.

In your Presentation, you should demonstrate how the Model fulfils the brief as identified by your client as well as areas you have shown extra initiative and insight in creating an effective and adaptable Model. As well, highlight any ways that you might improve upon the assumptions made by the business in assigning costs.