Exploring Excel 2016 Project 80 Marks = __/15% RESTAURANT ANALYSIS: Assume you are a restaurant manager. You are considering entering into a long-term agreement with a poultry company and planning to

Exploring Excel 2016 Project

80 Marks = __/15%

RESTAURANT ANALYSIS:

Assume you are a restaurant manager. You are considering entering into a long-term agreement with a poultry company and planning to purchase a new refrigeration unit to store the chicken. If you enter into this agreement, the poultry company agrees to provide a substantial discount for a specified period of time. You would like to stimulate sales for the lowest revenue producing chicken item on the menu as well as any chicken items not meeting a sales performance threshold. Thus, some of this savings will be passed on to the guests by placing these chicken items on sale.

TASKS:

Name the Lookup Table – 4 marks

a) Start Excel and Open the Sales V?.xlsx file provided by your professor. Ensure you open and complete the version assigned to you, otherwise you may not receive a grade for this project. Save As Sales_YourName (where YourName is replaced by your first and last name).

b) Make the Lookup Table worksheet the active sheet. Enter reasonable Prices in the B column for each of the Menu Items. Assign the name Price to the range containing menu items and price.

Insert Functions and Formulas – 22 marks

You need to compare the projected sales quantities to the actual sales quantities over a four month period. Notice that data on the ChickenSales worksheet in range A15:E20 contains four months of actual sales quantities for each menu item. Whereas, the range A24:E29 contains the sales projections made for the same four months prior to the start of each month.

a) Make the ChickenSales worksheet the active sheet. Wrap text in the range A5:I5 to make the column headings understandable. Ensure row heights and column widths are set so as to display cell contents.

b) In Cell B6 enter a formula to calculate the total Projected Sales Quantity for this menu item over the four months.

c) In Cell C6 enter a formula to calculate the total Actual Sales Quantity for this menu item over the four months.

d) In Cell D6 enter a formula to calculate the average Actual Sales Quantity over the four months.

e) In Cell E6 enter a formula to calculate the price of the menu item by looking up the price of the menu item from within the Price range.

f) In Cell F6 enter a formula to calculate the Total Revenue on each menu item based upon the Total Qty Sold and the Price of the menu item.

g) In Cell G6 enter a formula to calculate the percentage of Total Qty Projected sales that were actually sold (Total Qty Sold). For example, maybe 92% of the Total Qty Projected was actually sold.

h) In Cell H6 enter use a logical function in a formula to determine which menu item has the highest Total Revenue. For the menu item that has the highest revenue enter the words Best Item and for all other menu show a blank cell.

i) In Cell I6 enter a logical function in a formula to determine which items will be put on sale next in order to stimulate sales. An item should be put On Sale if its % of Projection is less than the Sale Threshold in Cell L6. An item should also be put On Sale if its Sales Revenue is the lowest. Leave the cell blank for items that are not put on sale.

j) Copy the formulas and functions down their respective columns.

k) In Cell L9 enter a formula that displays the number of days between the Start of the sale and the End of the sale. The Start and End dates have been provided by the poultry company and are entered in Cells L7 and L8.

Determine Loan Payments – 8 marks

You will need a loan in order to purchase a new refrigeration unit to store the poultry. You wish to make quarterly payments (4 payments per year) toward your loan repayment. You will then want to see how these payments will vary based on the number of years to repay and varying interest rates. You should generate one formula using Relative and Absolute Cell References and then copy the contents of the one cell into all the other cells. Manually entering different formulas into each cell will result in half marks.

a) Click on the Poultry Loan worksheet tab. Use a financial function in a formula in Cell C12 to calculate the loan payment amount given the loan amount in Cell C6 and the annual interest rate in Cell B12. Use the appropriate relative, mixed, and/or absolute cell references in the formula.

b) Copy the formula to Cells C13 and C14. The formula in C12 will reference the B12 interest rate, but when you copy the formula to C13 the formula should change to reference the B13 interest rate. Similarly, for C14 referencing the B14 interest rate.

