Bubbles Hot Tubs
Bubbles Hot Tubs are a business that manufactures and sells hot tubs to the UK market. At present the business manufactures three different types of hot tubs:
The selling prices for each of the hot tubs are priced competitively. Selling prices per hot tub are:
|
|
Selling price
|
|
DUO
|
£1000.00
|
|
SQUARE
|
£2000.00
|
|
ROUND
|
£1500.00
|
The manufacturing cost is based on the materials used, labour costs and expenses incurred to produce each different type of hot tub.
Bubbles Hot Tubs manufactures the three different hot tubs in separate areas of the production facility as this saves on set up costs and idle time.
The manufacturing costs for the different types of hot tub are provided below:
|
|
DUO
|
SQUARE
|
ROUND
|
|
Variable Costs:
|
|
|
|
|
Material cost per unit
|
£115
|
£400
|
£250
|
|
Labour cost per unit
|
£200
|
£400
|
£300
|
|
Expense cost per unit
|
£150
|
£250
|
£200
|
|
Fixed Costs:
|
|
|
|
|
Production overheads
|
£115000
|
£172500
|
£143000
|
|
Other overheads
|
£20500
|
£34000
|
29000
|
|
Budgeted capacity
|
2000
|
1000
|
1500
|
If actual production exceeds 70% of current budgeted capacity for any tub type, the material cost per tub will reduce by 10% due to supplier discounts. Production overheads cover the costs of the production supervisor, warehouse storage, rent, rates, utility bills and insurance, depreciation of machinery. The other overhead charge is to cover the advertising, selling and distribution costs and the product and public liability insurance.
Required:
Calculation of information using excel functionality
1. Produce an excel spreadsheet which calculates and identifies for each type of hot tub at any activity level;
Total Sales Revenue
Total Material Cost
Total Labour Cost
Total Expense Cost
Production Overheads
Other Overheads Profit / (Loss)
2. Use V and/or H look up and/or IF statements where appropriate. Presentation of calculated information using excel functionality
3. Present information in an appropriate layout so the end user can clearly identify the information above at any activity level for any type of hot tub.
4. Use data validation to enable the end user to select a particular hot tub and view the specific information for that hot tub.
Calculations using appropriate formulae
Using the spreadsheet you have created, set the number of units to the budgeted capacity level for each type of hot tub and calculate the following using appropriate formulae.
5. Calculate the total cost per unit for each type of hot tub.
6. Calculate the profit per unit for each type of hot tub.
7. Calculate the profit mark up for each type of hot tub.
8. Calculate the profit margin for each type of hot tub.