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 you Enable 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, 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 the correct Excel file, (Numbers files will not be accepted) and if it has a ~ in front and is only 1KB it is not the correct file (you didn’t close). Also please ensure 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 carefully.
Question Instruction
| Section A | The following are to be completed in the Labour HR Data sheet: |
| A1-A8 | 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.
|
| Section B | The following are to be completed in the Labour Job List 2020 sheet: |
| B1 | This sheet contains a list of jobs completed this year up to August. Unhide column C so you can see the date each job started. |
| B2 | Column G is so narrow we can’t see the values. Widen column G so it is roughly as wide as column H. |
| B3 | In J4 add up the Material, Labour and Overhead costs for the first job. Copy the formula down to J21. |
| B4 | Jobs 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. |
| B5 | In 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. |
| B6 | In L4 add the Penalty incurred to the Costs before Penalties to get the Total Cost. Copy for formula down to L21. |
| B7 | In column M calculate the dollar profit margin (Amount Quoted less Total Costs). |
| B8 | In Column N calculate the Gross Profit Margin (as a percentage). |
| B9 | In G23 calculate the total material costs. Copy the formula across to M23. |
| B10 | In Q4 calculate the total amount quoted (column B). |
| B11 | In Q5 calculate the largest amount quoted. |
| B12 | In Q6 calculate the smallest amount quoted. |
| B13 | In Q7 calculate the average amount quoted rounded to the nearest dollar. |
| Section C | The following are to be completed in the Inventory Worksheet |
| This sheet contains an inventory of all components required for manufacturing our robotic components. | |
| C1 | Name the ranges: E4:E28 to OnBackOrder
F4:F28 to Overstocked |
| C2 | 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). |
| C3 | Convert the data in A4:H28 to a table. Change the name of the table to Inventory. |
| C4 | 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 return Yes, otherwise leave the cell empty (do not put a space). The formula should copy down. |
| C5 | 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 the On Back-order column and they should not be re-ordered. In G4 enter a calculation that returns Yes if the value in D4 is less than the value in K11 and the item is not on Backorder, otherwise leave the cell empty. |
| C6 | 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.) |
| C7 | In K12 enter a calculation to workout how many items are on Back-order. |
| C8 | In K13 enter a calculation to work out how many Items with a value over $1000 are Overstocked. |
| C9 | We only place an order when we have at least 3 items that need re-ordering. In K14 enter a calculation that returns Yes if there are 3 or more items that require reordering, and otherwise returns No. |
| Section D | The following are to be completed in the JOB_4588 Worksheet |
| In this sheet we will work out the cost of materials. Labour and overheads for Job 4588. | |
| D1 | D14: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. |
| D2 | In H14 enter a formula to look up the Cost for the Item code in D14 from the Inventory table. Copy down. |
| D3 | 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 is actually in stock, if it isn’t the formula should 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. |
| D4 | In the Direct Labour section, in column H, use the Department name to lookup the appropriate rate from the Information sheet. |
| D5 | In column I calculate the total direct labour costs for each row. |
| D6 | In H41 calculate the total number of labour hours for the department shown in C41 from the Direct Labour section. Copy the formula down. |
| D7 | 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. |
| D8 | 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:
|
| D9 | In D54 calculate the total costs. |
| D10 | In D55 pull through the Quoted Price for this job from the Information Sheet. |
| D11 | From August the calculation of penalty for late delivery has changed. A lookup table has been included in the Information sheet 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. |
| D12 | In I55 calculate the penalty amount and deduct it from the Quoted Price. |
| D9 | In I56 calculate the gross profit margin. |
| Section F | The following are to be completed in the Journals Worksheet |
| E1 | Complete the journal entries for Job_4588. |
For solution, please contact at info@myassignmentguru.com or WhatsApp at +1(450)800-2020.