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

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 oftime. 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 ofthis 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 Sa1L’.cYour4Vanw (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 Al5: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:15 to make the column headings understandable. Ensure row height 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 116 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 Iteni and for all other menu show a blank cell. 

1) In Cell 16 enter a logical function in a formula to determine which items vill 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 L4. An item should also be put on sale if its Sales Revenue is the lowest. Enter the word “eI On Sale” if the item will be put on sales and leave the cell bank 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 display 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.

Need a Professional Writer to Work on this Paper? Click Here and Get this Essay Done ………