ACCT19060 T2 2017
Assessment task 2: Part B — Assignment
Due date: Monday of Week 9 [11:45pm [AEST] 11 September 2017] ASSESSMENT
This assessment item relates to the unit learning outcomes 3 and 5.
Details and instructions
This is the second part of assessment task 2. Similar to Part A, you can choose to do Part B either as an individual or you can pair up with another student currently enrolled in the unit. This student may be the same student you paired up for Part A or can be a different student. For administration purposes, you must email the Unit Coordinator (UC) if you intend to work in a pair by 4pm on Wednesday 30 August for Part B. Please provide the following information in this notification email: both student names, student ID numbers and the campus both are enrolled at. The UC will then grant you permission to work in a pair. Please do not work in a pair unless you have received permission to do so. If you fail to email the UC by this due time and date, you will not be allowed to work in a pair. If you choose to work in a pair without the UC's permission, you will share the mark 50/50 with the student you paired with (thus you will only receive 50% of the mark).
Part B is designed to help you to gain and/or improve your information technology skills. Specifically, Part B is designed to enhance your skills in designing and using spreadsheets. You will be required to design formulae in spreadsheets and apply links between cells within a workbook and between different worksheets.
You must use spreadsheets such as excel in Microsoft Office to answer Part B. You will lose a considerable amount of marks if you choose not to use spreadsheets and if you type in figures in the output sheets (budgets). In the past, some students have received zero for Part B because they did not read and follow the details and instructions, and typed in all the figures. Please note: marks are allocated for demonstrating your ability to design and use spreadsheets, thus for links between cells and worksheets, and for designing and using formulae. If you type in figures in cells in the budgets (output sheets), you will not receive any marks, even though the figures may be correct.
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, please be proactive and teach yourself a few weeks before the due date of this assignment. You can become familiar with using spreadsheets by clicking on the help facilities that most spreadsheet software packages provide or you may choose to find help on the internet and/or in textbooks in most libraries. In addition to these sources, students can also seek help from the Academic Learning Centre (ALC), the lecturer and/or tutor at the local campus. Distance education students: please contact the Unit Coordinator for assistance well in advance. We can also set up zoom sessions for discussions and if needed, for excel tutorials.
Design of spreadsheets
To receive maximum marks for designing the spreadsheets and workbooks, you must adhere to the following requirements:
• Only have one input sheet which must be the only source for entering all numeric values/data. The purpose of the input sheet is to ensure that you do not type any figures or data in the output sheets (i.e. budgets). The input sheet must only contain raw data. Please do not do your calculations in your input sheet, but link the raw data from your input sheet to the output sheets and use formulae in the output sheets to calculate the figures in each budget.
• When you carry figures that you have calculated in one output sheet forward to a subsequent output sheet, link these output sheets and do not link the subsequent output sheet again to the input sheet.
• Each budget (i.e. output sheet) must be in its own worksheet.
• Name each worksheet appropriately as follows: Budget number followed by a brief description or abbreviation, e.g. 1 Revenue, 3 DMP, and 5 OH.
• You may also use acronyms, e.g. Q1 (Quarter 1), Q2 (Quarter 2), etc.
• Your output sheets must only contain formulae and links to either the input sheet or previous output sheets if the figure was calculated in a previous budget. Do not type any figures in the cells of your output sheets.
• In essence, ensure you formulate the cells containing figures in all the output worksheets (budgets) in such a way that when you change a numeric value in the input sheet, then the calculations are done automatically in the output sheets.
• Use two (2) decimal places in calculations and show two decimal places in calculations where appropriate. You do not have to show decimal places when there are no cents in figures. For example, show the figure 0.08 with 2 decimal places but do not show the figure 1 as 1.00.
The following requirements are necessary to enhance the marking process and save time to mark the assignments. You will lose up to four (4) marks if you do not comply with them.
• Please be aware that due to time constraints with marking, only certain cells will be selected for marking. The content of cells (i.e. mostly the link and/or formula) will be marked. The marking team will use the following marking legends to indicate which cells were marked and where you have received marks or where you have lost marks:
? Legend: a cell highlighted in red indicates the link and/or formula is incorrect and hence you lost the relevant mark
? Legend: a cell highlighted in green indicates the link and/or formula is correct and hence you received the relevant mark
• Once all the assignments have been marked and returned, a marking criteria sheet will be uploaded to Moodle. The purpose of this sheet is to provide you feedback about your work. This sheet will list all the cells that were marked, the content that was marked (e.g. link or formula), and will provide the correct figure and mark. Please compare your marked assignment with this marking criteria sheet to understand why you’ve lost marks.
To assist with the marking and the use of the coloured legends above, please do not use any highlighting in your worksheets.
• Include a column for “marks” on the right hand side of each worksheet and formulate this column as follow: format colour = red; and number format = number. The marker will use this column to indicate how many marks you received.
• Ensure the presentation of each worksheet is of a high standard that is clear and can be marked easily.
• Ensure all columns are wide enough so that your figures do not display as #### in the cell. These #### cannot be marked and the marker will not widen the column width to see the figures.
• Show your calculations as far as practical in each worksheet. It is not necessary to show the calculations of each and every figure, as that will be impractical and not sensible. For the calculations you choose to show, please include a reference column to the left of the figures and show the calculation at the bottom of the worksheet (as shown in the example below). Showing calculations will enable the marker to provide you partial marks. Without showing the calculations, no partial marks can be provided.
Following is an example of where and how to show the calculations and “Marks” column in each worksheet:
Example Ltd: Budget 1
Revenue Budget Reference
Product A Calculation Q1 Q2 Half year Marks
Units 20,000 24,000 44,000
Selling price per unit $75 $80
Total sales for Product A a $1,500,000 $1,920,000 $3,420,000
Product B Q1 Q2 Half year
Units 44,000 48,000 92,000
Selling price per unit $55 $50
Total sales for Product B b $2,420,000 $ 2,400,000 $4,820,000
Total sales both products c 8,240,000
Reference to calculations
a 20 000 x 75 = 1 500 000
b 2 420 000 + 2 400 000 = 4 820 000
c 3 420 000 + 4 820 000 = 8 240 000
Please note: in the term 2 2017 assignment, only one product is made and sold.
You must submit your assignment electronically through the secure upload facility in the Moodle system. Please ensure your student name(s) and your student ID(s) appear on all documents you upload. If you do the assignment in a pair, only upload the file once in one student’s Moodle account. The marked assignment will be returned to that student’s account. Therefore, please contact the student who uploaded the pair file to get a copy of the marked assignment. Also please ensure you include your student ID(s) and name(s) in the saved file, followed by the appropriate .xls or .xlsx for your excel spreadsheet. For example: s0123456 Pat Jones.xlsx
Be mindful that there is a 5% penalty per day for late submission. You will be penalised immediately with a full day’s penalty if you submit your assignment after the due time, even though you may only be a few hours late. The penalty is not proportioned to the number of hours you submit your assignment late.
Assignment question 40 marks
Marshell Ltd produces a subassembly used in the production of forklifts. The assembly is sold to engine manufacturers and forklift maintenance facilities. The company is completing its fifth year of operations and is preparing to build its master budget for the coming year (2018). The master budget will be based on the following information:
(a) The finished goods inventory was 64 000 units at 31 December 2017, each costing $332.12. The desired ending inventory for each month is 80% of the next month’s sales.
(b) The data on materials used are as follows:
Direct material Per-unit usage Unit cost ($)
Metal 20 kg 16
Components 12 components 10
Inventory policy dictates that sufficient materials be on hand at the end of the month to produce 50% of next month’s production needs. This is exactly the amount of material on hand on 31 December of the prior year.
(c) Each unit uses six hours of direct labour. The average direct labour cost per hour is $28.50.
(d) Overhead each month is estimated using a flexible budget formula. (Note: Activity is measured in direct labour hours.)
Fixed-cost component ($) Variable-cost component ($)
Supplies - 2.00
Power - 1.00
Maintenance 60 000 0.80
Supervision 32 000 -
Depreciation 400 000 -
Taxes 24 000 -
Other 159 800 1.00
(e) The projected sales in units for the first five months of 2018 follow:
January February March April May
Estimated unit sales 80 000 100 000 120 000 125 000 124 000
The selling price is $410 per unit. The sales for December 2017 are 75 000 units.
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 two decimal places in calculations when appropriate and do not round up to the nearest dollar for example use direct labour cost per hour of $28.50 and not $29. Ensure your worksheets show such figures accordingly.
A. Design one input and the six output sheets as required in B below. Ensure your spreadsheets meet the “design of spreadsheets” and “formatting” requirements as stated above.
B. Prepare the following five (5) budgets for the first quarter of 2018. Show the monthly figures and the total for the quarter in separate columns.
1. Sales budget
2. Production budget. Show April in a column to the right of the “marks” column.
3. Direct material purchases budget. Show the calculations of Metal separate to that of Components.
4. Direct labour budget.
5. Overhead budget.
6. Prepare the ending finished goods inventory budget as at 31 March 2018. Show the unit cost of each component separately using two decimal places.
This Marking Criteria sheet is FYI only to indicate how marks will be allocated. You do not have to attach it to your assignment.
Marking Criteria Sheet
Marks available Marks awarded
Section A Design of input sheet 3
1 Sales budget 4
2 Production budget 7
3 Direct material purchases budget 12
4 Direct labour budget 4
5 Overhead budget 4
6. Ending finished goods inventory budget 6
Assignment Total 40 marks
Less: Late penalty (5% per day)
Less: Not complying with formatting (up to 4 marks)
Assignment Total out of 20 20 marks