PART 1: Technical competency (Assessable Value 15%)
CASE STUDY USING EXCEL SPREADSHEET
Yosemite Ltd manufactures wardrobes. Your team works in the accounting department and has been tasked with preparing the budget for the next month (Question 1). Your team has also been asked to provide management with some information and advice on kaizen budgeting (Question 2).
Yosemite makes two products:
Premium wardrobes - large wardrobes with mirrors
De Luxe wardrobes - medium sized wardrobes with an innovative shelving solution
Direct manufacturing labour is 3 hours for the premium wardrobe and 5 hours for the De Luxe.
The budgeted direct material usage for each product in 2017 is:
Square metres Premium De Luxe
Mirrors 2 0
Softwood 1 5
Hardwood 4 1
Balsa wood 1 4
Opening direct materials inventory for April 2017 is:
Square metres Premium De Luxe
Mirrors 40 0
Softwood 10 30
Hardwood 100 5
Balsa wood 4 40
Target ending direct materials inventory (April 30) is:
Square metres Premium De Luxe
Mirrors 24 0
Softwood 6 40
Hardwood 80 4
Balsa wood 4 44
Unit cost data is expected to be the same for April as it was in March for labour and all materials except Balsa wood where a change in supplier has resulted in a cost increase of 50%. Detailed cost data is as follows:
March (actual) April (budgeted)
$ $
Mirrors per square metre 160 160
Softwood per square metre 125 125
Hardwood per square metre 12 12
Balsa wood per square metre 16 24
Manufacturing labour cost per hour 30 30
Manufacturing overhead (both variable and fixed) is allocated to each wardrobe on the basis of budgeted direct manufacturing labour hours.
The budgeted variable manufacturing overhead rate for April 2017 is $35 per direct manufacturing labourhour.
The budgeted fixed manufacturing overhead for the month is $42,500. Both variable and fixed manufacturing overhead costs are allocated to each unit of finished goods on the basis of direct manufacturing labour-hours.
Data relating to finished goods inventory for April 2017 are:
Premium De Luxe
Opening Inventory (units) 20 5
Opening Inventory (dollars) $10,840 $4,850
Target Ending Inventory 30 15
Budgeted sales for April 2017 are 740 units of the premium and 390 units of the De Luxe. The budgeted selling prices per unit in April 2017 are $1,020 for the Premium and $1,600 for the De Luxe.
Assume the following in your answer:
• Work-in-process inventories are negligible and ignored.
• Direct materials inventory and finished goods inventory are costed using the FIFO method.
• Unit costs of direct materials purchased and finished goods are constant in April 2017.
REQUIRED:
1) Prepare the following budgets for April 2017:
a) Sales revenue budget (units and $) (2 Marks)
b) Production budget in units (2 Marks)
c) Direct materials usage budget and direct materials purchases budget (both budgets in units and $)
(6 Marks)
d) Direct manufacturing labour budget
(3 Marks)
e) Manufacturing overhead budget
(4 Marks)
f) Ending inventories budget (direct materials and finished goods)
(5 Marks)
g) Cost of goods sold budget (4 Marks)
4 marks are allocated for Excel presentation and use of formulas in Excel.
For each budget, prepare by product (Premium and De Luxe) and in total.
2) Management has also requested your team to give them some understanding on a new aspect of budgeting that has come to their attention. They have outlined a few questions for your team to address:
a) What is kaizen budgeting? (2 Marks)
b) How does kaizen budgeting differ from traditional budgeting? (3 Marks)
c) How could Yosemite Ltd incorporate elements of kaizen budgeting into its current budgeting process? Would you recommend they do so and why or why not? (4 Marks)
4 marks are allocated for presentation, grammar, spelling and appropriate Harvard referencing where needed.
2 marks are allocated for appropriate teamwork as evidenced by your minutes and any other observations made by your lecturer.
Total Technical Mark = 45 marks, weighted to 15% overall
Assignment Marking Sheet (Technical only)
Allocated Marks Marks Awarded Comment
1a
Sales budget in units and revenue
2.00
1b
Production budget
2.00
1c
Materials budget
6.00
1d
Labour budget
3.00
1e
Overhead budget
4.00
1f
Ending Inventories budget
5.00
1g
Cost of goods sold budget
4.00
Presentation and use of formulas in Excel
4.00
2a
Kaizen budgeting
2.00
2b
Kaizen / traditional budgeting
3.00
2c
Human behaviour & budgeting
4.00
Presentation, grammar, spelling & referencing
4.00
Teamwork (from minutes and
other observations)
2.00
Total
45.00
Weighted Total 15.00
Lecturer’s Comment:
Signature: ……………………………………………………………………... Date: …………………
Points to follow when creating your team excel workbook:
1) Create your excel workbook for the assignment - It is best to create one workbook and name each worksheet in the workbook with the question number, instead of creating many different files for each question. This will create consistency in submission and only one file per team per assignment will need to be submitted.
2) Naming the workbook and worksheets - Name the workbook with a student name in the group and their number and save under this name. Name the worksheet in the workbook with the question number.
3) Each question must begin on a fresh worksheet not continued from the previous question.
4) Minutes of the meeting must be attached for group assignment separate on a word document.
5) Cover page for assignment – make sure you have a cover page with a list of the student names and numbers in the team.
6) Use the excel formulas in your worksheet. Do not use calculators to arrive at the answer.
Team Meetings
The team should have a minimum of three meetings (the team may have as many meetings as they need to) and should maintain minutes of those meetings which should be included with the team assignment as an attachment. The minutes of meetings should (at the very least) contain - 1) Where and when the meeting was held
2) People present; absent; apologies
3) What was discussed, any disagreements/conflicts, points that were agreed on;
3) The follow up action relating to each member of the team;
4) Any other relevant matters, anything else that is significant to the successful completion of the assignment.
An agenda template and a template for minutes of meeting are attached (after Part 2 of the assignment information) for your team to use.