Using Excel to Solve Cost Accounting Problem

Data

BHD

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

30

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 (i) 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.