Section E 23 E1 2 E2 2 E3 2 E4 3 E5 3 The following are to be completed in the JOB_4155 Worksheet In this sheet we need to be able to cost up different jobs. 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. 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. In G14 enter a formula to look up the Category for the Menu code in C14 from the Menu table. Formula should copy down. 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. 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 0. (Items with a cost of 0 will automatically highlighted in orange to alert the user that they have selected an item that is not available.) 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.) 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:C55 to work out how many days they worked on this job. In 165 use the value in H65 and the overheads information in the Cost Overview sheet to calculate the production overheads. In D69 calculate the total costs. In 170 calculate the gross profit margin. E6 2 EZ 4 E8 2 E9 1 E10 1 E11 1 D Catering Job Costs Cient Pamia Technologies Job Number 4155 Contact 3 Job Description Sales Conference 11/8/20 0 1 2 3 Category Costume Total Cost 4 5 -6 7 Size Large Large Large Large Large 0 1 2 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 5 Medium Medium Large Medium Large Large Medium Medium Large Medium Large Large Large Medium Large Medium Medium Large Medium Medium 0 1 2 3 3262 3 3436 3810 3 1 2874 5 6 7 1721 1 3 3 2 3361 2570 0 1 2 3 Hous 5 Day Cost 5 TOTAL FOOD COSTS 6 LABOUR COSTS Do Temp Start 9 17/8/20 Temp Chat 0 18/8/20 Temp Chal 1 19/8/20 Temp Chat 19/8/20 General Help 3 20/8/20 Temp Chet 20/8/20 Wait Statt 521/8/20 General Help -6 Staff Number 1 2 2 8 8 Details Description Prepare Ingredients Preparation and Cooking Cooking Setupat venue Cooking and Plating Table Service Pack up and cleanup 2 3 2 0 TOTAL LABOUR 1 2 PRODUCTION OVERHEADS 3 Days Cost 5 TOTAL OVERHEADS 6 SUMMARY TOTAL COST 9 0 1 Quoted Price before GST) $ Grom Proti Margin (%) 12,200.00 3 Section E 23 E1 2 E2 2 E3 2 E4 3 E5 3 The following are to be completed in the JOB_4155 Worksheet In this sheet we need to be able to cost up different jobs. 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. 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. In G14 enter a formula to look up the Category for the Menu code in C14 from the Menu table. Formula should copy down. 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. 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 0. (Items with a cost of 0 will automatically highlighted in orange to alert the user that they have selected an item that is not available.) 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.) 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:C55 to work out how many days they worked on this job. In 165 use the value in H65 and the overheads information in the Cost Overview sheet to calculate the production overheads. In D69 calculate the total costs. In 170 calculate the gross profit margin. E6 2 EZ 4 E8 2 E9 1 E10 1 E11 1 D Catering Job Costs Cient Pamia Technologies Job Number 4155 Contact 3 Job Description Sales Conference 11/8/20 0 1 2 3 Category Costume Total Cost 4 5 -6 7 Size Large Large Large Large Large 0 1 2 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 5 Medium Medium Large Medium Large Large Medium Medium Large Medium Large Large Large Medium Large Medium Medium Large Medium Medium 0 1 2 3 3262 3 3436 3810 3 1 2874 5 6 7 1721 1 3 3 2 3361 2570 0 1 2 3 Hous 5 Day Cost 5 TOTAL FOOD COSTS 6 LABOUR COSTS Do Temp Start 9 17/8/20 Temp Chat 0 18/8/20 Temp Chal 1 19/8/20 Temp Chat 19/8/20 General Help 3 20/8/20 Temp Chet 20/8/20 Wait Statt 521/8/20 General Help -6 Staff Number 1 2 2 8 8 Details Description Prepare Ingredients Preparation and Cooking Cooking Setupat venue Cooking and Plating Table Service Pack up and cleanup 2 3 2 0 TOTAL LABOUR 1 2 PRODUCTION OVERHEADS 3 Days Cost 5 TOTAL OVERHEADS 6 SUMMARY TOTAL COST 9 0 1 Quoted Price before GST) $ Grom Proti Margin (%) 12,200.00 3
