BADM 2301 Lab 3 Assignment

1. Formatting (2pt). Open Grocery.xlsx (the file after finishing Lab 3 Tutorial), create a new sheet. Name the new sheet “Suppliers” and move it behind “Grocery” sheet. Open WeeklyQuotes.xlsx, copy the whole sheet and paste it onto the new “Suppliers” sheet you just created.
Format the worksheet using font Arial, size 10. With the exception of column A, make sure the numbers are in “Currency” format with 2 decimal places, aligned to the right.
Make the header row stand out using font style Bold and Center alignment.
2. Understanding Price Quotes (2pts). Britney would like to calculate the average and minimum of the price quotes for each item (product). Please do so using Excel functions and put the results in column P (for average) and Q (for minimum) of the “Suppliers” worksheet. Label the columns accordingly (“Average” for P and “Minimum” for Q).
 Pay attention to the empty cells. Would they affect the average and minimum?
3. Finding Lowest Quote for Restocking (3pts). Switch to “Grocery” worksheet, put “Average” in E1 and “Minimum” in F1. In column E and F, use VLOOKUP to find the average and minimum quotes for each item from the “Suppliers” worksheet.
Note: the list in the “Suppliers” is much longer so copy & paste won’t work.

You will need to REALLY understand how VLOOKUP function works and the meaning of each argument. Check Lab 3 Tutorial again if you have any issues.

4. Inventory Management Based on Previous Demand (4pts). Every week Britney needs to decide how many units she needs to replenish for each item. She would like to estimate the weekly replenishment order size using both the sales and quotes data from Week 52 of last year (more precisely, the number of units sold across all items during
that week).
Please calculate the estimate for her and put the results in column G. Label it “Units.” Since we do not have weekly sales data, we will assume the following for simplicity:
(1) There were exactly 52 weeks in the last year;
(2) Weekly demand for each item is constant all year long (i.e., demand in week 1 = demand in week 2 … = demand in week 52) (Think: given annual sales, how do you calculate weekly sales);
(3) Britney always orders from the supplier with the lowest quote; and
(4) Britney always sets a fixed 5% markup for each item.

DETAILED ASSIGNMENT

20201009163713lab_assignment3_