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

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

hi i have problem with my quiz so i would to submit that with you by taking screen shot and giving the supplementary with that. it would be really helpful for me if you could finish this for meBUACC5933Cost and Management AccountingSemester 2, 2019AssignmentContribution to overall assessment: 25%Due Date Thursday 26th September, 2019 at 11.59PMYour text includes discussions on “People, Planet...TaskSummary: Your small team of IT professionals has been tasked to prepare a submission for potential investors that allows them to understand the potential social, ethical and legal opportunities and...Jim Collins stated that a company does not go from good to great overnight but by relentlessly pushing a giant heavy flywheel in one direction, building momentum until a point of breakthrough. The power...Name: Consumer Behaviour and Marketing PsychologyAssessment Title: Individual ReportLength: 2,300 wordsAssessment DescriptionThis assessment is a culmination piece, in which you will be given an opportunity...Part B – Written ReflectionSubmit a written reflection on your experience of working in a team during the trimester using Tuckman’s model of group dynamics as the framework (1750 words, worth 20 marks).This...SNUG105:Person and Family Centred CareASSESSMENT DETAILSAssessment 3EssayDue date Thursday 10th October week 10 before 17:00Weighting 40%Submission Your assignment will be submitted into a Turnitin submission...**Show All Questions**