c) Copy the formula to Cells D12 and E12. The formula in C12 will reference the C11 year, but when you copy the formula to D12 the formula should change to reference the D11 year. Similarly, for E12 referencing the E11 year.

d) Complete copying the formula to Cells D13:E14 and ensure the interest rate cell references and the year cell references change accurately.

Set File Properties – 6 marks

You need to set some details about the workbook.

a) In the File, Properties, enter a Title for this Workbook RestaurantName Sales (where RestaurantName is the name you have chosen for the restaurant).

b) In the File Properties, enter a Tag for this Workbook YourFirstandLastNames, Manager (where YourFirstandLastNames is your first and last names and Manager is your job title).

c) Under File, Properties enter your first and last name as Author. You may need to adjust the File, Options for your name to appear as Author.

Format Data – 18 marks

You need to format the titles and numeric data in the ChickenSales sheet. In addition, you want to freeze the column labels so that they do not scroll offscreen. You also want to apply conditional formatting to emphasize values above the average value.

a) Create a name for this restaurant and a company logo (image file). Insert your company logo (image file) into the ChickenSales and PoultryLoan worksheets and place the top-right corner of the logo in cell A1. Ensure the logo does not overlap other content. Ensure the logo is the same size on both worksheets.

b) On the ChickenSales worksheet, merge and center the main title Chicken Sales Analysis, across the Menu Item table. Format the title in a larger font and fill the cell with colour.

c) Move the date cells (C4:D4) to H1:I1.

d) Apply the “Currency” number format to the monetary values in columns E and F.

e) Format the Avg Monthly Qty Sold values with one decimal place.

f) Freeze column A so it does on scroll offscreen.

g) Apply conditional formatting to values in the Total Revenue column. When a cell’s value is greater than the average of the values in the column the cell fills with colour.

h) Change the dark purple formatting on the column headings on the ChickenSales worksheet to some other colour(s) that better suit your company logo and company colours. Ensure the cell contents fit the cell and are clearly visible.

i) Click on the PoultryLoan worksheet. You would like to use the repayment option that keeps the quarterly payment amount under $2000, has the shortest term, and the lowest interest rate. Locate the ONE cell that meets these conditions and add a Comment saying Winner! (or something of that sort).

Create Sparklines and Insert a Chart – 6 marks

You will create a chart comparing the total revenue for each menu item, and you will insert sparklines to display trends over a four-month period of time.

a) On the ChickenSales worksheet create Sparklines (lines or columns) in Cells F15:F20 to display four-month trends for actual sales for each menu item. Show the high point in each sparkline. Apply a Sparkline Style of your choice.

b) Create a Chart comparing the Total Revenue for each of the Menu Items. Select any one of Pie, Line, or Bar chart type.

c) Position the Chart beside the Actual Sales Quantities area and ensure it does not extend past column P. Apply any Chart Style to this Chart. Change the Chart Title to Chicken Sales Revenue by Menu Item.

Sort and Filter the Data – 16 marks

To preserve the integrity of the original data, you copy the worksheet. You will then convert the data in the copies worksheet to a table, and apply a table style, sort and filter the data, and then display totals.

a) Copy the ChickenSales sheet and place the copied sheet at the end of the workbook.

b) Remove the conditional formatting rule on the ChickenSales (2) sheet.

c) Convert the data in the Menu Item area in the ChickenSales (2) sheet to a table.

d) Sort the table by % of Projection in ascending order (if the sorting does not work, you will still get the marks for this question, as there is a glitch commonly associated with this question)

e) Apply a filter to display the top 5 performers only.

f) Display a total row. Add totals for columns B, C, and F.

g) On the ChickenSales worksheet, create a footer with the file name code on the left side, your name in the center, and the sheet tab code on the right side.

h) On the ChickenSales (2) worksheet, apply 0.5” (1.25 cm) left and right margins. Scale to fit on one page. Select Landscape orientation. Preview to ensure all is set correctly.

Save the workbook. Close the workbook and exit Excel. Submit the workbook as directed by your instructor.