10% worthneed 80% to 90%.student number - 45233357 (in order to access questions)ALL EXCEL
Excel Assignment 2019 S1 Instructions
Scenario
The Blues Brothers guitar Company makes three types of guitar. The Company has used a traditional product costing system with direct labour hours as the only cost driver for overhead application, but has asked you to develop a full activity based costing method solution for product costing and profitability measurement.
Start by downloading the assessment workbook from iLearn and copying/moving it to an appropriate folder. When you open the workbook it is very important that you Enable Macros/Content. You will then be asked to enter your Student Number (you will not be able to edit it afterwards, so type it in carefully) and then enter your Student Name.
General Guidelines
• All the light blue cells require you to insert some form of calculation, i.e. start with an “=”, just typing in the answer will result in 0 marks.
• Do not change the structure of the workbook in anyway or put any workings anywhere other than directed.
• Only use rounding functions where specifically requested.
• The use of named ranges and tables is encouraged, and in certain cases required.
• Because you are developing a model that we will wish to use for future months and different values it is very important that all calculations will return a correct answer regardless of the values in the spreadsheet and must still work correctly when the lists of data (Labour Costs, BOM & Inventory) are sorted in a different order.
• Double check your answers as you go as one wrong result may affect other results.
• Remember to save often.
• When you are ready to submit, save, close and upload the completed Excel File to iLearn (Excel Submission). You do not need to rename it as your student number will be automatically attached by iLearn, but please ensure that it is an Excel file you submit, (Numbers files will not be accepted) and that there are no “non-English” characters in the file name as these files cannot be opened in an English version of Excel.
Detailed instructions are provided on the following pages. Please follow each of the instructions precisely.
Section A The following questions are to be answered on the Labour Costs Sheet: Marks 10
A 1 Calculate Monthly Super for each employee using the specified Super rate in E3. Apply an absolute cell reference to E3 so that the formula can be dragged down. 2
A 2 Calculate the Total Package (monthly) by adding salary and super for each employee. 1
A 3 In row 19, calculate total monthly salaries, super and package. 1
A 4 In E4 calculate the average monthly package rounded to the nearest 10. 2
A 5 In E5 calculate the average hourly wage by dividing the value in E4 by 20 and then by 8. 2
A 6 In E6 calculate the highest employee salary (total package). 1
A 7 In E7 calculate the lowest employee salary (total package). 1
Section B The following questions are to be answered on the Activity Based Costs Sheet: Marks
6
B 1 Direct labour costs are calculated as the average hourly salary of the employees assigned to that product line (e.g. calculate the average total packages for employees working on Tenor Electric, then divide by 8 and divide by 20). These assignments can change from one month to the next, and the calculation must still work, i.e. if E10026 is assigned to Bass Electric your calculation should stop including his salary. 3
B 2 In row 13 calculate the number of employees working on each product line (as with the above example if the employees are reassigned you calculations should still give the correct answer.) 2
B 3 In B25 calculate the total monthly overheads 1
Section C The following questions are to be answered on the Cost Drivers Sheet (using the values in the ABC sheet): Marks 16
C 1 In C4 calculate the total number of setup hours for one month. In D4 calculate the cost for 1 hour. 3
C 2 In C5 calculate the total machine hours for one month. In D5 calculate the cost of one machine hour. 3
C 3 In C6 calculate the total number of sales invoices for one month. In D6 calculate the cost of one sales invoice. 2
C 4 In C7 calculate the total number of orders (supplier invoices) for one month. In D7 calculate the cost of placing one order. 2
C 5 In C8 calculate the total number of employees. In D8 calculate the cost of payroll for one employee. 2
C 6 In C9 calculate the total number of units made in one month. In D9 calculate the cost of packaging for one unit. 2
C 7 In C10 calculate the total square metres used. In D10 calculate the cost per square metre. 2
Section D The following questions are to be answered on the BOM and Inventory Sheets: Marks 17
D 1 In the Inventory sheet convert the data in A3:I61 to a table. Change the name of the table to Inventory. 2
D 2 In the Inventory sheet name the range K4:L9 Suppliers. (This should be a named range NOT a table). 1
D 3 In the BOM sheet convert the data in A3:H39 to a table. Change the name of the table to BOM. Answer all the rest of this section in the BOM sheet. 2
D 4 For each component part code look up the description in the Inventory sheet. 2
D 5 For each component part code look up the unit of measure (per) in the Inventory sheet. 2
D 6 For each component part code look up the unit price in the Inventory sheet. 2
D 7 For each component part code look up the discount rate for the supplier of that component. (This is a challenge question, if it is too hard skip it and just leave blank). 4
D 8 Calculate the total cost of each component less the discount. (1 mark will be awarded if the cost is calculated correctly without the discount.) 2
Section E The following questions are to be answered on the Product Costs Sheet: Marks 21
E 1 Calculate the Set-up cost for each product using the calculated cost driver and information in the ABC sheet. 2
E 2 Calculate the Machinery running for each product using the calculated cost driver and information in the ABC sheet. 1
E 3 Calculate the Receiving for each product using the calculated cost driver and information in the ABC sheet. 2
E 4 Calculate the Payables administration for each product using the calculated cost driver and information in the ABC sheet. 2
E 5 Calculate the Payroll preparation and payment for each product using the calculated cost driver and information in the ABC sheet. 2
E 6 Calculate the Packing for each product using the calculated cost driver and information in the ABC sheet. 1
E 7 Calculate the Utilities for each product using the calculated cost driver and information in the ABC sheet. 2
E 8 In B11:D11 Calculate the total cost of overheads for each product. 1
E 9 Using information in the BOM, calculate the cost of Direct Materials for each product. 2
E 10 Calculate the cost of Direct Labour for each product using values in the ABC sheet 1
E 11 In row 17 pull through the calculated overheads from row 11 1
E 12 Calculate the total cost per unit for each product 2
E 13 Using the sell price in the ABC sheet, calculate the profit made on each product (in some cases this may be negative, indicating a loss) 1
E 14 Calculate the Gross Margin for each product (as a percentage). 1
Section F The following questions are to be answered on the Inventory Sheet: Marks 15
F 1 In column G use a formula to determine if the stock level is below the minimum (specified in L18), if it is put a -Y- in the reorder column otherwise leave it blank (i.e. put “”) 2
F 2 In column H, if the stock level is below the minimum look up the number to reorder in the Reorder Quantities data. Reorder amounts are based on unit, so if it is a set we reorder 20 but if it is a litre we reorder 75. Note these values can change and your formula should still work. 3
F 3 In column I, calculate the cost of the reorder using the unit price, quantity to be reordered and supplier discount. 4
F 4 In column M, calculate how many products (unique product codes) are supplied by each supplier. 2
F 5 In column N, calculate the total cost to Re-order from each supplier. 2
F 6 In L19 calculate the total value of the current inventory (stock on hand). To do this you will need to find out how to use the SUMPRODUCT function. 2
TOTAL MARKS 85