Assessment task 2 Assignment
Due date:
Monday of Week 7 [4pm [AEST] 20 August 2012]
ASSESSMENT
Weighting:
30%
2
Objectives
This assessment item relates to the course learning outcomes 2, 3, 4 and 7.
Details
The assignment is designed to help you to gain and/or improve your generic, analytical and information technology skills. There are two parts to this assignment. Part A is designed to enhance your skills in the planning, designing and using of spreadsheets and relates to budgets. Part B is designed to enhance your written communication and analytical skills and relates to various costing issues to consider in making decisions.
Students must use spreadsheets such as excel in Microsoft Office to answer Part A. You will lose all 12 marks allocated to the use of spreadsheets if you choose not to use spreadsheets.
As this is an advanced course, it is assumed that students enrolled in this course are familiar with the use of spreadsheets. If you are not familiar with using spreadsheets or some functions of a spreadsheet, we advise you to use the help facilities which are provided within most spreadsheet software. You will also find help on how to use spreadsheets on the internet and/or in textbooks in most libraries. In addition to these sources, students can also contact the Academic Learning Centre and the lecturer or tutor at the local campus. Distance Education students may contact the Course Coordinator for assistance.
Students must design the spreadsheet so that you have an input spreadsheet with input cells for entering the data, which are linked to output spreadsheets (i.e. the 9 budgets). We recommend you design your output sheets as shown in the examples on page 8 of this document. In essence, ensure your spreadsheet is designed in such a way that when you change a number in an input cell, the calculations are done and the relevant output sheet(s) is (are) updated automatically. Design the spreadsheet so that the only numeric values that need to be altered when scenarios change are those in the input cells. Your spreadsheets must contain formulae for doing calculations. However, you are strongly encouraged to show your calculations on your spreadsheets. If your figures are incorrect and you do not provide your calculations, you may not receive any marks. The markers do not have the time to follow your links and/or to guess how you derived at your figures. One way of showing your calculations in your output sheet is in brackets for example as shown in bold below:
October
Sales (7 000 x $100)
$700 000
The Course Coordinator reserves the right to request a student to supply a soft copy of their spreadsheets and to explain to your lecturer how the assignment was prepared using that spreadsheet.
Part A Budgeting 75 marks
Steelworks Ltd manufactures two types of shopping trolleys for the Australian market: Standard trolley and Child carry trolley (which includes a plastic seat for a baby). Steelworks Ltd buys the direct materials in metres to manufacture the frames of the trolleys and they buy the wheels in boxes of 100 wheels per box. They use rod steel to manufacture the frames of both types of trolleys. The Standard trolley is almost twice as big as the Child carry trolley. The Child carry trolley is also fitted with a plastic seat. The following data are available for the July 2012 to June 2013 budget:
The direct-cost inputs for each trolley are:
Direct-cost inputs
Standard trolley
Child trolley
Rod steel for frames
24 metres of steel
14 metres of steel
Wheels
4 wheels
4 wheels
Plastic seat
0
1 seat
Direct manufacturing labour
15 minutes
20 minutes
The Marketing manager of Steelworks Ltd projects the following monthly sales and estimated selling prices of the two trolleys:
Months
Standard trolleys
Child trolleys
Projected sales
Selling price (S/P)
Projected sales
Selling price (S/P)
July to August
25 000 per month
$80 each
3 000 per month
$85 each
September to October
27 000 per month
$84 each
3 200 per month
$89 each
November to December
30 000 per month
$88 each
3 400 per month
$92 each
January to June
24 000 per month
$86 each
2 800 per month
$90 each
The actual inventory of finished goods as at 30 June 2012 is as follows:
Inventory at 30/06/2012
Standard trolleys
Child trolleys
Units
31 750
3 800
Total cost
$1 505 300
$160 398
The actual inventory of raw material as at 30 June 2012 is as follows:
Inventory at 30/06/2012
Steel (for frame)
Wheels
Plastic seat
Units
867 400 metres
1 514 boxes
4 100
Total cost
$1 257 730
$272 520
$30 750
The firm has a policy of carrying an end-of-month finished goods inventory of 100% of the following months sales plus 25% of the second following months sales. It is also company policy to keep raw material inventory at the end of a month at levels sufficient so that they have enough inventory on hand to manufacture the required number of trolleys for the following month. For example, the level of raw material inventory at the end of January will be the level of raw material required to enable the company to manufacture the number of trolleys required for February.
Unit costs of direct materials purchased and unit costs of finished goods sold remain unchanged throughout each budget year but can change from year to year. The actual and estimated unit costs for the two types of frames, for fabric and for Plastic seat are as follows:
Actual for 12 months ending 30/06/2012
Estimated for 12 months ending 30/06/2013
Rod steel for frame
$1.45 per metre
$1.60 per metre
Wheels
$180 per box
$200 per box
Plastic seat
$7.50 each
$7 each
Steelworks Ltd has a labour contract that calls for a wage increase of 4% per hour on 1 December every year. The current wage rate is $14.50 per hour. In addition to wages, direct manufacturing labour-related costs include superannuation contributions of $0.725 per hour and workers compensation insurance of $0.30 per hour. These costs also increase by 4% per hour on 1 December every year. The cost of employee benefits paid by Steelworks Ltd for its employees is treated as a direct manufacturing labour cost.
Manufacturing overhead (both variable and fixed) is allocated to each trolley on the basis of budgeted direct manufacturing labour hours (DMLH) per trolley. The budgeted variable manufacturing overhead rate will be $4.50 per DMLH hour up to 30 November 2012 and is estimated to be $5.10 per DMLH from 1 December 2012. The fixed manufacturing overhead will be $14 000 per month up to 31 October 2012 and is budgeted to be $16 000 per month from 1 November 2012. Both variable and fixed manufacturing overhead costs are allocated to each unit of finished goods.
Assume the following in your answer:
· Direct materials inventory and finished goods inventory are costed using the FIFO method.
· There is no work-in-progress inventory at any given point in time.
Use 3 (three) decimal places in your calculations and round the figures shown in your output sheets to the nearest dollar.You must use formulae to do your calculations in your output sheets. However, please show the calculation of your figures on your output sheets. If your figures in your output sheets are incorrect and you do not show how you arrived at your figures you may not receive any marks or partial marks. The markers will carry forward any mistakes you made in earlier budgets but we cannot guess where figures come from if your figures are incorrect and you do not show how you calculated them.
Required:
(i) Prepare the following six (6) budgets by month for October, November and December of 2012 and the totals for these three months:
1. Revenue budget
2. Production budget in units
3. Direct material usage budget (both units to be used and the cost budget in dollars)
4. Direct material purchases budget in units and in dollars
5. Direct manufacturing labour hours and cost budget
6. Manufacturing overhead budget (you are not required to calculate the totals for the three months, only the individual months)
(ii) Prepare the following three (3) budgets for the three months ending 31 December 2012 only. (Show these budgets intotal for the quarter only, not per month. Round your numbers for these budgets up to zero decimal places):
7. Opening and ending inventories budget for direct materials for the three months ending 31 December 2012.
8. Opening and ending inventories budget for finished goods for the three months ending 31 December 2012.
9. Income Statement budget for the three months ending 31 December 2012. Show the detail of how cost of goods sold was calculated in this budget.
(iii) Print and submit the following for your answer to Part A (worth 12 marks see marking criteria sheet):
· The input sheet
· The formulae spreadsheet(s) of all nine budgets in (i) above (i.e. showing the rows and columns together with any formula that has been used in each cell of the spreadsheet).
· The nine (9) budgets you prepared in (i) and (ii) above.
Please note: the 12 marks allocated for the use of spreadsheets are for the design and layout of your input and output spreadsheets and for the design and use of formulae. The formulae sheets are not considered to be calculations and are not marked or looked at to ascertain how you derived at your figures. Your formulae sheets will be marked merely to ascertain the linkages in your spreadsheets and the logical flow of the design of your spreadsheets. Please show your calculations in your output spreadsheets of the nine budgets required in (i) and (ii) above to enable the markers to award you partial marks if your figures are incorrect.
Hint: To help you with calculating some figures, it may be useful to design some spreadsheets in such a way to include a few months before and after the three months required in this assignment.
Part B Decision making and relevant information 15 marks
Part B is a continuation from Part A.
Steelworks Ltds current machinery has a capacity to manufacture 36 000 trolleys per month (32 000 Standard trolleys and 4 000 Child trolleys) but has a constraint on the direct manufacturing labour hours. The company rents very large premises that have enough space to acquire more machinery if required. The company received an order from an overseas customer to manufacture 10 000 Child trolleys, to be delivered 1 January 2013. The trolleys can be manufactured any month from July to December 2012. To meet the requirements of the overseas customer, the Child trolleys will have to be customised to add a set of brakes to the trolley. Steelworks Ltd does not have the equipment to make the brakes. They could buy equipment for $30 000 to make the brakes and add the brakes to the trolleys themselves. Alternatively they can buy the brakes from a supplier for $6 per set and add the brakes to the trolleys. Regardless of their decision about the brakes, if they decide to accept this order, they will manufacture the trolleys. The customer is prepared to pay $90 per trolley.
Required:
Advise the CEO of Steelworks Ltd whether to accept or reject the order. Discuss the following issues that Steelworks Ltdwill have to consider in deciding whether to accept or reject the order.
1. Relevant costs. (5 marks)
2. Other financial issues and costs. (5 marks)
3. Other non-financial issues. (5 marks)
Justify your answers and decisions for the three issues above with figures. Please show all your calculations to all the figures you provide in Part B. You may use the figures you calculated in Part A in answering Part B, but you do not necessarily have to.
Word limit for Part B:between 1 000 1 500 words.
Student name
Student ID number
Marking Criteria Sheet
Marks available
Marks awarded
Part A Budgeting
63 marks
Revenue budget
8
Production budget in units
10
Direct material usage budget
10
Direct material purchase budget
10
Direct manufacturing labour hours budget
6
Manufacturing overhead cost budget
4
Opening and ending raw material inventory costs
6
Opening and ending finished goods inventory costs
6
Cost of goods sold budget
3
Use of Spreadsheets
12 marks
Design of input sheet
4
Design and use of formulas
4
Layout of output sheets
4
Part B Decision making and relevant information
15 marks
Discussion of relevant costs
5
Discussion of financial costs and issues
5
Discussion of other non-financial issues
5
Assignment Total
90 marks
Assignment Total out of 30
30 marks
Suggested Layout for Output spreadsheets
For example: Revenue budget
Revenue budget for Standard trolleys
Oct
Nov
Dec
Total
Units
S/P per unit
Total sales for Standard trolleys
Revenue budget for Child trolleys
Oct
Nov
Dec
Total
Units
S/P per unit
Total sales for Child trolleys
Total sales for all trolleys
For example: Production budget in units
Standard trolleys
Oct
Nov
Dec
Total
Budgeted units sold
Add budgeted ending fin. gds inventory (trolleys)
Total requirements
Deduct beginning fin. gds inventory (trolleys)
Budgeted production
Child trolleys
Budgeted unit sales
Add budgeted ending fin. gds inventory (trolleys)
Total requirements
Deduct beginning fin. gds inventory (trolleys)
Budgeted production
For example: Direct materials usage budget
Oct
Nov
Dec
Total
Steel rod for:
Standard trolleys
Child trolleys
Plastic seat
Standard trolleys
Child trolleys
Wheels for:
Standard trolleys
Child trolleys