| 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 | ||||||||||||
| Last Edited: | 01-06-2018 | Project Secretary | |||||||||||
| Creation Date: | 31-05-2018 | 1 | days since last edited | ||||||||||
| Created By | |||||||||||||
| Proect Manager | 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 | ||||||||||||
| Assumptions | |||||||||||||
| 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 | https://www.popularrestorations.com/spreadsheets/restorationworksheetexample.xls | |||||||
| 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 | ||||||||
| Direct labour | Variance Report | ||||||||||||
| Undercarriage, cast steel | $0.00 | Direct Material | Standard price / Unit | Total Cost | Actual Quantity Purchased | Actual Price | Qty Used | Price Variance | |||||
| Front suspension kit | $2,975.00 | Frames | $2,000.00 | $56,000.00 | 46 | $1,217.39 | 46 | 36000 | |||||
| Steering | $2,975.00 | Suspension | $1,250.00 | $35,000.00 | 47 | $744.68 | 47 | 23750 | |||||
| Reline shoes and turn drums | $2,380.00 | Rims | $1,000.00 | $28,000.00 | 46 | $608.70 | 46 | 30141.3 | |||||
| Master and wheel cylinder kits | $1,020.00 | Sandblasting | $700.00 | $19,600.00 | 45 | $435.56 | 45 | 38278.89 | |||||
| Rebuilt fuel pump | $0.00 | Replacement hubcaps | $400.00 | $11,200.00 | 53 | $211.32 | 53 | 48817.92 | |||||
| New gas lines | $1,275.00 | Rechrome | $500.00 | $14,000.00 | 40 | $350.00 | 40 | 42300 | |||||
| Fuel filter | $0.00 | Tires | $4,000.00 | $1,12,000.00 | 44 | $2,545.45 | 44 | -60886.4 | 158401.7541 | ||||
| Rebuild clutch disk | $1,275.00 | ||||||||||||
| Assembly | $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 | |||||||
| Direct Material | Front suspension kit | $85.00 | $5,655.00 | 55 | $102.82 | 55 | -980 | ||||||
| Frames | $2,000.00 | Steering | $85.00 | $5,655.00 | 65 | $87.00 | 65 | -130 | |||||
| Suspension | $1,250.00 | Reline shoes and turn drums | $85.00 | $4,176.00 | 45 | $92.80 | 45 | -351 | |||||
| Rims | $1,000.00 | Master and wheel cylinder kits | $85.00 | $2,349.00 | 25 | $93.96 | 25 | -224 | |||||
| Sandblasting | $700.00 | Rebuilt fuel pump | $85.00 | $2,610.00 | 40 | $65.25 | 40 | 790 | |||||
| Replacement hubcaps | $400.00 | New gas lines | $85.00 | $2,610.00 | 25 | $104.40 | 25 | -485 | |||||
| Rechrome | $500.00 | Fuel filter | $85.00 | $4,785.00 | 60 | $79.75 | 60 | 315 | |||||
| Tires | $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 | ||||||
| Indirect Overhead Cost | Annual cost | ||||||||||||
| Manager’s salaries | $1,750.00 | ||||||||||||
| Workshop lease | $312.50 | ||||||||||||
| Electricity | $50.00 | ||||||||||||
| Water | $87.50 | ||||||||||||
| Plant depriciation | $0.00 | ||||||||||||
| Cleaning products | $50.00 | ||||||||||||
| Waste Disposal | $50.00 | ||||||||||||
| Total Labour Cost | $13,600.00 | ||||||||||||
| Total Material Cost | $9,850.00 | ||||||||||||
| Indirect Overhead Cost | $2,300.00 | ||||||||||||
| Total Cost | $25,750.00 | ||||||||||||
| Profit % | $0.00 | ||||||||||||
| Total Quote | $25,750.00 | ||||||||||||
| Input | |||||||||||||
| Output | |||||||||||||
| Vehicle type | Ford Cortinas | ||||||||||||
| Estimated Quote | $25,750.00 | ||||||||||||
| Job Type | |||||||||||||
| Job 1 | Undercarriage, cast steel | Not Required | |||||||||||
| Job 2 | Front suspension kit | Required | |||||||||||
| Job 3 | Steering | Required | |||||||||||
| Job 4 | Reline shoes and turn drums | Required | |||||||||||
| Job 5 | Master and wheel cylinder kits | Required | |||||||||||
| Job 6 | Rebuilt fuel pump | Not Required | |||||||||||
| Job 7 | New gas lines | Required | Performance | ||||||||||
| Job 8 | Fuel filter | Not Required | Total Cost | $25,750.00 | |||||||||
| Job 9 | Rebuild clutch disk | Required | Total Earning | $25,750.00 | |||||||||
| Job 10 | Assembly | Required | Profit | $0.00 | |||||||||
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
