Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

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.

                         
Rob’s Classic Cars         
                         
Background                        
The implementation of the project would be of much help to Rob's Classic Cars operations. The project would help in determining the necessary cost needed to modify and restore valuable cars. The project model would, therefore, give an allowance for testing different models of vehicles as well as different customers. This is because cost estimations would have been made clear in the project management.         
       
       
       
       
                         
    Name Student Number Position (Role)   MRC        
    Latika Dalal   Project Manager                
    Gourav    Statistical Analyst                
    Kirandeep Pandher   Project Sponsor                
01-06-2018       Project Secretary                
31-05-2018 1 days since last edited                  
Created By                        
Latika Dalal                        
                         
      Go to specific page                  
    Part 1 Documentation                  
    Part 2 Data                  
    Part 3 Workings                  
    Part 4 Input_Output                  
                         
                         
                         
                         
                         
                         
                         
How to Use           Limitations            
Only input tab needs to be use and the input cell is marked with "yellow" color.      The model is a simplified version of calculating cost of restoration.            
User can use the dropdown option available and provide necessary information to get a desirable output.   It is build to show the understanding of excel features while designing a model.            
User do not have to do anything expect input tab data entry       The practicality of this model required other factors, which is not considered            
                         
                         
                         
                         
In order to build this model, number of car services, both estimated figure as well as annual figures are assumed. In addition to this, materials unit, individual labbour hours, actual total costs are also assumed.          
         
                         
                         
                         
        Responding to business need              
Rob's Classis Cars specializes in building and restoring classic cars for customers in Ford Cortinas and Ford Escorts. This company was started as a hobby and now has expanded to a growing business with several jobs completed yearly. Rob's Classic Cars value the input of customers. This is the reason why they offer custom modifications and restorations according to the customer's specifications. The business is passionate about restoring classic cars to the customer’s specifications and ensures there is the value given to the customers. The work of restoration involves direct and indirect cost. This plan, therefore, is developed to provide an estimated cost of estimated direct labor and materials and budgeted overhead costs.
                           
      Principle Excel Function or design characteristic  Cell reference(s) and link          
      Understanding and Responding to Need Written under background section in identification page Background          
      Accurate All excel formulas were checked multiple times and excel functions like "IF", "Nested IF", Cell reference have been used to maintain the accuracy accuracy check          
    Usability Instructions, limitations and assumptions clearly specified in the documentation page. Clear labelling throughout How to use          
    Flexibility Data can be easily changed without affecting input/output or working section. Only raw data section needs to be altered under data tab. Data          
    Auditable workings are self explanatory. Cell lebels have been used to make the clarity of calculations done. cell lebel          
    Efficient separate sheet has been used and "next", "back" and "main menu" button have been used to move freely to any other tab button          
                         
                         
                         
Main Menu Next Back       Ford Escorts [units] Ford Cortinas [Units] Ford Escorts [units] Ford Cortinas [Units]      
  Estimated   Total Service [number of cars] 40 40 25 35      
Direct Material Standard price / Unit Indirect Overhead Cost Annual cost   Direct Material         Total Cost    
Frames $2,000.00 Manager’s salaries $70,000.00   Frames 20 25 21 25 $56,000.00    
Suspension $1,250.00 Workshop lease $12,500.00   Suspension 25 25 22 25 $35,000.00    
Rims $1,000.00 Electricity $2,000.00   Steering 20 25 20 26 $28,000.00    
Sandblasting $700.00 Water $3,500.00   Brakes 15 15 20 25 $19,600.00    
Replacement hubcaps $400.00 Plant depriciation $0.00   Cylinder 14 22 25 28 $11,200.00    
Rechrome $500.00 Cleaning products $2,000.00   Fuel filter 15 15 20 20 $14,000.00    
Tires $4,000.00 Waste Disposal $2,000.00   Transmission rebuild kit 18 16 18 26 $1,12,000.00    
                         
Direct labour Standard Rate/Hour Ford Escorts Ford Cortinas Total Cost Actual Labour Hours              
Undercarriage, cast steel $85.00 12 12 $2,088.00 18              
Front suspension kit $85.00 30 35 $5,655.00 55   source          
Steering $85.00 30 35 $5,655.00 65  
Reline shoes and turn drums $85.00 20 28 $4,176.00 45              
Master and wheel cylinder kits $85.00 15 12 $2,349.00 25              
Rebuilt fuel pump $85.00 15 15 $2,610.00 40              
New gas lines $85.00 15 15 $2,610.00 25              
Fuel filter $85.00 20 35 $4,785.00 60              
Rebuild clutch disk $85.00 20 15 $3,045.00 35              
Assembly $85.00 15 20 $3,045.00 40              
                         
                         
            Variance Report        
