could you help me to work out these instructions and what formulas and how steps to use in excel would be greatly appreciated?
G H 1 Completed Job Summary for 2019 Days Job No Manager, Date startedly Date completed M take 4 JOB_4950 Martin 02-01-2019 20-02-2019 49 5 JOB_4951 Toni 25-01-2019 02-03-2019 6 JOB_4952 Martin 26-01-2019 19-02-2019 7 JOB_4953 Toni 26-01-2019 22-02-2019 27 8 JOB 4954 Amir 11-02-2019 29-03-2019 9 JOB_4955 Amir 16-02-2019 16-03-2019 10 JOB_4956 Amir 20-02-2019 11-04-2019 11 JOB 4957 Martin 22-02-2019 04-03-2019 12 JOB 4958 Toni 06-03-2019 24-03-2019 13 JOB_4959 Toni 19-03-2019 06-04-2019 14 JOB_4960 Amir 02-04-2019 08-05-2019 15 JOB_4961 Amir 15-04-2019 05-05-2019 16 JOB 4962 Toni 17-04-2019 06-06-2019 17 JOB 4963 Amir 27-04-2019 05-05-2019 18 JOB_4964 Amir 12-05-2019 26-06-2019 19 JOB 4965 Amir 05-06-2019 09-07-2019 20 JOB_4966 Amir 07-06-2019 13-07-2019 21 JOB_4967 Toni 13-06-2019 23-06-2019 22 JOB_4968 Toni 26-07-2019 01-08-2019 23 JOB 4969 Toni 01-08-2019 14-08-2019 24 JOB 4970 Toni 03-08-2019 27-08-2019 Man hour 784 317 384 324 552 381 760 120 259 259 432 400 920 90 756 490 403 Material cost 77077.00 71352.00 31104.00 25974.00 43930.00 55356.00 49200.00 13730.00 17154.00 29682.00 46152.00 25480.00 93150.00 12896.00 84150.00 58310.00 37332.00 14970.00 7146.00 14768.00 40344.00 Labour overheas Total Cos 18032.00 7416.22 102525.22 6974.00 2998.65 81324.65 11520.00 3632.43 46256.43 7128.00 3064.86 36166.86 16008.00 5221.62 65159.62 8763.00 3604.05 67723.05 16720.00 7189.19 73109.19 2400.00 1135.14 17265.14 6993.00 2450.00 26597.00 7511.00 2450.00 39643.00 9504.00 4086.4959742.49 11200.00 3783.78 40463.78 19320.00 8702.70 121172.70 1980.00 851.35 15727.35 19656.00 7151.35 110957.35 13230.00 4635.14 76175.14 10881.00 3812.16 52025.16 4480.00 1513.51 20963.51 1296.00 454.05 8896.05 4050.00 1277.03 20095.03 7500.00 2364.86 50208.86 Invoice amount = 105570.00 79110.00 53280.00 42040.00 56940.00 61550.00 85700.00 19030.00 28250.00 40540.00 55660.00 40710.00 132710.00 14880.00 109000.00 70110.00 51590.00 25670.00 9370.00 22020.00 55980.00 $ Margir, 3044.78 -2214.65 7023.57 5873.14 -8219.62 -6173.05 12590.81 1764.86 1653.00 897.00 -4082.49 246.22 11537.30 -847.35 -1957.35 -6065.14 -435.16 4706.49 473.95 1924.97 5771.14 G.P.% 2 .88 -2.80 13.18 13.97 -14.44 - 10.03 14.69 9.27 5.85 2.21 -7.33 0.60 8.69 -5.69 -1.80 -8.65 -0.84 18.33 5.06 8.74 10.31 160 48 135 250 B1 In B3 calculate the total number of days spent on the completed jobs. B2 In B4, use the values in the Completed Jobs sheet to calculate the total revenue. 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.) In B6, use the values in the Completed Jobs sheet to calculate the Highest Labour Cost. B4 B5 In B7, use the values in the Completed Jobs sheet to calculate the Lowest Labour Cost. B6 In B8, use the values in the Completed Jobs sheet to calculate the Number of Completed Jobs. 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) 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. B8 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. 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. B10 B11 B9 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. 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.) . 1 Job Stats for 2019 2 5 Total Days Work 4 Total Revenue Average Material Cost 6 Highest Labour Cost Lowest Labour Cost 8 Number of Completed Jobs 9 Highest Revenue Job No. 10 7 11 Number of Jobs Jobs with a loss Average Revenue Total $ Profit 12 Manager 13 Amir 14 Toni 15 Martin 16
