ACCT19060 T1 2017
Assessment task 2: Part B — Assignment
| Due date: | Monday of Week 9 [11:45pm [AEST] 8 May 2017] | ASSESSMENT |
| Weighting:
|
20%
|
2 |
Objectives
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 Monday 1 May 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.
Formatting
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 1 2017 assignment, only one product is made and sold.
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.
Extension
Please be aware that you CANNOT apply for extension to submit Part B of the assignment through the CQUniversity website. If you want to apply for extension for Part B, please email the Unit Coordinator and include your supporting evidence. Be aware of CQUniversity’s policy that you must apply for extension before the due date and time of submission. Hence, if you want to apply for extension for Part B, you must email the Unit Coordinator BEFORE 11:45pm on Monday 8 May 2017.
Assignment question 40 marks
Tembisa Ltd produces indoor playground equipment. The company is completing its fifth year of operations and is preparing to build its master budget for the coming year (2017). The master budget will be based on the following information:
- The finished goods inventory was 13 000 units at 31 December 2016. Tembisa Ltd is planning the following ending finished goods inventories for each quarter of 2017:
| Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | |
| Estimated unit finished goods inventory | 15 000 | 20 000 | 10 000 | 18 000 |
- Each piece of equipment uses five hours of direct labour and three units of direct materials. Workers are paid $10 per hour, and one unit of direct materials costs $80.
- Tembisa plans to have closing inventory of direct materials of 30% of the direct materials needed for next quarter’s unit sales.
- Fixed overhead totals $1 million each quarter. The fixed overhead rate is computed by dividing the quarter’s total fixed overhead by the quarter’s budgeted production in units.
- Variable overhead is budgeted at $6 per direct labour hour.
- The forecast quarterly sales in units for 2017 are as follows:
| Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | |
| Estimated unit sales | 70 000 | 75 000 | 90 000 | 95 000 |
The selling price is $400 per unit. The fourth-quarter sales for 2016 are 65 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.
REQUIRED:
- 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 and the second quarter of 2017 and also show the totals for the first half of the year.
- Sales budget
- Production budget.
- Direct material purchases
- Direct labour budget.
- Overhead budget.
- Prepare the ending finished goods inventory budget as at 30 June 2017. 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 | |
| Section B | ||
| 1 Sales budget | 4 | |
| 2 Production budget | 8 | |
| 3 Direct material purchases budget | 10 | |
| 4 Direct labour budget | 4 | |
| 5 Overhead budget | 5 | |
| 6. Ending finished goods inventory budget | 6 | |
| Assignment Total | 40 marks | |
| Assignment Total out of 20 | 20 marks |
Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).
After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.
If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.