$0.00     Direct Material Standard price / Unit Total Cost Actual Quantity Purchased Actual Price Qty Used Price Variance      
$2,975.00     Frames $2,000.00 $56,000.00 46 $1,217.39 46 36000      
$2,975.00     Suspension $1,250.00 $35,000.00 47 $744.68 47 23750      
$2,380.00     Rims $1,000.00 $28,000.00 46 $608.70 46 30141.3      
$1,020.00     Sandblasting $700.00 $19,600.00 45 $435.56 45 38278.89      
$0.00     Replacement hubcaps $400.00 $11,200.00 53 $211.32 53 48817.92      
$1,275.00     Rechrome $500.00 $14,000.00 40 $350.00 40 42300      
$0.00     Tires $4,000.00 $1,12,000.00 44 $2,545.45 44 -60886.4 158401.7541    
$1,275.00                        
$1,700.00     Direct labour Standard Rate/Hour Total Cost Actual Labour Hours Actual rate per hour Labour hour Used Price Variance      
      Undercarriage, cast steel $85.00 $2,088.00 18 $116.00 18 -558      
      Front suspension kit $85.00 $5,655.00 55 $102.82 55 -980      
$2,000.00     Steering $85.00 $5,655.00 65 $87.00 65 -130      
$1,250.00     Reline shoes and turn drums $85.00 $4,176.00 45 $92.80 45 -351      
$1,000.00     Master and wheel cylinder kits $85.00 $2,349.00 25 $93.96 25 -224      
$700.00     Rebuilt fuel pump $85.00 $2,610.00 40 $65.25 40 790      
$400.00     New gas lines $85.00 $2,610.00 25 $104.40 25 -485      
$500.00     Fuel filter $85.00 $4,785.00 60 $79.75 60 315      
$4,000.00     Rebuild clutch disk $85.00 $3,045.00 35 $87.00 35 -70      
      Assembly $85.00 $3,045.00 40 $76.13 40 355 -1338    
Annual cost                        
$1,750.00                        
$312.50                        
$50.00                        
$87.50                        
$0.00                        
$50.00                        
$50.00                        
                         
$13,600.00                        
$9,850.00                      
$2,300.00                        
$25,750.00                        
                         
$0.00                        
$25,750.00                        
                         
Input                      
          Output            
  Ford Cortinas                      
          Estimated Quote $25,750.00            
                         
Undercarriage, cast steel Not Required                      
Front suspension kit Required                      
Steering Required                      
Reline shoes and turn drums Required                      
Master and wheel cylinder kits Required                      
Rebuilt fuel pump Not Required                      
New gas lines Required       Performance            
Fuel filter Not Required       Total Cost $25,750.00            
Rebuild clutch disk Required       Total Earning $25,750.00            
Assembly Required       Profit $0.00            
                         
                         
                         
                         
Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Vehicle Restoration Job Cost Calculation Model Spreadsheet. Retrieved from https://myassignmenthelp.com/free-samples/acc610-business-scenario-and-instructions/costs-of-vehicle-restoration-jobs.html.

"Vehicle Restoration Job Cost Calculation Model Spreadsheet." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/acc610-business-scenario-and-instructions/costs-of-vehicle-restoration-jobs.html.

My Assignment Help (2020) Vehicle Restoration Job Cost Calculation Model Spreadsheet [Online]. Available from: https://myassignmenthelp.com/free-samples/acc610-business-scenario-and-instructions/costs-of-vehicle-restoration-jobs.html
[Accessed 19 May 2024].

My Assignment Help. 'Vehicle Restoration Job Cost Calculation Model Spreadsheet' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/acc610-business-scenario-and-instructions/costs-of-vehicle-restoration-jobs.html> accessed 19 May 2024.

My Assignment Help. Vehicle Restoration Job Cost Calculation Model Spreadsheet [Internet]. My Assignment Help. 2020 [cited 19 May 2024]. Available from: https://myassignmenthelp.com/free-samples/acc610-business-scenario-and-instructions/costs-of-vehicle-restoration-jobs.html.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
Whatsapp
callback
sales
sales chat
Whatsapp
callback
sales chat
close