ACCT19060 T1 2019
Assessment task 2: Part A — Assignment
Due date: Tuesday of Week 6 [11:45pm [AEST] 23 April 2019] ASSESSMENT
Weighting:
15%
2
Objectives
This assessment item relates to the unit learning outcome 3.
Details and instructions
This is the first part of assessment task 2. You can choose to do Part A either as an individual or you can pair up with another student who is currently enrolled in the unit. For administration purposes, you must email the Unit Coordinator (UC) about your intention to work in a pair by 4pm on Friday 12 April. Please provide the following information in your 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 awarded).
Part A is designed to help you to gain and/or improve your information technology skills. Specifically, it 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 A. 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 this part 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 unit, it is assumed that students enrolled in this unit 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 have calculated figures in an output sheet and those figures are used in a subsequent output sheet, link these figures to the previous output sheet and do not link these figures 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.
• Unless advised otherwise, use and show two (2) decimal places in calculations where appropriate. You do not have to show decimal places when there are no cents or fractions in figures. For example, show the figure 0.08 with 2 decimal places but do not show the figure 1 as 1.00.
• High professional presentation standards that you will apply when presenting budgets to your employer in the workplace. Ensure your ‘employer’ will be able to follow your logic in the design of your worksheets.
Formatting
The following requirements are necessary to enhance the marking process and save time to mark the assignments. Please note: five (5) marks are allocated for complying with the design and formatting requirements.
• Due to time constraints with marking, only certain cells will be selected for marking. Marks are awarded for using the correct link and/or formula in cells and not the correct figures. 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. To understand your mark, please compare your marked assignment with this marking criteria sheet.
• 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: font colour = red; and number format = number. The marker will use this column to indicate how many marks you received, unless you received full marks, then this column may be left blank.
• Ensure the presentation of each worksheet is of a high professional standard that is clear and can be marked easily.
• Ensure all columns in your worksheets 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.
• Leave each workbook on 100%.
• 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 where appropriate. Without showing the calculations, no partial marks can be provided.
• Delineate figures with appropriate commas. For example, show 20,000 and not 20000. Further, show $2,420,000 and not $2420000. Only use $ for dollar values that will be linked to the budgeted Income Statement.
Following is an example of how to reference to and show the calculations, and the “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
$1,500,000 $1,920,000 $3,420,000
Q1 Q2 Half year
44,000 48,000 92,000
55 50
Total sales for Product A a
Product B
Units
Selling price per unit
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
Submission requirements
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 15 marks
Hosken Ltd manufactures and sells snake repellents. Customers can buy the product either as single units or in a pair. In September 2018, Hosken Ltd’s budget department gathered the following data to prepare the budgets for 2019:
2019 Projected sales
Product Quarter 1 Quarter 2 Quarter 3 Quarter 4
Single units 8 000 6 500 7 000 9 000
Pairs (in single units) 12 000 6 000 8 000 14 000
Selling price
Per single unit $90 $80 $80 $90
Per pair $160 $140 $140 $160
Finished goods Inventories in units: on 1 January 2019, 6 600 units and on 31 December 2019, 7 500 units.
The following direct materials are used in manufacturing the product:
Direct material Unit usage Quantity used per unit
A Kilogram 0.5
B Kilogram 0.8
C Each 1
Projected data for 2019 with respect to direct materials are as follows:
Direct material Anticipated purchase price Expected inventories
1 January 2019 Target inventories
31 December 2019
A $20 per kg 2 800 kg 2 975 kg
B $35 per kg 4 400 kg 4 760 kg
C $12 per unit 5 700 units 5 980 units
Projected direct manufacturing labour requirements and rates for 2019 are as follows:
Department Time per unit Rate per hour
Moulding 15 minutes $14.55
Assembly 5 minutes $16.75
Manufacturing overhead is allocated at the rate of $15 per direct manufacturing labour-hour (total time of both departments).
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.
REQUIRED:
A. Design one input and the seven output sheets as required in B below. To obtain the maximum 5 marks for presentation, ensure your spreadsheets meet the “design of spreadsheets” and “formatting” requirements as stated above.
B. Prepare the following seven (7) budgets for 2019. Show quarter and total figures for the year only in the sales budget. In all the other budgets, only show figures for the full twelve months.
1. Sales budget (separate figures for selling single units and pairs).
2. Production budget (in units).
3. Direct material purchases budget (in kilograms for materials A and B and in units for material C). Show the calculations of the three materials in separate columns.
4. Direct materials purchases budget (in dollars). Show the calculations of the purchases of the three materials separately.
5. Direct manufacturing labour budget (separate hours and dollars of the Moulding from that of the Assembly department). Show the budgeted production units, direct manufacturing labour hours per unit, DMLH for the year, and DML rate per hour.
6. Budgeted finished goods inventory. Show the unit cost and input quantities of each cost component (DM, DL, and Overhead cost) and of each direct material separately. Use two decimal places in showing cost per unit and three decimal places in showing quantities of input per cost component. Show the number of units and dollar values of Finished Goods inventory for both 1 January 2019 and 31 December 2019.
7. Budgeted Income Statement for the year ending 31 December 2019. Use the dollar amount of ‘purchases’ figure for direct materials in calculating the cost of goods sold. Show the determination of cost of goods sold in the budgeted Income Statement.
Ensure the dollar value that will be linked to the budgeted Income Statement is shown at the bottom of each relevant worksheet and that these figures are rounded to the nearest dollar.
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 5
Section B
1 Sales budget 8
2 Production budget 3
3 Direct material purchases budget (in kilograms and units) 7
4 Direct material purchases budget (in dollars) 4
5 Direct manufacturing labour hours and cost budget 7
6. Finished goods inventory budget 14
7 Income Statement 7
Comply with design and formatting requirements 5
Assignment Total 60 marks
Less: Late penalty (5% per day)
Assignment Total: 15 % weighting 15 marks