ACCT19060 T2 2019 Assessment task 2: Part A — Assignment Due date: Monday of Week 6 [11:45pm [AEST] 26 August2019] ASSESSMENT Weighting: 15% 2 Objectives This assessment item relates to the unit...

ACCT19060 T2 2019

Assessment task 2: Part A — Assignment

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) – at the email address provided in the Unit Profile – about your intention to work in a pair by 4pm on Friday 16 August. Please provide the following information in your notification email: both student names, student ID numbers and the campus both are enrolled at. Please do not work in a pair unless the UC has granted you permission to do so. 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 figures in cells the output sheets (budgets).
In the past, some students have received zero marks because they did not read and follow the details and instructions. 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 figures in cells in the budgets (output sheets), you will not receive the relevant marks, even though the figures may be correct.

As this is an advanced unit and you have used excel spreadsheets in prerequisites for this unit, it is assumed that you are familiar with the use of spreadsheets. If not, please
be proactive and teach yourself well in advance of the due date. 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 UC.

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 DML.

· You may also use acronyms, e.g. Q1 (Quarter 1), Q2 (Quarter 2), etc.

· The cells in your output sheets that contain figures (calculations) 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. Please do not 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.

· Ensure you meet high professional presentation standards that you will apply when presenting budgets to your employer in the workplace. Ensure your budgets are presented in a sensible way so that your ‘employer’ will be able to follow your logic in the design of your worksheets. Also ensure that the font size is big enough for your ‘employer’ to read on a computer and that each budget can be read on one screen.

Formatting

The following requirements are necessary to enhance the marking process and save time to mark the assignments. Please note: three (3) 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 may 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

· To assist with the marking and the use of the coloured legends above, please do not highlight any texts in your worksheets.

· 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 show you which cells were marked and what was marked (e.g. link or formula). The sheet will also show the correct figures. To understand your mark, please compare your marked assignment with this marking criteria sheet.

· 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. Ensure the marker can use this column to show your awarded mark. If you receive full marks, the marker may choose to leave the ‘marks’ column blank. Only use one decimal place in this column.

· Ensure the presentation of each worksheet is of a high professional standard, that it provides all the required information and proper descriptions of figures calculated, is sensible, 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.

Following is an example of how to reference and show the calculations, and the “Marks” column in each worksheet:

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

Eish Ltd manufactures two types of vacuum cleaners: Standard and Deluxe. In September 2018, Eish Ltd’s budget department gathered the following data to prepare the budgets for 2019:

Projected sales for quarter 1 of 2020 are 8 000 Standard vacuum cleaners and 5 500 Deluxe vacuum cleaners.

Finished goods Inventories in units:

The company has a policy to have 10% of the following quarter’s sales in inventory at the end of any quarter.

The following direct materials are used in manufacturing the product, both for 2018 and 2019:

Projected data for 2019 with respect to direct materials are as follows:

The purchase prices for direct materials for 2018 were: $30 per kg for material A; $48 per kg for material B; and $10 per kg for material C.

Projected direct manufacturing labour requirements and rates for 2019 and 2018 are as follows:

Manufacturing overhead is allocated at the rate of $12 per direct manufacturing labour-hour (total time for both products) for 2019 and at $10.50 for 2018.

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 3 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 in the
sales
and
production
budgets only. In all the other budgets, only show figures for the full twelve months.

1. Sales budget (separate figures for the two products).

2. Production budget (in units). Keep the two products separate.

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. Show the production needs and quantity per unit for the two products separately.

4. Direct materials purchases budget (in dollars). Show the calculations of the purchases of the three materials in separate columns.

5. Direct manufacturing labour budget. Show the budgeted production units and the DMLH per unit for each product and show the calculations for the two products in separate columns).

6. Budgeted finished goods inventory as at 1 January 2019 and 31 December 2019. Show the two finished products separately. Show the unit cost and input quantities of each of the following in separate columns: Direct material A, Direct Material B, Direct Material C, Direct Labour, Manufacturing Overhead. Use two decimal places in showing cost per unit and three decimal places in showing quantities of input per cost component.

7. Budgeted Cost of Goods Sold for the year ending 31 December 2019. Use the dollar amount of ‘purchases’ figure for direct materials in calculating the cost of goods sold.

Ensure the dollar value that will be linked to the budgeted Cost of Goods Sold is shown in each relevant worksheet and that these figures are rounded to the nearest dollar.