Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Lab 2A Instructions – Microsoft Excel

Learning Objective:

Applying advanced Excel functions in a business context

Performing sales analyses, inventory replenishment and demand forecast using Excel Understanding and solving problems with the VLOOKUP function and pivot table

Background:

This assignment is based on what you learned in your class assignment 3 about VLOOKUP and Pivot Tables.

Business Scenario:

DangerWay has an extensive supplier network consisting of both local and regional farmers, from which the store places regular replenishment orders. In addition to the itemized annual report, Britney also receives a WEEKLY update with quotes from these suppliers for all her store items. WeeklyQuotes.xlsx provides a copy of the report for the 52nd week of the last year. Note not all items are available from all suppliers – a blank cell indicates unavailability. In this assignment, you are asked to perform the following analyses using Excel procedures and functions.

Requirements:

More specifically, you are asked to perform the following tasks and analyses:

1. Formatting Open the Grocery_lastname.xlsx file (which you worked on in Class Assignment 3), rename it Lab 2A_Grocery_lastname and create a new sheet called “Suppliers” within the file and move it to the right of “Grocery” sheet. Then, open the file attached with this lab called WeeklyQuotes.xlsx, and copy the whole sheet and paste it onto the “Suppliers” sheet you just created in the Lab 2A_Grocery_lastname file.

Format the Supplier worksheet using font Arial, size 10. Except for column A, all numbers should be in “Currency” format with 2 decimal places, aligned to the right. Make sure the header row stands out using font style Bold and Center alignment.

2. Understanding Price Quotes 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 (Column P should be “Average” and Column Q should be “Minimum”).

Ø Pay attention to the empty cells. Would they affect the average and minimum?

3. Finding Lowest Quote for Restocking Switch to “Grocery” worksheet, label the column E “Average” and column F “Minimum”. In columns 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. 

4. Go back to Grocery worksheet and type “Category” in cell D1, In cell D2, type “=VLOOKUP (B2,Categories!A:B,2,FALSE)”. Press Enter. You should see “Fruits” as the category if the first product in your worksheet is “Apples”. Then, use auto-fill or drag-fill to populate the rest of the list for column D.

5. Inventory Management Based on Previous Demand. 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.

Let’s quickly organize what information we have on hand. For each item, we know:

(1) the annual sales from the last year,

(2) the unit cost (lowest quote), and

(3) the 5% markup for the selling price.

Now, it is your job to estimate the weekly replenishment order size for each item. For your solutions, allow decimal numbers for “Units” by setting the format to have 2 decimal places.

Hint: Annual Sales = Unit Cost * 1.05 * Annual Quantity Sold

6. Advanced Revenue Analysis. To run a marketing campaign for DangerWay, Britney wants to know which product category is her cash-cow in generating revenue. Hired as an expert in data analytics, you are asked to answer the following questions based on the product, sales and category information you have on hand (column A, B and D of worksheet “Grocery”.)

(1) What are the average sales for each category?

(2) Which product category generates the most total sales? Which generates the least? Create 2 pivot tables in a new worksheet to answer the above 2 questions. Rename the worksheet as “Pivot Table” and write the answers in the top of the worksheet.

Submission/Due:

Save your Lab 2A_Grocery_lastname Excel file and submit it on Blackboard under “LA2A - Lab Assignment 2A”.