BHD 30 Using Excel to Solve Cost Accounting Problem Data Cost of Motor Car 11,000 Trade-in price after 3 years or 60,000 kilometres is expected to be 3,000 Maintenance - 6-monthly service costing 120 Spares/ Replacement parts, per 1,000 kilometres 40 Vehicle licence per year Motor Insurance per year 300 Petrol per litre (car uses only Mumtaz) 0.200 Average distance from one litre of petrol: 25 kilometres Tyre replacement after 25,000 kilometres: 4 tyres costing BHD45 each Required (a) Using the data given in the table above, use MS Excel to: (i) Prepare a schedule to be presented to management. The schedule must show the following for 5000, 10000, 15000, 20000, 25000 and 30000 kilometres per year: (1) Total variable cost (2) Total fixed cost (3) Total cost (4) Variable cost per kilometre (5) Fixed cost per kilometre (6) Total cost per kilometre (4 marks) (ii) Plot on a graph (in Excel) the information given in your answer to (1) above for the costs listed in (1), (2), (3) and (6) (2 marks) (iii) Show (using the graphs in (ii) approximate total costs applicable at 18,000 kilometres and 27,000 kilometres and the total cost per mile at each of these mileages. (2 marks) (b) A friend of yours recently read a statement in a cost accounting book which did not appear to make sense. The statement was the more kilometres you travel the cheaper it becomes." Comment briefly on this statement, concluding whether the statement makes sense or not. (2 marks) BHD 30 Using Excel to Solve Cost Accounting Problem Data Cost of Motor Car 11,000 Trade-in price after 3 years or 60,000 kilometres is expected to be 3,000 Maintenance - 6-monthly service costing 120 Spares/ Replacement parts, per 1,000 kilometres 40 Vehicle licence per year Motor Insurance per year 300 Petrol per litre (car uses only Mumtaz) 0.200 Average distance from one litre of petrol: 25 kilometres Tyre replacement after 25,000 kilometres: 4 tyres costing BHD45 each Required (a) Using the data given in the table above, use MS Excel to: (i) Prepare a schedule to be presented to management. The schedule must show the following for 5000, 10000, 15000, 20000, 25000 and 30000 kilometres per year: (1) Total variable cost (2) Total fixed cost (3) Total cost (4) Variable cost per kilometre (5) Fixed cost per kilometre (6) Total cost per kilometre (4 marks) (ii) Plot on a graph (in Excel) the information given in your answer to (1) above for the costs listed in (1), (2), (3) and (6) (2 marks) (iii) Show (using the graphs in (ii) approximate total costs applicable at 18,000 kilometres and 27,000 kilometres and the total cost per mile at each of these mileages. (2 marks) (b) A friend of yours recently read a statement in a cost accounting book which did not appear to make sense. The statement was the more kilometres you travel the cheaper it becomes." Comment briefly on this statement, concluding whether the statement makes sense or not. (2 marks)
