Lesson Plan Example Week 2 of Excel Latko
Instructions Type your full name into cell A1. Type your course and section number into cell A2. Type the current date into cell A3. In cell A11 type in the words "Johnny Jim's Cashier Results" - do not use quotes, but do use font size 20, and make the text bold. Merge and center these words from A11 to I11.
- Save the file to your flash drive, naming the file jj_cashiers.xlsx
- Starting in row 14, type in the following information to set up your spreadsheet.
- Q2 - 2009: This column shows how many customers each cashier serviced in Q2 (quarter 2) of 2009. There is nothing to figure out for this column.
- Q3 - 2009: This column shows how many customers each cashier serviced in Q3 of 2009. There is nothing to figure out for this column.
- Q4 - 2009: Intense training sessions took place during the 3rd quarter of 2009. You will create a formula in the Q4 - 2009 column to show how many customers each cashier is expected to service in the quarter. Due to the training sessions, it is expected each cashier will service 20% more customers in Q4 of 2009 than the number of customers serviced in Q3 of 2009. In other words, take the number of customers serviced in the Q3 column and increase them by 20% using a formula.
- Gross Sales: In this column you will create a formula to show how much money each cashier collected from customers in Q2, Q3, and Q4. To find this answer you will need the following information: in Q2 of 2009 the average bill for each customer serviced was $7.00, in Q3 of 2009 the average bill for each customer serviced was $7.50, and in Q4 of 2009 the average bill for each customer serviced is expected to be $10.00.
- Coupons: In this column you will create a formula to show how much money is expected to be lost from customer coupons. While not every customer uses coupons, you have found the average amount per customer serviced is 1.25% of the total bill. Create a formula that will show the estimate of how much money will be lost from the gross sales of each cashier due to coupon usage for all the customers serviced in Q2, Q3, and Q4. Hint: You will not use the average function in this formula - you are just being told the average amount saved per bill.
- Complaints: In this column you will use a combination of functions to show how much money is expected to be lost due to customer complaints. Management is pretty clear - when a customer complains, that customer gets their meal for free. From past experience, you know that as your employees service more customers, they tend to make mistakes and cause customers to complain. Use the following information to find the amount of money each cashier will lose due to all customer complaints in Q2, Q3, and Q4: When the median number of customers serviced for Q2, Q3, and Q4 is 1,150 customers or more, the cashier will lose 5% from his/her gross sales column. If the cashier does not have a median number of complaints of 1,150 or more customers for the three quarters, the cashier will only lose 2.50% from his/her gross sales column.
- Net Sales: In this column you will create a formula to find the Net Sales figure for each cashier. In general, net sales are equal to the gross sales minus any deductions/expenses. For this problem, you will take the gross sales of each cashier and deduct the coupon loss and complaint loss for that cashier to arrive at your answer.
- Rating: In this column you will use a function to give a rating for each cashier based on his/her performance over the three quarters of Q2, Q3, and Q4. The Johnny Jim's Cashiers will be rated as The Works, Average, or Stale Bread. Any cashier who has net sales greater than $35,000 is to be rated as The Works. Any cashier with sales greater than $27,000 (but not greater than $35,000) is to be rated as Average. All other cashiers are to be rated as the dreaded Stale Bread.
- Sort: **Save your work before doing this step** Sort out the employees so the cashier with the highest gross sales (from the gross sales column) is listed above the cashiers with the lowest gross sales.
- Total: Use a function to get the total of the Q2 - 2009, Q3 - 2009, Q4 - 2009, Gross Sales, Coupons, Complaints, and Net Sales columns.
- Min: Use a function to get the Min of the Q2 - 2009, Q3 - 2009, Q4 - 2009, Gross Sales, Coupons, Complaints, and Net Sales columns.
- Max: Use a function to get the Max of the Q2 - 2009, Q3 - 2009, Q4 - 2009, Gross Sales, Coupons, Complaints, and Net Sales columns.
- Median: Use a function to get the Median of the Q2 - 2009, Q3 - 2009, Q4 - 2009, Gross Sales, Coupons, Complaints, and Net Sales columns.
- Chart: Finally, you will need to create a chart. You must use a bar chart (clustered bar in 3-d) showing the top 6 cashiers and the number of customers each served in Q2-2009, Q3-2009, and Q4-2009. Do not include unnecessary information. Give the Chart a title – “Johnny Jims Sales”
Create a text box saying which quarter was the highest cashier. Insert an arrow pointing to the highest cashier. Make sure the legend is at the bottom. Label the horizontal and vertical axis.