Scenario

Seasonal Delights Catering offer personalised catering to corporate clients. To help cost their jobs they have recently implemented a Job Costing System. The head chef has allocated a costing for ingredients to each menu item to help quickly calculate how much they will spend on materials based on the menu selected by the client. For each job casual staff are employed by the hour as required. You need to help finish off the spreadsheet template that they will use to cost Job 4155 and all future jobs.

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 two worksheets are locked and you will only be able to change the cells specified.

Question Instruction

Section A The following are to be completed in the Labour July Job List sheet: 

A1-A8

This sheet contains a list of jobs done in July, 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 G 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 Cost Overview sheet: 

B1

This sheet contains cost information. B6 contains the cost for the monthly rent. In C6 enter a calculation that will use the value in B6 to calculate the annual rent. Copy the formula down to C10.

B2

In B11 calculate the total monthly Indirect Costs. Copy the formula across to C11.

B3

In C14 calculate the annual cost of Electricity and Gas. Copy the formula down to C16.

B4

In B17 calculate the total cost for Services and Utilities. Copy the formula across to C17.

B5

Supplied costs have been estimated for the year. In B20 calculate the monthly costs for cleaning equipment. Copy the formula down to B22.

B6

In B23 calculate the total monthly cost for Supplies. Copy the formula across to C23.

B7

In B25 calculate the total month costs. Copy the formula across to C25.

B8

Widen column G so it is a similar width to the other columns.

B9

Apply the Percentage number format to the value in G5. If necessary adjust to show no decimal places.

B10

Staff earn an extra percentage for working on weekends. Use the rate in G5 to calculate the weekend rate for a Temp Chef. Your calculation must use appropriate referencing so that the formula can be copied down. If the rate in G5 changes, the weekend salaries must update accordingly.

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.

C1

Name the cells I6:I54 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.

C3

Convert the data in A5:I54 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.

C5

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.

C7

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).

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.

C11

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 I12 use a formula to calculate the highest price in D7:E113.

D2

In cell I13 use a formula to calculate the lowest price in D7:E113.

D3

In I7 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 I10.

D4

In J7 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.

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 I14 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.)

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.

E7

In I49 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.)

E8

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. (Do not use NETWORKDAYS as it excludes weekends and the company does work weekends. Do a simple subtraction and then add 1 as Excel assumes midnight to midnight which is not the case.)

E9

In I65 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 I70 calculate the gross profit margin.

Section F The following are to be completed in the Journals Worksheet

F1

Complete the journal entries for Job_4155.

Worksheet