Creating Lookup Table and calculating Sales in Excel

Name the Lookup Table

TASKS:

Name the Lookup Table –

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

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.

Insert Functions and Formulas

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

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

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.