Scenario
Futura robotics manufacture custom made robotic components. Each job is unique and costed individually. You are required to create a spreadsheet that records a list of completed jobs and allows the company to cost up new jobs.
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
- With the exception of the Journal Accounts, which are to be selected from the drop-down list, all the light blue cells require you to insert some form of 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 any way 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 where specified may be required. If not specifically required there will be no penalty for not using them, but it may make the formulas more difficult.
- Because you are developing a model that we will wish to use for future years/jobs or 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 (e.g. Completed Jobs, & 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 correct completed Excel File to iLearn (Excel Submission). You do not need to rename the file as iLearn will automatically attach your student number but please ensure that it is an Excel file you submit, (Numbers files will not be accepted), that it is bigger than 1KB (means you’ve submitted wrong file) 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.
| Section A | The following are to be completed in the Completed Jobs sheet: | 12 | |
| A1 | This sheet contains a list of completed jobs for the year. Column B containing the Manger information has been hidden, unhide column B. | 1 | ☐ |
| A2 | In E4 use a formula to calculate the number of days between date started and date completed. For example, if the job started on the 4th and finished on the 24th of January, the days taken would be 20. Copy the formula down to E24. | 1 | ☐ |
| A3 | Change the formatting of Days Taken values to be General (number with no decimals). | 1 | ☐
|
| A4 | In I4:I24, use the Man Hours provided along with the information on overheads in the Information sheet, to calculate the manufacturing overhead for each job | 2 | ☐ |
| A5 | In J4:J24 calculate the total costs for each job. | 1 | ☐ |
| A6 | Widen column J so that all the values are clearly visible. | 1 | ☐ |
| A7 | In L4:L24 calculate the profit margin ($) for each job. | 1 | ☐ |
| A8 | In M4:M24 calculate the Gross Profit Margin for each job. (Note: in some cases there may be a loss which will give a negative answer.) | 1 | ☐ |
| A9 | Format the Gross Profit Margin figures to show as a percentage. | 1 | ☐ |
| A10 | Convert the data in A3:M24 to a Table. Name the table Jobs. (NB name the table, not the range). | 2 | ☐
|
| Section B | The following are to be completed in the Stats Worksheet | 23 | |
| Read through the questions and determine if adding named ranges will help you, they are not required but may make your life easier. | |||
| B1 | In B3 calculate the total number of days spent on the completed jobs. | 1 | ☐ |
| B2 | In B4, use the values in the Completed Jobs sheet to calculate the total revenue. | 1 | ☐ |
| B5 | In B5, use the values in the Completed Jobs sheet to calculate the Average Material Cost rounded to the nearest 10. (You are required to use a rounding function here.) | 2 | ☐ |
| B4 | In B6, use the values in the Completed Jobs sheet to calculate the Highest Labour Cost. | 1 | ☐ |
| B5 | In B7, use the values in the Completed Jobs sheet to calculate the Lowest Labour Cost. | 1 | ☐ |
| B6 | In B8, use the values in the Completed Jobs sheet to calculate the Number of Completed Jobs. | 1 | ☐ |
| B7 | Challenge: In B9, use the values in the Completed Jobs sheet to calculate which job was the highest revenue job. Note: 1 Mark will be given for just identifying the highest revenue amount, but to get full marks we need the associated Job No.
(Hint: Investigate the INDEX function) |
3 | ☐ |
| B8 | In B13, use the values in the Completed Jobs sheet to calculate the total number of jobs managed by Amir. Copy the formula down for Toni and Martin. | 2 | ☐ |
| B9 | In C13, use the values in the Completed Jobs sheet to calculate the number of jobs managed by Amir that made a loss (negative margin). Copy the formula down for Toni and Martin. | 3 | ☐ |
| B10 | In D13, use the values in the Completed Jobs sheet to calculate the average revenue of jobs managed by Amir. Copy the formula down for Toni and Martin. | 2 | ☐ |
| B11 | In E13, use the values in the Completed Jobs sheet to calculate the total $ profit for jobs managed by Amir. Copy the formula down for Toni and Martin. | 2 | ☐ |
| B9 | In the Stats sheet, create a 2-D Pie Chart showing the Total $ profit for each manager. Ensure there is only one chart and that it has the title Profit by Manager. Show data labels as percentages on the pie segments and show the legend at the bottom of the chart. (Please follow instructions exactly.)
|
4 | ☐ |
| Section C | The following are to be completed in the Inventory Worksheet | 20 | |
| C1 | Go to the Inventory Sheet. This sheet contains a list of all the items the company keep in stock to build the robotics. Each part is identified by a unique code. Convert the Range A3:I53 to a Table. Name the table Inventory. | 2 | ☐ |
| C2 | Name the following ranges:
A4:A53 Code E4:E53 Cost_Price (and any other ranges you feel may prove useful) |
2 | ☐ |
| C3 | In F3:F53 calculate the retail price (paid by customer) using the Cost Price and the % Markup in L3. (Note the markup may be changed and your calculation should still produce the correct answer.) | 1 | ☐
|
| C4 | L4 indicates the maximum stock level. In G3:G53 create a formula to check if the quantity in stock is over the maximum stock in L4 (this level can be adjusted). If it is over the stock level put Yes in the cell, otherwise leave it empty (do not put No or a space). Formula should copy down, check it is working correctly. | 2 | ☐
|
| C5 | L5 indicates the minimum stock level while column D indicates if an item is on back order (already been reordered). If a stock item is below the minimum and not on Backorder (is blank) we need to reorder it. In H3:53 create a formula to check if the item needs to be re-ordered. If it does put Yes in the cell, otherwise leave it empty
(do not put No or a space). Formula should copy down, check it is working correctly. |
3 | ☐
|
| C6 | Reorder quantities are based on price as shown in the Reorder Quantities data (K11:L16), e.g. currently for items that cost from $100 up to (but not including) $300 we re-order 35. Create a calculation in I4 that will check if we need to reorder and if so return the correct reorder amount otherwise return 0. Ensure the formula is copied down for the whole table. Note stock levels and reorder quantities will change, you should have one consistent calculation for the column and the calculations should work regardless of values or sort order of the inventory data. | 4 | ☐ |
| C7 | In L6 calculate how many item codes require reordering. | 2 | ☐ |
| C8 | In L7 calculate how many item codes are over stocked. | 2 | ☐ |
| Use Excel’s built in help to investigate the SUMPRODUCT function. In L8 calculate the total cost of all inventory items currently in stock. | 2 | ☐
|
|
| Section D | The following are to be completed in the JOB_4971 Worksheet | 19 | |
| D1 | This sheet shows the Job Card for the most recent job, but it is missing several calculations. The first cost section is for direct materials. Items used for the job are listed by item code. In F13:F18 use calculations to lookup the correct description for each item from the Inventory table. | 2 | ☐
|
| D2 | In I13:I18 use calculations to lookup and calculate the total cost for each item. (Cost to the company not the client). | 3 | ☐ |
| D3 | In I21 calculate the total costs for direct materials. | 1 | ☐ |
| D4 | In H25:H31 use a formula to lookup the correct hourly rate for each department from the Information sheet. | 2 | ☐ |
| D6 | In I25:I31 calculate the labour costs for each task. | 1 | ☐ |
| D7 | In I33 calculate the average hourly rate. (What it cost on average for one hour of labour on this job.) | 1 | ☐ |
| D8 | In I34 calculate the total labour costs. | 1 | ☐ |
| D9 | In H37 use a calculation to determine the total direct labour hours for the department shown in C37. (Note the formula must work for different scenarios. you can’t assume that C26 and C29 will always be Assembly). Copy the formula down to H39. | 2 | ☐ |
| D10 | In I37:I39 use the data in the Information worksheet to determine the manufacturing overheads for each department. | 2 | ☐ |
| D11 | In I42 calculate the total manufacturing overheads. | 1 | ☐ |
| D12 | In I45 calculate the total costs. | 1 | ☐ |
| D13 | In I46 reference (pull through) the Invoice Amount from the Information Sheet. | 1 | ☐ |
| D14 | In I47 calculate the Gross Profit Margin. | 1 | ☐
|
| Section E | The following are to be completed in the Journals Worksheet | 6 | |
| E1 | Complete the journal entries for Job 4971. | 6 | ☐ |
Please contact us at info@myassignmentguru.com or whatsapp at +1(450)800-2020 for solution.