Scenario

Quantum Robotics are a manufacturing company who supply custom made robotic components. You are required to provide a spreadsheet that will help cost individual jobs, provide a summary of completed jobs for the year and help manage inventory.

Start by downloading the assessment workbook from iLearn and copying/moving it to an appropriate folder. When you open the Excel file it is very important that youEnable Macros and 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. Please note the first three worksheets are locked, and you will only be able to change the cells specified.

General Guidelines

• The assessment can be completed on a Windows machine or a Mac ,but you will need to use a desktop version of Excel, preferably 2010 or later. Numbers, Sheets and Excel online are not suitable.
• All the light grey cells are where your answers need to go, please do not change any other cells.
• Except for the journal names, all the answers require a calculation or cell reference, 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 is encouraged, and in certain cases required but unless stipulated calculations
• do not need to use named ranges.
• Please do NOT create tables other than where directed.
• Because you are developing a model that we may wish to use for future jobs with different values it is very
• important that all calculations return a correct answer regardless of the values in the spreadsheet and must still work correctly when the lists of data, like Inventory and Wage Rates are sorted in a different order.
• Double check your answers as you go as one wrong result may affect other results.
• To get full marks for a question both the formula AND the answer must be correct. Partial marks may be
• awarded for correct or partially correct formulas, but this will be at the marker's discretion.
• Use the check boxes in the Done column to keep track of tasks completed.
• Remember to save often.
• When you are ready to submit,save,closeanduploadthe 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 the correctExcel file, (Numbers files will not be accepted) and if it hasa ~ in front and is only 1KB it is not the correct file (you didn't close). Also please ensurethat there are no"non-English" characters in the file name as these files cannot be opened in anEnglish version of Excel.
• Detailed instructions are provided on the next pages. Please follow each of the instructions carefully.

Question Instruction Marks Done

Section A

The following are to be completed in the LabourHR Datasheet:

8

This sheet contains staff information, 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 are to be completed in the LabourJob List 2020sheet:

14

A1-A8

1. B1This sheet contains a list of jobs completed this year up to August. Unhide column C so you can see the date each job started.
2. B2Column G is so narrow we can't see the values. Widen column G so it is roughly aswide as column H.
3. B3In J4 add up the Material, Labour and Overhead costs for the first job. Copy the formula down to J21.
4. B4Jobs that ran late incurred a late penalty charged as a percentage of the total amount (column B) for each day they ran late (column F). The percentage used is in Q9. Change the number format for Q9 to be percentage with one decimal place.
5. B5In cell K4, using the information in columns B and F and the percentage In Q9, calculate the late penalty for the first job. Use appropriate cell referencing so that the formula can be copied down. Copy the formula down to K21.
6. B6In L4 add the Penalty incurred to the Costs before Penalties to get the Total Cost. Copy for formula down to L21.
7. B7In column M calculate the dollar profit margin (Amount Quoted less Total Costs).
8. B8In Column N calculate the Gross Profit Margin (as a percentage).
9. B9In G23 calculate the total material costs. Copy the formula across to M23.
10. B10In Q4 calculate the total amount quoted (column B).
11. B11In Q5 calculate the largest amount quoted.
12. B12In Q6 calculate the smallest amount quoted.
13. B13In Q7 calculate the average amount quoted rounded to the nearest dollar.
14. This sheet contains an inventory of all components required for manufacturing our
15. robotic components.

C1Name the ranges: E4:E28 toOnBackOrder

F4:F28 toOverstocked

C2

C3Convert the data in A4:H28 to atable. Change the name of the table toInventory. C4

C5

C6

1111

11

1111112

2

2

Section C

The following are to be completed in theInventoryWorksheet

21

Have a look at the calculation in K9 that is calculating the total value of our stock in inventory. It is using named ranges but these named ranges stop at row 22 so it is not returning the correct answer. Update both the named ranges so they go down to row 28. (You can add any other named ranges you feel may be helpful).

1

K10 contains the maximum number we should have in stock of any item, but in some cases we are overstocked. In F4 enter a calculation to check if the value in stock (D4) is greater than the value in K10, if it is returnYes, otherwise leave the cell empty (do not put a space). The formula should copy down.

2

Where number In Stock falls below the minimum required stock level shown in K11, we need to re-order that item. Some orders may already have been re-ordered but not come into stock, they are indicated with a Yes in theOn Back-ordercolumn and they should not be re-ordered. In G4 enter a calculation that returnsYesif the value in D4 is less than the value in K11 and the item is not on Backorder, otherwise leave the cell empty.

3

Re-order quantities are based on price. The data in J4:K6 specifies the quantities to re-order for each price bracket, so for example if the item costs from 0 up to (but not including) $300 we re-order 50. In H4 enter a formula that will return the number we need to re-order; this should be 0 if we do not need to re-order. (Note: The values in J4:K6 may be subject to change.)

3

Question Instruction

C7In K12 enter a calculation to workout how many items are on Back-order.

C8In K13 enter a calculation to work out how many Items with a value over $1000 are Overstocked.

C9We only place an order when we have at least 3 items that need re-ordering. In K14 enter a calculation that returnsYesif there are 3 or more items that require reordering, and otherwise returnsNo.

D1D14:D19 contains the list of parts required to complete the job. In E14 enter a formula to return the description for the code from the Inventory table. Copy the calculation down.

D2In H14 enter a formula to look up theCostfor the Item code in D14 from the Inventory table. Copy down.

D3

D4In the Direct Labour section, in column H, use the Department name to lookup the appropriate rate from theInformationsheet.

D5In column I calculate the total direct labour costs for each row.

D6In H41 calculate the total number of labour hours for the department shown in C41

Marks Done

1. 2
2. 3

3

22

2

1. 1
2. 2

1

1. 1
2. 2

2

6

Section D

The following are to be completed in theJOB_4588Worksheet

26

In this sheet we will work out the cost of materials. Labour and overheads for Job 4588.

In I14 we need to calculate the total cost for the quantity shown in column G, but we also need to include a check that the item isactually in stock, if it isn't the formulashould return 0. After you have copied down you will observe that there is one 0 value item and it has highlighted in red to warn us. Change the item code from S1009 to S1008 to correct the problem.

3

from the Direct Labour section. Copy the formula down.

Overheads are calculated based on labour hours. The Information sheet contains the Budgeted manufacturing overhead and the Budgeted direct labour hours for the year. Use this information along with the hours worked to calculate the overhead for each department in I41:I43.

2

Insert a Donut chart showing the percentage hours labour for each department on this job. Turn off the Legend and the Chart Title. Add Data Labels that show both the Department Name and the percentage hours. The values are different for each student, but the chart should look something like this:

3

D7

D8

D9In D54 calculate the total costs.

D10In D55 pull through the Quoted Price for this job from theInformationSheet.D11

D12In I55 calculate the penalty amount and deduct it from the Quoted Price.D9In I56 calculate the gross profit margin.

From August the calculation of penalty for late delivery has changed. A lookup table has been included in theInformationsheet showing the new structure, so for example if the job is 8 days late, penalties are calculated at 2% of the total cost per day it is late. In I54 enter a calculation to check if the Date Finished in cell I8 was after the Date Required in I6, if it was calculate the late penalty percentage otherwise return 0.

3

Section F

The following are to be completed in theJournalsWorksheet

6

E1Complete the journal entries for Job_4588.

TOTAL MARKS70