Accounting & Finance Assignment-
Learning outcomes and pass attainment level:
Demonstrate a use of accounting information for decision making purposes.
Teddington Traditional Toys-
Teddington Traditional Toys manufactures and sells traditional toys in both the UK and Europe. At present they manufacture three such toys:
The selling prices for each of the toys are priced competitively.
Selling prices per toy are:
|
Toy
|
Selling price
|
|
Pedal car
|
£40.00
|
|
Teddy bear
|
£10.00
|
|
Building blocks
|
£16.00
|
The manufacturing cost is based on the materials used, labour costs and expenses incurred to produce the toy.
Teddington Traditional Toys manufactures the three toys in distinct cells of the production facility as this saves on set up costs and idle time.
Manufacturing costs for each of the toys is provided below:
|
|
Pedal car
|
Teddy bear
|
Building blocks
|
|
Per toy:
|
|
|
|
|
Material cost
|
£11.25
|
£4.20
|
£7.72
|
|
Labour cost
|
£9.75
|
£3.25
|
£3.75
|
|
Expenses
|
£4.60
|
£0.55
|
£0.82
|
|
|
|
|
|
|
Per month
|
|
|
|
|
Production overheads
|
£4,500
|
£3,600
|
£1,800
|
|
Other overheads
|
£1,500
|
£1,200
|
£800
|
|
Budgeted capacity
|
850
|
10,000
|
2,000
|
Production overheads cover the costs of the production supervisors, line managers, warehouse storage, rent, rates, utility bills and insurance, depreciation of the machines. If actual production exceeds 60% of current budgeted capacity the production overheads will increase by 25%.
The other overhead charge is to cover the advertising, selling and distribution costs as well as the administrative support from the finance and purchasing departments. This cost also covers a membership fee to the British Toy and Hobby Association (BTHA) and the product and public liability insurance.
Required-
Presentation:
1. Use an appropriate and clear layout suitable for the end user of the information presented.
Functions and formulae:
2. Using the information provided in the scenario, you are to produce a single Excel spreadsheet making use of the following MS Excel functions:
i. data validation
ii. logical functions and operators, including the IF statement
iii. V and H lookup
iv. What-if / goal seek
The spreadsheet should clearly show the total income and the total separately each item of cost and total profit.
3. At the budgeted capacity level, for each of the toys, calculate the mark-up and margin per toy.
4. Calculate the break-even quantity for each of the toys.
5. Calculate the margin of safety for each of the toys.