ACCT19060 T1 2016
Assessment task 2: Part B — Assignment
Due date: Wednesday of Week 7 [11:45pm [AEST] 27 April 2016] ASSESSMENT
Weighting: 20%
2
Objectives
This assessment item relates to the course learning outcomes 3 and 5.
Details
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 course. This student may be the same student you paired up for Part A or can be a different student. If you want to work in a pair for Part B, you must notify the Course Coordinator via email by 4pm on Wednesday 20 April 2016. Please read the Course Profile for instructions about such notification. If you do not notify the CC by this deadline, you forfeit your right to do this part of the assignment in a pair. If you choose to do this part in a pair without permission, you will share the mark awarded.
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 as well as 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). This is because marks are allocated for demonstrating your ability to design and use spreadsheets, thus for links between cells and worksheets and for formulae.
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 may contact the Course Coordinator for assistance.
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 onto the output sheets (.i.e. budgets).
• The input sheet must be linked to all other output sheets and must not contain any formulae.
• 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,
e.g. 1 Revenue, 3 DM usage, and 7 I/S. You may use abbreviations.
• 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 inputs 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 all the cells 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 three (3) decimal places in all your numerical calculations. However, it is not necessary to show 3 decimal places in all the figures, as your worksheets will look cluttered and messy. Hence only show 3 decimal places in the cells which contain figures that have numbers other than zero (0) in the 3 decimal places. For example, show the figure 0.083 with 3 decimal places but do not show the figure 1 as 1.000.
• Show the dollar values of all the figures in the various budgets that will be linked to the Income Sheet to the nearest dollar in the relevant budget (i.e. do not show any cents).
Formatting
The following requirements are necessary to enhance the marking process and save time to mark the assignments. Hence you will lose marks if you do not comply with them.
• Please be aware that due to time constraints, not all figures and cells can be marked. The marking team may use the following marking legends to indicate which figures and cells were marked and where you have received marks or where you have lost marks:
? Legend: a cell highlighted in red indicates the figure and/or formulae is incorrect and hence you lost the relevant mark
? Legend: a cell highlighted in green indicates the figure and/or formulae is correct and hence you received the relevant mark
? A marking criteria sheet will be provided and uploaded to Moodle when the assignments are returned. This will indicate which cells were marked and what the relevant mark is.
To assist with the marking and the use of coloured legends, please ensure that you 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; number format = number, and show 2 decimal places. The marker may use this column to indicate how many marks you received, for example 0.25 or 0.5.
• Although some marks will be given for formulae and the logic used in designing the formulae, the marker does not have time to trace the links in your formulae and to recalculate your figures. Therefore, show your calculations as far as practical in each worksheet. You do not have to show all calculations as this will not be practical. It is recommended to use a reference system to show the calculations as shown in the example below. Show a reference column (containing a reference number) to the left of the figures and show the calculation at the bottom of the worksheet. You will receive zero marks if your figures are incorrect and you did not show your calculations. Partial marks can only be given if you show your calculations.
• Ensure all worksheets (budgets) can be seen and read on the screen when you open the worksheet. Thus ensure all the columns of a particular budget fit on one screen and that the font size is big enough so that the marker does not have to enExchangeable the font size. Also please ensure that you save each worksheet so that when the marker opens the particular worksheet, it opens exactly what you want the marker to mark. Hence, ensure that the marker does not have to scroll up or down or left or right to get to the cells that have to be marked.
• 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.
$1,500,000 $1,920,000 $3,420,000
Q1 Q2 Half year
44,000 48,000 92,000
$55 $50
Following is an example of where and how to show the calculations and “Marks” column in each worksheet:
Example Ltd: Budget 1
Revenue Budget
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
Product B
Units
Selling price per unit
Total sales for Product B b $2,420,000 $ 2,400,000 $4,820,000
Total sales c 8,240,000
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 and your student ID appear on all documents you upload, and details of both students if you did the assignment in a pair. 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 this student’s account. Please contact this student to get a copy of the marked assignment. Also please ensure you include your student ID and name in the saved files, followed by the appropriate .xls or .xlsx for your excel spreadsheet. For example: s0123456 Pat Jones.xlsx
Extension
Please be aware that you CANNOT apply for extension to submit Part B of the assignment through the CQUniversity website and that if you want to apply for extension, that you must email the Course Coordinator. Also be aware of CQUniversity 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 Course Coordinator BEFORE the due date and time of submission, and attach documents as evidence for the application.
Assignment question 80 marks
Xepa Ltd makes and sells two types (Standard and Exchangeable) of sunglasses. For the fourth quarter of 2015, the company sold 15 000 Standard sunglasses at $35 each and 5 000 Exchangeable sunglasses at $60 each. The following data are available for the 2016 calendar year budget:
Standard sunglasses Quarter 1 Quarter 2 Quarter 3 Quarter 4
Estimated unit sales 12 000 14 500 16 800 19 000
Sales price per unit $35 $38 $40 $43
Direct labour hours per unit 10 minutes 10 minutes 8 minutes 8 minutes
Direct material required for frame per unit 100 grams 100 grams 100 grams 100 grams
Direct material required for lenses per unit 2 lenses 2 lenses 2 lenses 2 lenses
Exchangeable sunglasses Quarter 1 Quarter 2 Quarter 3 Quarter 4
Estimated unit sales 4 500 4 800 5 200 5 500
Sales price per unit $80 $90 $90 $95
Direct labour hours per unit 15 minutes 15 minutes 14 minutes 14 minutes
Direct material required for frame per unit 100 grams 100 grams 100 grams 100 grams
Direct material required for lenses per unit 6 lenses 6 lenses 6 lenses 6 lenses
Both products use the same direct materials and are manufactured by the same labourers. Direct materials for the frames are bought in kilograms. Following are the costs for Direct Materials and the rates for Direct Labour for the relevant periods:
Costs Q4 2015 Q1 2016 Q2 2016 Q3 2016 Q4 2016
Direct material for frame per kilogram $110 $110 $120 $120 $130
Direct material per lens $4.50 $5.00 $5.50 $5.50 $5.50
Direct labour per hour $16 $16 $16.50 $16.50 $16.50
The direct labour rate includes wages, all employee-related benefits, and the employer’s share of payroll tax. The company’s enterprise agreement calls for an increase in direct labour wages on 1 April every year (the first day of the second quarter). The increase from 1 April 2015 has been included in the direct labour hourly rate above. The current machinery has been in operation since 2013 and the direct labour hours and the quantity of the direct materials required per unit of quarter 1
1
2016 has been the standard since then. New and improved machinery will be fully operational on July 2016, hence the reduction in the direct labour time required per unit.
Management desires an ending inventory of finished goods units each quarter equal to 50 per cent of the next quarter’s sales units. Management also desires an ending inventory of all raw materials (both frames and lenses) equal to 75% of the particular quarter’s quantities used. The raw material inventory on 31 December 2015 was as follows: 1 369 kilograms of material for frames and 41 625 lenses.
The manufacturing overhead costs change twice a year: on the 1st of April and on the 1st of October.
The variable cost per Direct Manufacturing Labour Hour (DMLH) rate is the same for both products. The following table summarises the manufacturing overhead costs relevant to the period of the budget, effective from the following dates:
1/10/2015 1/4/2016 1/10/2016
Variable cost per DMLH $4.00 $4.20 $4.40
Total fixed costs for six months (allocated equally p.m.) $33 000 $34 800 $36 000
The company calculates the fixed costs per unit for each quarter based on the total number of sunglasses produced in the relevant quarter.
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 below. Ensure your spreadsheets meet the “design of spreadsheets” and “formatting” requirements as stated above.
Show the calculations for the two products separately (as illustrated in Example Ltd on p.3 of this assignment) for budgets 1, 2, 3, 4 and 6 below. Also show the calculations of the two products separately unless informed otherwise.
B. Prepare the following five (5) budgets for the first and second quarter of 2016 and the totals for the first half of the year. Also show at the bottom of each of these 5 budgets the total dollar value that will be shown in the Income Statement (required in budget 7).
1. Revenue
2. Production budget in units. Include a column to the left of the Q1 2016 column and show the figures for Q4 of 2015.
3. Direct material usage in quantity and dollars. First calculate the quantities, splitting the quantity of frames from the quantity of lenses. Show the quantity used for Q4 2015 in a column on the left hand side of the Q1 2016 column. Separate the direct materials used for the frames from the direct materials used for the lenses. Show the kilograms of DM for the frames used and the number of lenses per product per quarter and for the half year. Then add the total kilograms of DM used for lenses for both products per quarter and for the half year together (and do the same for the number of lenses) and show these figures. (Tip: You require the total figures of kilograms of material for frames and the number of lenses for the period ending 30 June 2016 to calculate the quantities and dollar values of direct material to be purchased for the half year. There is no need to calculate the cost used and/or purchased per product or per quarter, you only have to calculate these figures for the period ending 30 June 2016). (Refer to Schedule 3A on page 383 of the prescribed textbook for guidance as to how to calculate the quantity and dollar amounts of the direct materials that need to be purchased for the 6 months ending 30 June 2016).
4. Direct manufacturing labour costs. Calculate first the DMLH per product separately and then add the DMLH of the two products together. Then calculate the DML costs for the relevant periods.
5. Manufacturing overhead costs. Show the total DMLH of both products (calculated and shown in budget 4 above) per quarter and use these figures to calculate the variable costs for the required periods. (Please do not show the overhead costs for the two products separately).
Prepare the following two (2) budgets for the first half of 2016. Do not prepare it per quarter.
6. Finished Goods inventory: beginning and ending (Show the calculations of the cost per unit for the beginning and for the closing inventory for each product). Please do not show the Raw Materials inventory.
7. Income Statement. Use the absorption costing method and show the following line items: cost of goods manufactured, cost of goods available for sale, and cost of goods sold. Add a column to show which budget these figures are linked with. Do not show any cents in your figures in this budget.
Please refer to the following Marking criteria sheet for the allocation of marks.
Marking Criteria Sheet Marks available Marks awarded
1 Revenue budget 7
2 Production budget in units 9
3 Direct material usage in quantities and dollars budget
(separate DM for frames from DM for lenses) 10
4 Direct manufacturing labour hours and cost budget 8
5 Manufacturing overhead costs 6
6 Finished Goods inventory: Beginning and Ending 20
7 Income Statement 10
Design of Spreadsheets
One input and 7 output sheets, named appropriately 2
No cents in total dollar values (that will be shown and linked to budget 7) 2
Correct decimal places in calculations 2
Comply with formatting requirements* 4
Assignment Total 80 marks
Assignment Total out of 20 20 marks
* Marking this assignment is very time consuming. The markers will only mark what they see when they open each worksheet. Thus, when you save your spreadsheet, please ensure that all worksheets open with the information you want to be marked, visible on the screen. Also, ensure that you use a big enough font size and set the zoom level on 100% so that the marker can see everything on each worksheet clearly. The marker does not have the time to scroll up and down or left and right to find the relevant information to mark. If they have to, it means you probably have made the budgets unnecessarily complicated. All budgets are short and simple and should fit nicely on one screen. Hence, plan and design your worksheet carefully.