V or H Look Up ( Accounting Job)

BIRMINGHAM CITY BUSINESS SCHOOL

UNDERGRADUATE DEGREES COURSEWORK FRONT SHEET

MODULE TITLE: Accounting & Finance

MODULE CODE: ACC4004

MODULE LEADER: Rachel Homer

ISSUE DATE: 31st October 2016

HAND IN DATE: 16th December 2016; 12 noon via Moodle

HAND BACK DATE: 23rd January 2017

Learning outcomes and pass attainment level:

Demonstrate an use of accounting information for decision making purposes

This coursework represents 20% of the module final mark.

Page 2 of 5

COURSEWORK INSTRUCTIONS

1. Your assessment should be submitted via Moodle no later than 12 noon on 16th December 2016. Where a student submits his or her first attempt at an assessment up to 2 hours after the

published deadline the mark that will be awarded will be reduced by 10%. For example, a mark

of 60% will be reduced by 6% so that the mark that the student will receive is 54%. Where the

process of calculating the mark results in a mark which is not a whole number, that mark will be

expressed to one decimal place.

The 10% reduction in the mark will not be applied in the following two cases:

 the mark is below the pass mark for the assessment. In this case the mark achieved by

the student will stand

 where a deduction of 10% will reduce the mark from a pass to a fail. In this case the

mark awarded will be 40%.

Where a student submits his or her first attempt at an assessment between two hours and 5

working days after the published deadline the maximum mark available will be 40%. Work

submitted more than 5 working days after the published deadline will not be marked and the

student will be deemed to have failed an attempt at the assessment.

2. You must present your answer as an Excel spreadsheet.

3. The work must be your own. There is no objection to students discussing the content and approaches to be adopted with each other but the final submission you make must be 100% your OWN work. Any work that is found to be authored by another person will be reported as plagiarism.

4. No specific layout is required, you should however, ensure that the presentation of your information is clear for end users.

PLAGIARISM WILL BE REPORTED.

THIS MAY RESULT IN FAILURE AND DELAY COMPLETION OF THE MODULE.

Page 3 of 5

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:

DUO TUB

SQUARE TUB

ROUND TUB

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.

Page 4 of 5

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) (45 marks)

2. Use V and/or H look up and/or IF statements where appropriate. (9 marks)

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. (5 marks)

4. Use data validation to enable the end user to select a particular hot tub and view the

specific information for that hot tub. (5 marks)

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. (9 marks)

6. Calculate the profit per unit for each type of hot tub. (9 marks)

7. Calculate the profit mark up for each type of hot tub. (9 marks)

8. Calculate the profit margin for each type of hot tub. (9 marks)

(Total: 100 marks)

Page 5 of 5

Bubbles Hot Tubs

Student ID:

Marks available

Marks Awarded

Is the work that of the student? Yes – mark following the mark scheme provided No – report as plagiarism

Calculation of information using appropriate excel functionality

54%

Presentation of information using appropriate excel functionality

10%

Calculations using appropriate formulae within excel

36%

Total assessment mark 100%