please help with the excel formulas and answers for the blue highlighted columns.
instrustions attached 
Cost of Ingredients for Menu Options O Menu bibe Avec Medium Avenes Price La Y Y Cather Mains Finger Food Salad Higher Price Lower Me Menu Cod 2627 1151 1236 3039 1261 1003 3341 2948 3821 1316 3053 3992 390B 2864 3868 3295 2697 1189 2346 1770 3924 1873 2267 Incredient Com Medium Lero $53 $80 $83 $125 $83 $125 $53 $80 $113 $50 $63 $45 $6B $35 $53 $52 $66 $66 $66 $40 $34 $51 $46 $53 $35 $83 $53 $125 $117 $53 $53 $35 $35 Category Maina Mains Mains Mains Mains Mains Maina Mains Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Mains Finger Fooch Finger Fooch Finger Food Fincer Food Finger Food Finger Food Finear Food Fine Food Finger Food Fine Food Finger Fooch Finger Food Finger Fooch Finger Food Finger Fooch Finger Fooch Finger Fooch Fincer Fooch Finger Fooch Dish Roare Turkey Planer Whole Turkey Roat with Sutina Honey Maple Glazed Lea Hom Second Road Pork with Crackling Traditional Lamb Roar Fruit Cake with Froting and Decoration Lana Vegetarian Gnocchi (Spinach and Ricotta Cannelloni (Boh Laanathe way that Mamma maka Tortellini ala Panna Ravioli Fresh Soinsch and Rice Ravioli Fresh Meal Gnocchi Con Patate Soaches Blonde Siciliana Marinara Galic. Prown and Panda Cannelloni Soinash & Riconal Eccolant Parmidiana Pineapole Clared Ham Italian Pot Road Roce Vecutable Chicken Color Chicken Stroon 380 Chicken Plane Herb Guned Lea of Lamb Roar Chicken Roll Pamer Chicken Planter Trio Vecstable Kebab Gurry Puth Fih. Prown and Calamari Planer Sushi Aroncini Panter-Baby Size Aroncini Panter-Baby Size Vegetarian Vecretarian Planter Cooked Mini Quiche Mini Vegetarian Pies Ribbon Sandwiches - Vecetario Foccacia Plamer. Vestarian Meatball Plateralian Style Pizza Pe Vectarian Pizza Plamer Gutlet pieces and Aroncini Planer Foccacia Planer Gumbed Calamar Planer Fresh Seafood Home Antia (Mixed Meal Chem and Olive $38 $63 $113 $75 2792 1780 1913 3750 1660 3483 3781 3523 $46 $37 $42 $53 $56 $63 $80 $65 $45 $6B $66 $53 $35 $36 1831 1185 2965 3413 2150 1793 3203 3726 2379 3414 1042 2517 $54 $63 $56 $56 $56 $37 $37 $35 $63 $53 $72 2841 Finger Food $50 350 2135 2154 $33 $52 $39 $28 $28 $32 $59 $42 Y $62 1702 b072 5923 1575 25 23 21 364 $45 $62 $28 $30 $41 $50 $58 $35 Y Y Finger Food Fincer Fooch Finger Food Finger Fooch Finger Fooch Fincher Food Finger Fooch Fincher Food Finger Fooch Finger Food Finger Fooch Finger Fooch Fincer Fooch Fincher Fooch Fine Fooch Finger Fooch Finger Fooch Finger Fooch Finger Fooch Fincher Food Finger Fooch Fincher Food Finger Fooch Finger Fooch Salach Salach Cooked Prawn Homer Lemon Chicken Fincan Pamer Proxiu Hom with Malon Vectarian Pamer Hendes Ribbon Sandwishes Mini Sorina Rolle Mini Samoa Cheer Tomato Bail Murah Scalloa and Bones Coconut Prown with Mans Save Smoked Salmon and Camembert Putte Lemon Ginger Prawna Baked Owen with Garlic Herb Butter Chicken Drumme Chicken Cheese Paris Clared Chicken Bites Mini Size Maxi Tante Sauce Rolls Steamed Chilli Prawne Soinash & Ricotta Puth Party Planner Chicken Yakitori Chee & Herb Potato Couette Chicken Gute Soisy Ea'n Bacon Salad Planer Julienne Vectable Salad $102 $53 $90 $75 a 152 $50 $32 $31 2800 2992 2009 3906 1216 3791 $32 $55 $B3 $56 $48 $59 $59 $6B $32 $39 $39 $45 $29 $30 1181 3595 3092 2960 $45 Catering Job Costs Cient Pamia Technologies Job Number 4155 Condos Job Description Sales Conference 11/8/20 3 2 DAR Category Cost/Unit Total Cost 3 FOOD COSTS (MATERIALS) Menu Code Quantity 3341 1 2697 2 1873 1 2267 1 1780 2 1913 3 1660 1 3523 3 2965 3 1793 1 2557 3 3776 1 2841 1 2135 3 3693 3 3923 3 2518 1 2992 1 1181 1 3262 3 3436 3 3810 1 2874 1 1721 3 3361 3 2570 2 Size Large Large Large Large Large Large Medium Medium Large Medium Large Large Medium Medium Large Medium Large Large Large Medium Large Medium Medium Large Medium Medium TOTAL FOOD COSTS LABOUR COSTS De Staff Number Day Cos! Hours 5 8 8 17/8/20 18/8/20 19/8/20 19/8/20 20/8/20 20/8/20 21/8/20 Temp Start Temp Chef Temp Cher Temp Chel General Help Temp Chet Wait Statt General Help 2 2 2 2 3 2 Details Description Prepare Ingredients Preparation and Cooking Cooking Setup al venue Cooking and Plaring Table Service Pack up and clean up TOTAL LABOUR PRODUCTION OVERHEADS Days Cost TOTAL OVERHEADS SUMMARY TOTAL COST $ 12,400.00 Quoted Price before GST) Grom Profil Margin( B D Journal Entries for JOB_4155 5 Account Debit Credit $0.00 $0.00 Available Journal Accounts Accounts Payable Accounts Receivable Manufacturing Overhead Cost of Goods Sold Finished Goods Inventory Materials Inventory Sales Wages Payable Work in Process Inventory Purchase of Direct Materials (on Account) 3 Materials Inventory Accounts Payable 0 1 Issue of direct materials to production 2 Work in Process Inventory 3 Materials Inventory 4 5 Direct labour incurred 6 Work in Process Inventory 7 Wages Payable 8 9 Overhead applied to production o Work in Process Inventory 1 Manufacturing Overhead 2. 3 Cost of finished production transferred to Finished Goods store 4 Finished Goods Inventory 5 Work in Process Inventory 6 7 Cost of finished product sold 8 Cost of Goods Sold 9 Finished Goods Inventory 0 1 August Sales 2 Accounts Receivable 3 Sales 4 5 6 7 8 9 0 1 2 3 4 5 6 Section C The following are to be completed in the Client Database Worksheet This sheet contains a list of clients, when they joined and the number of catering jobs they have booked with us. ci Name the cells 16:154 Client_Status. C2 The formula in L15 is using the named range Jobs to add up the total number of catering jobs done for all clients. It is not returning the correct answer because the Named Range is not correct. Change the named range Jobs to include all cells from F6:F54. The value in L15 should now be correct. Convert the data in A5:154 to a table. Change the name of the table to ClientDB. C4 In column G we want to identify all clients who joined in the last 9 months. The date 9 months ago has been calculated in L11. In G6 create a calculation to put Yes if the client start date was on or after the date shown in L11. If they joined before then, leave the cell empty (do not put a space). The formula should copy down automatically CS We have decided to give a gift to thank customers for their loyalty. Create a calculation in H6 to return Gift if the client joined before the date shown in L12 or have purchased 15 or more jobs. If neither is true, leave the cell empty. The formula should copy down. C6 In L13 create a calculation to work out how many New clients we have. In L14 create a calculation to work out how many gifts we are giving away. C8 Customers are awarded a status based on how many jobs they have booked. The data in K6: L9 shows the minimum number of jobs required to achieve each status, so for example, a customer who has booked between 10 and 19 jobs gets Gold status. In the status column enter a calculation to calculate the status for each customer using the data provided in K6:L9 (these values may change). Question Instruction c9 In M6 create a calculation to work out how many Clients have Bronze status. Copy the formula down to M9. C10 In N6 create a calculation to work out how many jobs Clients with Bronze status have booked. Copy the formula down to N9. cu Create a Donut chart to show the percentage of clients of each status. Change the Chart Title to Client Status and add Data Labels to show percentages. Use Chart Element tools to position the legend at the bottom of the chart (do not drag). Section D The following are to be completed in the Menu Worksheet This sheet contains Menu options with associated costs D1 In cell 112 use a formula to calculate the highest price in D7:E113. DZ In cell 113 use a formula to calculate the lowest price in D7:E113. . D3 In 17 create a calculation to work out how many Mains dishes are available (Category is Mains and Off Menu is not set to Y). Copy down to 110. D4 In 17 create a calculation to work out the average cost of Medium size Mains dishes. Use appropriate referencing so that the formula can be dragged down and across. (1 mark for correct referencing.) Section E The following are to be completed in the JOB_4155 Worksheet In this sheet we need to be able to cost up different jobs. E1 D6 contains the name of the client we are doing the job for. In D7 enter a formula to look up the contact person for this client in the Client Database sheet. E2 In F14 enter a formula to look up the Dish Name for the Menu code in C14 from the Menu table. Formula should copy down. E3 In G14 enter a formula to look up the Category for the Menu code in C14 from the Menu table. Formula should copy down. E4 In H14 enter a formula to look up the cost for the Menu code in C14 and size in E14 from the Menu table. Formula should copy down. E5 In 114 enter a formula to calculate the Total Cost for that Menu item. Include a check in the calculation to see if the menu item is off Menu, if it is return a cost of O. (Items with a cost of O will automatically highlighted in orange to alert the user that they have selected an item that is not available.) E6 In the labour costs section in H49 enter a calculation to return the name of the day of the week for the date shown in C49. The formula should copy down. In 149 calculate the labour cost for that labour type and quantity using the values in the Cost Overview sheet. (Ensure you apply weekend rates for Saturdays and Sundays. Your calculations must take this into account even if your data does not contain weekend dates as the dates could change.) ES Overheads are calculated by taking the total overheads (see Cost Sheet) and apportioning them over the days worked in the year (see Cost Sheet). In H65 calculate the difference between the first and last date in C49:055 to work out how many days they worked on this job. E9 In 165 use the value in H65 and the overheads information in the Cost Overview sheet to calculate the production overheads. E10 In D69 calculate the total costs. E11 In 170 calculate the gross profit margin. Section F The following are to be completed in the Journals Worksheet Complete the journal entries for Job_4155. E7 F1 Cost of Ingredients for Menu Options O Menu bibe Avec Medium Avenes Price La Y Y Cather Mains Finger Food Salad Higher Price Lower Me Menu Cod 2627 1151 1236 3039 1261 1003 3341 2948 3821 1316 3053 3992 390B 2864 3868 3295 2697 1189 2346 1770 3924 1873 2267 Incredient Com Medium Lero $53 $80 $83 $125 $83 $125 $53 $80 $113 $50 $63 $45 $6B $35 $53 $52 $66 $66 $66 $40 $34 $51 $46 $53 $35 $83 $53 $125 $117 $53 $53 $35 $35 Category Maina Mains Mains Mains Mains Mains Maina Mains Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Maina Mains Finger Fooch Finger Fooch Finger Food Fincer Food Finger Food Finger Food Finear Food Fine Food Finger Food Fine Food Finger Fooch Finger Food Finger Fooch Finger Food Finger Fooch Finger Fooch Finger Fooch Fincer Fooch Finger Fooch Dish Roare Turkey Planer Whole Turkey Roat with Sutina Honey Maple Glazed Lea Hom Second Road Pork with Crackling Traditional Lamb Roar Fruit Cake with Froting and Decoration Lana Vegetarian Gnocchi (Spinach and Ricotta Cannelloni (Boh Laanathe way that Mamma maka Tortellini ala Panna Ravioli Fresh Soinsch and Rice Ravioli Fresh Meal Gnocchi Con Patate Soaches Blonde Siciliana Marinara Galic. Prown and Panda Cannelloni Soinash & Riconal Eccolant Parmidiana Pineapole Clared Ham Italian Pot Road Roce Vecutable Chicken Color Chicken Stroon 380 Chicken Plane Herb Guned Lea of Lamb Roar Chicken Roll Pamer Chicken Planter Trio Vecstable Kebab Gurry Puth Fih. Prown and Calamari Planer Sushi Aroncini Panter-Baby Size Aroncini Panter-Baby Size Vegetarian Vecretarian Planter Cooked Mini Quiche Mini Vegetarian Pies Ribbon Sandwiches - Vecetario Foccacia Plamer. Vestarian Meatball Plateralian Style Pizza Pe Vectarian Pizza Plamer Gutlet pieces and Aroncini Planer Foccacia Planer Gumbed Calamar Planer Fresh Seafood Home Antia (Mixed Meal Chem and Olive $38 $63 $113 $75 2792 1780 1913 3750 1660 3483 3781 3523 $46 $37 $42 $53 $56 $63 $80 $65 $45 $6B $66 $53 $35 $36 1831 1185 2965 3413 2150 1793 3203 3726 2379 3414 1042 2517 $54 $63 $56 $56 $56 $37 $37 $35 $63 $53 $72 2841 Finger Food $50 350 2135 2154 $33 $52 $39 $28 $28 $32 $59 $42 Y $62 1702 b072 5923 1575 25 23 21 364 $45 $62 $28 $30 $41 $50 $58 $35 Y Y Finger Food Fincer Fooch Finger Food Finger Fooch Finger Fooch Fincher Food Finger Fooch Fincher Food Finger Fooch Finger Food Finger Fooch Finger Fooch Fincer Fooch Fincher Fooch Fine Fooch Finger Fooch Finger Fooch Finger Fooch Finger Fooch Fincher Food Finger Fooch Fincher Food Finger Fooch Finger Fooch Salach Salach Cooked Prawn Homer Lemon Chicken Fincan Pamer Proxiu Hom with Malon Vectarian Pamer Hendes Ribbon Sandwishes Mini Sorina Rolle Mini Samoa Cheer Tomato Bail Murah Scalloa and Bones Coconut Prown with Mans Save Smoked Salmon and Camembert Putte Lemon Ginger Prawna Baked Owen with Garlic Herb Butter Chicken Drumme Chicken Cheese Paris Clared Chicken Bites Mini Size Maxi Tante Sauce Rolls Steamed Chilli Prawne Soinash & Ricotta Puth Party Planner Chicken Yakitori Chee & Herb Potato Couette Chicken Gute Soisy Ea'n Bacon Salad Planer Julienne Vectable Salad $102 $53 $90 $75 a 152 $50 $32 $31 2800 2992 2009 3906 1216 3791 $32 $55 $B3 $56 $48 $59 $59 $6B $32 $39 $39 $45 $29 $30 1181 3595 3092 2960 $45 Catering Job Costs Cient Pamia Technologies Job Number 4155 Condos Job Description Sales Conference 11/8/20 3 2 DAR Category Cost/Unit Total Cost 3 FOOD COSTS (MATERIALS) Menu Code Quantity 3341 1 2697 2 1873 1 2267 1 1780 2 1913 3 1660 1 3523 3 2965 3 1793 1 2557 3 3776 1 2841 1 2135 3 3693 3 3923 3 2518 1 2992 1 1181 1 3262 3 3436 3 3810 1 2874 1 1721 3 3361 3 2570 2 Size Large Large Large Large Large Large Medium Medium Large Medium Large Large Medium Medium Large Medium Large Large Large Medium Large Medium Medium Large Medium Medium TOTAL FOOD COSTS LABOUR COSTS De Staff Number Day Cos! Hours 5 8 8 17/8/20 18/8/20 19/8/20 19/8/20 20/8/20 20/8/20 21/8/20 Temp Start Temp Chef Temp Cher Temp Chel General Help Temp Chet Wait Statt General Help 2 2 2 2 3 2 Details Description Prepare Ingredients Preparation and Cooking Cooking Setup al venue Cooking and Plaring Table Service Pack up and clean up TOTAL LABOUR PRODUCTION OVERHEADS Days Cost TOTAL OVERHEADS SUMMARY TOTAL COST $ 12,400.00 Quoted Price before GST) Grom Profil Margin( B D Journal Entries for JOB_4155 5 Account Debit Credit $0.00 $0.00 Available Journal Accounts Accounts Payable Accounts Receivable Manufacturing Overhead Cost of Goods Sold Finished Goods Inventory Materials Inventory Sales Wages Payable Work in Process Inventory Purchase of Direct Materials (on Account) 3 Materials Inventory Accounts Payable 0 1 Issue of direct materials to production 2 Work in Process Inventory 3 Materials Inventory 4 5 Direct labour incurred 6 Work in Process Inventory 7 Wages Payable 8 9 Overhead applied to production o Work in Process Inventory 1 Manufacturing Overhead 2. 3 Cost of finished production transferred to Finished Goods store 4 Finished Goods Inventory 5 Work in Process Inventory 6 7 Cost of finished product sold 8 Cost of Goods Sold 9 Finished Goods Inventory 0 1 August Sales 2 Accounts Receivable 3 Sales 4 5 6 7 8 9 0 1 2 3 4 5 6 Section C The following are to be completed in the Client Database Worksheet This sheet contains a list of clients, when they joined and the number of catering jobs they have booked with us. ci Name the cells 16:154 Client_Status. C2 The formula in L15 is using the named range Jobs to add up the total number of catering jobs done for all clients. It is not returning the correct answer because the Named Range is not correct. Change the named range Jobs to include all cells from F6:F54. The value in L15 should now be correct. Convert the data in A5:154 to a table. Change the name of the table to ClientDB. C4 In column G we want to identify all clients who joined in the last 9 months. The date 9 months ago has been calculated in L11. In G6 create a calculation to put Yes if the client start date was on or after the date shown in L11. If they joined before then, leave the cell empty (do not put a space). The formula should copy down automatically CS We have decided to give a gift to thank customers for their loyalty. Create a calculation in H6 to return Gift if the client joined before the date shown in L12 or have purchased 15 or more jobs. If neither is true, leave the cell empty. The formula should copy down. C6 In L13 create a calculation to work out how many New clients we have. In L14 create a calculation to work out how many gifts we are giving away. C8 Customers are awarded a status based on how many jobs they have booked. The data in K6: L9 shows the minimum number of jobs required to achieve each status, so for example, a customer who has booked between 10 and 19 jobs gets Gold status. In the status column enter a calculation to calculate the status for each customer using the data provided in K6:L9 (these values may change). Question Instruction c9 In M6 create a calculation to work out how many Clients have Bronze status. Copy the formula down to M9. C10 In N6 create a calculation to work out how many jobs Clients with Bronze status have booked. Copy the formula down to N9. cu Create a Donut chart to show the percentage of clients of each status. Change the Chart Title to Client Status and add Data Labels to show percentages. Use Chart Element tools to position the legend at the bottom of the chart (do not drag). Section D The following are to be completed in the Menu Worksheet This sheet contains Menu options with associated costs D1 In cell 112 use a formula to calculate the highest price in D7:E113. DZ In cell 113 use a formula to calculate the lowest price in D7:E113. . D3 In 17 create a calculation to work out how many Mains dishes are available (Category is Mains and Off Menu is not set to Y). Copy down to 110. D4 In 17 create a calculation to work out the average cost of Medium size Mains dishes. Use appropriate referencing so that the formula can be dragged down and across. (1 mark for correct referencing.) Section E The following are to be completed in the JOB_4155 Worksheet In this sheet we need to be able to cost up different jobs. E1 D6 contains the name of the client we are doing the job for. In D7 enter a formula to look up the contact person for this client in the Client Database sheet. E2 In F14 enter a formula to look up the Dish Name for the Menu code in C14 from the Menu table. Formula should copy down. E3 In G14 enter a formula to look up the Category for the Menu code in C14 from the Menu table. Formula should copy down. E4 In H14 enter a formula to look up the cost for the Menu code in C14 and size in E14 from the Menu table. Formula should copy down. E5 In 114 enter a formula to calculate the Total Cost for that Menu item. Include a check in the calculation to see if the menu item is off Menu, if it is return a cost of O. (Items with a cost of O will automatically highlighted in orange to alert the user that they have selected an item that is not available.) E6 In the labour costs section in H49 enter a calculation to return the name of the day of the week for the date shown in C49. The formula should copy down. In 149 calculate the labour cost for that labour type and quantity using the values in the Cost Overview sheet. (Ensure you apply weekend rates for Saturdays and Sundays. Your calculations must take this into account even if your data does not contain weekend dates as the dates could change.) ES Overheads are calculated by taking the total overheads (see Cost Sheet) and apportioning them over the days worked in the year (see Cost Sheet). In H65 calculate the difference between the first and last date in C49:055 to work out how many days they worked on this job. E9 In 165 use the value in H65 and the overheads information in the Cost Overview sheet to calculate the production overheads. E10 In D69 calculate the total costs. E11 In 170 calculate the gross profit margin. Section F The following are to be completed in the Journals Worksheet Complete the journal entries for Job_4155. E7 F1
