Assessment task 2 Assignment
Due date:
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 15 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 spreadsheets so that you have one input sheet for entering all numeric values. You must also have various output sheets for the eleven required budgets. You must not enter any numeric values in the output sheets. The input sheet must be the only source of data entry and must be linked to all other output sheets. Thus, the output spreadsheets must only contain formulae for calculating the figures. In essence, ensure you design your spreadsheet in such a way that when you change a numeric value in an input cell in the input data spreadsheet, then the calculations are done automatically in the output spreadsheets. Please refer to page 7 of this document as an example to guide you with designing your output spreadsheets so that each of the three months and the total for the quarter are shown in different columns.
Submission requirements
You must submit your assignment electronically through the secure upload facility in the Moodle system. Please ensure your student name and student ID appear on the documents you upload.
Assessment criteria
The marking criteria sheet at the end of the assignment indicates the allocation of marks for the assignment.
There is a 5% penalty per calendar day for late submissions.
1
Part A Budgeting 55 marks
Southern Star Ltd manufactures two types of skateboards for the Australian market: Junior skateboards and Senior skateboards. The Senior skateboard is first of its kind in the world and has a wireless speed control system, which allows the rider to control the speed by hand. Southern Star Ltd buys the direct materials (wood) for the boards in metres and the wheels in boxes of 56 wheels per box. They use the same wood to manufacture the boards of both types of skateboards. The Junior skateboard is smaller and lighter than the Senior skateboard and hence uses less wood. Only two layers of wood are glued together to manufacture the Junior skateboard whereas three layers of wood are glued together to manufacture the board of the Senior skateboard. The Junior skateboard also uses smaller wheels than the Senior skateboard. The following data are available for the January 2013 to December 2013 budget:
The direct-cost inputs for each skateboard are:
Direct-cost inputs
Junior skateboard
Senior skateboard
Wood for boards
0.5 metres of wood
2 metres of wood
Wheels
4 wheels
4 wheels
Speed control system
0
1 system
Direct manufacturing labour
20 minutes
30 minutes
The Marketing manager of Southern Star Ltd projects the following monthly sales and estimated selling prices of the two skateboards for 2013:
Months
Junior skateboards
Senior skateboards
Projected sales
Selling price
Projected sales
Selling price
(S/P)
(S/P)
January to February
2 500 per month
$40 each
3 000 per month
$185 each
March to April
2 700 per month
$42 each
3 200 per month
$195 each
May to June
3 000 per month
$44 each
3 400 per month
$210 each
July to December
2 400 per month
$43 each
2 800 per month
$190 each
The actual inventory of finished goods as at 31 December 2012 is as follows:
Inventory at 31/12/2012
Junior skateboards
Senior skateboards
Units
3 750
4 500
Total cost
$75 690
$620 231
The actual inventory of raw material as at 31 December 2012 is as follows:
Inventory at
Wood Junior
Wood Senior
Wheels
Wheels
Speed control
31/12/2012
skateboard
skateboards
Junior boards
Senior boards
system
Units
1 300 metres
6 200 metres
186 boxes
222 boxes
3 100
Total cost
$19 500
$93 000
$26 040
$89 510
$232 500
2
The firm has a policy to keep the following level of finished goods inventory at the end of any particular month: 100% of the following months sales plus 50% of the second following months sales. It is also company policy to keep raw material inventory at the end of a particular month at levels sufficient to manufacture the required number of skateboards for the following month. For example, the level of raw material inventory at the end of January will be the raw material required to manufacture the number of skateboards 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 wood, the wheels of the two types of boards, and for the Speed control system are as follows:
Actual for 12 months ending
Estimated for 12 months
31/12/2012
ending 31/12/2013
Wood for boards
$15 per metre
$14.50 per metre
Wheels for Junior skateboards
$140 per box
$151.20 per box
Wheels for Senior skateboards
$403.20 per box
$448 per box
Speed control system
$75 each
$80 each
Southern Star Ltd has a labour contract that calls for a wage increase of 3.5% per hour on 1 June every year. The current wage rate is $ 13 per hour. In addition to wages, direct manufacturing labour-related costs include superannuation contributions of $0.70 per hour and workers compensation insurance of $0.30 per hour. These costs also increase by 3.5% per hour on 1 June every year. The cost of employee benefits paid by Southern Star Ltd for its employees is treated as a direct manufacturing labour cost.
Manufacturing overhead (both variable and fixed) is allocated to each skateboard on the basis of budgeted direct manufacturing labour hours (DMLH) per skateboard. The budgeted variable manufacturing overhead rate will be $2.50 per DMLH hour up to 31 May 2013 and is estimated to be $2.80 per DMLH from 1 June 2013. The fixed manufacturing overhead will be $15 000 per month up to 30 April 2013 and is budgeted to be $18 000 per month from 1 May 2013. Both variable and fixed manufacturing overhead costs are allocated to each unit of finished goods.
Southern Star Ltd aims to stay ahead of its competitors and does ongoing research and development of its boards. They employ a full-time engineer to do research and development and it cost the company $8 000 per month to employ the engineer. The cost to employ the engineer will also increase with 3.5% on 1 June 2013. The fixed component of budgeted marketing costs is $120 000 for the year ending 31 December 2013. In addition to the fixed cost for the marketing department, the company pays sales people a commission of 5% of revenues earned in a particular month. The distribution costs consist of a fixed component of $27 000 for the six months ending 30 June 2013 and a variable component of $4 per skateboard sold for a particular month. All fixed costs are allocated equally per month for budgeting purposes.
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.
3
Use 3 (three) decimal places in your calculations and round the dollar amounts shown in your output sheets to the nearest dollar.
Required:
All budgets must be linked to the input sheet and where necessary, to the other budgets. Do not enter any numerical values in the output sheets.
1 Design an input sheet that is the only source of data entry, link the input sheet with all the output sheets (budgets) and design and use formulae for calculating the figures in all the output spreadsheets. Please refer to the marking criteria sheet regarding the allocation of the 15 marks for the design and use of the spreadsheets and formulae. You will only receive maximum marks if the marker can follow your logic in the design of your spreadsheets and hence if it is not too difficult and time-consuming to mark.
2 Prepare the following seven (7) budgets by month for April, May and June of 2013 and the totalsfor these three months:
Revenue budget
Production budget in units
Direct material usage budget in units. Calculate the cost budget in dollars for each of the following five direct materials used for the quarter only: Wood (Junior), Wood (Senior, Wheels (junior), Wheels (Senior) and Speed control system. You do not have to show these cost budgets either per month or the opening inventory for the period.
Direct material purchases budget in units and in dollars. Show the number of boxes of wheels to be purchased and their costs.
Direct manufacturing labour hours and cost budget (show DMLH per product per month)
Manufacturing overhead budget (show total DMLH for both products per month)
Non-manufacturing costs budget
3 Prepare the following four (4) budgets for the quarter ending 30 June 2013 only. (Show the totalof the three months in these budgets only. Do not show the monthly figures. Round yournumbers for these budgets up to zero decimal places):
8.
Opening and ending inventories budget for direct materials for the three months ending
30
June 2013.
9.
Opening and ending inventories budget for finished goods for the three months ending
30
June 2013.
Cost of good sold budget for the three months ending 30 June 2013.
Income Statement budget for the three months ending 30 June 2013. Show the gross margin and the operating profit in this budget.
Hint: To help you with calculating some figures, it may be useful to design the spreadsheets of some budgets in such a way as to include a few months before and/or after the three months required in this assignment.
4
Part B Decision making and relevant information 20 marks
Part B is a continuation from Part A
On 4 February 2013, Southern Star Ltd received a phone call from a potential customer in India who wants to sell the boards in India, starting 1 July 2013. The customer offers Southern Star Ltd 10% more per skateboard than what the current selling price is and wants to buy exactly the same number of boards per month (starting 1 July 2013) as what Southern Star Ltd is currently budgeting for. Thus, Southern Star Ltd will have to manufacture twice as many boards per month as the budgeted sales from 1 July 2013. The skateboards should be delivered a month following the date of sale. Thus, if Southern Star Ltd records the sale in July 2013, then the skateboards should be delivered on the first day of September 2013. Southern Star can start the manufacturing of the skateboards for this order any time from 1 March 2013. The customer believes they will be able to double their order in 2014 and again in 2015.
Southern Star Ltd bought its own building to use for the manufacturing of the skateboards 10 years ago. However, the building can only accommodate Southern Stars current machinery, that has a capacity to manufacture 8 000 skateboards per month (4 000 of each type of skateboard). Other than the space constraint of the building, Southern Stars management is not aware of any other constraints and/or issues that could hinder then from meeting this customers order. The CEO contemplates following many other Australian companies, moving their manufacturing businesses to India as one option for expanding the business. The labour costs and rent of property in India are about half of what they currently are in Australia.
Required:
Advise the CEO of Southern Star Ltd whether to accept or reject the order. Discuss the following issues that Southern Star Ltd will have to consider in deciding whether to accept or reject the order.
1.
Relevant costs.
(5 marks)
2.
Other financial issues and costs.
(6 marks)
3.
Other non-financial issues.
(6 marks)
Please notethat 3 marksare allocated for demonstrating appropriate communication skills in youressay. To receive these 3 marks your essay must be well written, have logical arguments and a coherent discussion, be free of grammatical and spelling mistakes, and use of the correct formatting and reference styles.
You will also only receive full marks if you justify your answers and decisions for the three issues above with figures, and if you provide sufficient calculations to support these figures. It is recommended that you base your calculations and discussions in Part B on the figures you calculated in Part A, but you are not expected to redo any budgets in answering Part B.
Word limit for Part B:between 1 000 1 500 words.
5
Student name
Student ID
Marking Criteria Sheet
Marks
Marks
Comments
available
awarded
Part A Budgeting
55 marks
Revenue budget
4
Production budget in units
8
Direct material usage budget: units
5
Direct material usage budget: cost
2
Direct material purchase budget: units
5
Direct material purchase budget: cost
2
Direct manufacturing labour hours budget
5
Manufacturing overhead cost budget
4
Non-manufacturing costs budget
4
Opening and ending raw material inventory
5
costs
Opening and ending finished goods inventory
5
costs
Cost of goods sold budget
2.5
Income statement budget
3.5
Design of Spreadsheets
15 marks
Links between input and output sheets
4
Design and use of formulae
6
Layout of spreadsheets
5
Part B Decision making and relevant
20 marks
information
Discussion of relevant costs
5
Discussion of financial costs and issues
6
Discussion of other non-financial issues
6
Well written, logical arguments and coherent
3
discussion, free of grammatical and spelling
mistakes, correct formatting and reference
styles.
Assignment Total
90 marks
6
Assignment Total out of 30
30 marks
Suggested Layout of columns for spreadsheets
For example: Revenue budget
Revenue budget for Junior
Apr
May
June
Total
skateboards
Units
Selling price per unit
Total sales for Junior
skateboards
Revenue budget for Senior
Apr
May
June
Total
skateboards
Units
Selling price per unit
Total sales for Senior
skateboards
Total sales for all skateboards
For example: Production budget in units
Junior skateboards
Apr
May
June
Total
Budgeted units sold
Add budgeted ending finished
goods inventory (skateboards)
Total requirements
Deduct beginning finished goods
inventory (skateboards)
Budgeted production
Senior skateboards
Budgeted unit sales
Add budgeted ending fin. gds
inventory (skateboards)
Total requirements
Deduct beginning fin. gds inventory
(skateboards)
Budgeted production
For example: Direct materials usage budget
Apr
May
June
Total
Wood for:
Junior skateboards
Senior skateboards
Speed control system
Senior skateboards
Wheels for:
Junior skateboards
Senior skateboards
7
8