Excel Assignment 2021 S1 Instructions
Scenario
The Bass Brothers Guitar Company make three types of guitar. Till now the company have used a traditional product costing system but they have decided to switch to ABC costing and have 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 grey 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 next pages. Please follow each of the instructions precisely.
Good luck ??
Question Instruction Marks Done
Section A The following are to be completed in the Labour HR Data sheet: 8
A1-A8 This sheet contains a list of employees at the company followed by a series of multiple-choice questions. Some of the questions are general, some refer to the data. Each question has only one correct answer, please indicate the correct answer by changing the corresponding value in column F from FALSE to TRUE. Only change one option for each question or it will be marked wrong.
8 ?
Section B The following questions are to be answered on the Labour Costs Sheet: 16
B1 Column D contains the number of hours each staff member works during the standard working week. Some staff are required to work a day or a half day on the weekend, these hours attract a higher rate and so are shown separately in column E. In F10 calculate the total weekly hours for that employee. Copy the formula down to F21. 1 ?
B2 Change the number formatting of the cells F10:F21 to General. 1 ?
B3 B4 contains the percentage extra paid on weekends. In G10 use the value in B4 to calculate the weekend rate. Use appropriate referencing and drag the formula down to G21. 2 ?
B4 Widen column H so that it is roughly the same width as the other columns. 1 ?
B5 In H10 calculate how much that employee gets paid for a week. Copy the formula down to H21. 1 ?
B6 In cell I10 calculate the Employee’s average hourly rate. Copy the formula down to I21. 1 ?
B7 In D23 calculate the total standard hours worked. Drag the formula across to F23. 1 ?
B8 In I4 calculate the highest standard hourly rate. 1 ?
B9 In I5 calculate the lowest standard hourly rate. 1 ?
B10 In I6 calculate the average standard hourly rate and then apply a rounding function to round it to 0 decimal places. 2 ?
B11 Full time staff work a total of 40 hours/week. In B5 calculate what percentage of staff are full time. (Your calculation should still give a correct answer if staff hours change). 3 ?
B12 Name the range B10:B21 Product_Line
1 ?
Section C The following questions are to be answered on the Activity Based Costs Sheet: 5
C1 In B13, using the information in the Labour Costs sheet, calculate how many staff are assigned to the Tenor Electric Product line. Drag the formula across to D13. 2 ?
C2 Direct labour costs per hour are calculated by adding the average hourly rates of the employees assigned to that product line. In B14 calculate the direct labour costs per hour for Tenor Electric, then drag the formula across to D14. 2 ?
C3 In B17 calculate the total monthly overheads 1 ?
Section D The following questions are to be answered on the Cost Drivers Sheet (using the values in the ABC sheet): 11
D1 In C4 calculate the total number of setup hours for one month. (Please note that the set-up has to be done for each production run.) 2 ?
D2 In C5 calculate the total machine hours for one month. 2 ?
D3 In C6 calculate the total number of sales invoices for one month. 1 ?
D4 In C7 calculate the total number of orders (supplier invoices) for one month. 1 ?
D5 In C8 calculate the total number of employees. 1 ?
D6 In C9 calculate the total number of units made in one month. 1 ?
D7 In C10 calculate the total square metres used. 1 ?
D8 In D4 calculate the cost of 1 set-up hour. Copy the calculation down to D10. 2 ?
Section E The following questions are to be answered on the BOM Sheet: 13
E1 In the BOM sheet convert the data in A3:H39 to a table. Change the name of the table to BOM. (This is not a named range, ensure you change the table name.) 2 ?
E2 In column D for each component part code use a calculation to look up the description in the Inventory sheet. 2 ?
E3 In column E for each component part code use a calculation to look up the unit of measure (per) in the Inventory sheet. 2 ?
E4 In column F for each component part code use a calculation to look up the unit price in the Inventory sheet. 2 ?
E5 In column G 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). 3 ?
E6 In column H 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 F The following questions are to be answered on the Product Costs Sheet: 23
In this section we calculate the costs involved in making one unit of each product.
F1 Calculate the Set-up cost for each product using the calculated cost driver and information in the ABC sheet. 3 ?
F2 Calculate the Machinery running cost for each product using the calculated cost driver and information in the ABC sheet. 1 ?
F3 Calculate the Receiving cost for each product using the calculated cost driver and information in the ABC sheet. 2 ?
F4 Calculate the Payables administration cost for each product using the calculated cost driver and information in the ABC sheet. 2 ?
F5 Calculate the Payroll preparation and payment cost for each product using the calculated cost driver and information in the ABC sheet. 2 ?
F6 Calculate the Packing cost for each product using the calculated cost driver and information in the ABC sheet. 1 ?
F7 Calculate the Utilities cost for each product using the calculated cost driver and information in the ABC sheet. 2 ?
F8 In B13:D13 Calculate the total cost of overheads for each product. 1 ?
F9 In B14:D14, using information in the BOM, calculate the cost of Direct Materials for each product. 2 ?
F10 In B15:D15 Calculate the cost of Direct Labour for each product using values in the ABC sheet (multiply direct labour hours by direct labour costs per hour). 1 ?
F11 Using the data in B13:D15 and any appropriate data labels, create a 2-D stacked column chart to compare the cost breakdowns for the three products. Change the chart title to Product Costs. Using charting tools (not drag and drop) reposition the legend at the top of the chart. Remove the gridlines. The chart should look something like this, but values will vary.
4 ?
F12 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 ?
F13 Calculate the Gross Margin for each product (as a percentage).
1 ?
Section G The following questions are to be answered on the Inventory Sheet: 14
G1 The inventory table contains a list of all the items kept in stock to make our guitars. The minimum quantity we need to have in stock of these items is shown in M18, this value may be changed by the inventory manager. In H4 enter a formula to check if the In Stock amount is below the minimum stock if it is return a “Y” otherwise leave the cell empty. The formula will automatically copy down. Note: your formula must still work if the minimum stock amount changes. 2 ?
G2 Some low stock items have already been re-ordered, these are indicated with a “Y” in the Backorder column and do not need to be re-ordered. The quantities to reorder are determined by the unit (Per) and are provided in a lookup table in L12:M16. In I4 create a calculation to look up the correct re-order quantity for that item if it has fallen below the minimum stock level and is not on back order. If it does not need to be re-ordered your calculation should return 0. (Do not put the 0 in quotes.) 4 ?
G4 In J4 create a calculation to work out the cost to re-order the number indicated in I4, this must factor in the discount offered by the relevant supplier. (Where there are no items to re-order the calculation should return 0.) 3 ?
G5 A named range has been added called On_Back_Order but it only goes down to row 30. Edit the named range so it extends to row 61. (Do not create another named range, change the existing one.) 1 ?
G6 In O4 calculate the average cost of items supplied by that supplier. Copy the formula down to O9. 2 ?
G7
In P4 calculate the number of items on back-order for that supplier. Copy the formula down to P9. 2 ?
TOTAL MARKS 90