Northwind Products and Suppliers Case Study Project

 

Do you want Northwind Products and Suppliers Case Study Solutions? If yes, then Casestudyhelp.com is available 24/7 for answering your complex questions. Get Assignment Answers online at a reasonable price. Our Case Study Experts delivers 100% original content writing with deep research. We never let you down, with our authentic assignment services.

 

 

Pricing Analysis and Purchase Plan Project

Scope: You will use Excel and Pivot Tables to summarize Northwind product and sales data. The idea is to analyze how Northwind’s products are priced, how they have sold, and ultimately how many to purchase. Each group will act as a category manager of a specific category and will create a PowerPoint presentation for Northwind management.

STEP 1: READ THE CASE.

Every product in Northwind’s database belongs to one category, such as Beverages, Condiments, etc., and each team of students is assigned to manage/purchase one category of products. In the past, and due to perishability of food items, Northwind’s management allocated only enough funds to purchase a one-month supply of inventory for each product. However, with new refrigeration and climate-controlled storage facilities, they have allotted a budget to buy a minimum of three months (i.e., one quarter) of each product. It is unclear which products might have greater demand and potentially greater sales because, in the past, Northwind salespeople have pushed and promoted the products that were left in stock in order to deplete inventory. So, for this first round of the new purchase process, and until demand trends can be better ascertained, the category management teams will focus on maximizing total potential profit by assuming that everything purchased for inventory can be sold before it expires. As such, product cost and profit margin will be driving factors.

STEP 2: RETRIEVE THE DATA.

Query 1 (Product list). Join the following 2 tables and query the following fields, and export it into an Excel spreadsheet:

• Product table (ProductName, ListPrice, UnitCost, Discontinued, Supplier)
• Category table (CategoryName)

Query 2 (Line-item data). Join the following 4 tables and query the following fields, and export it into a spreadsheet:

• Details (Price, Quantity, Discount)
• Order (OrderDate)
• Product (ProductName, Discontinued)
• Category (CategoryName)

STEP 2A: CALCULATE MARGIN.

Read about how price “markup” compares to profit “margin”: https://fitsmallbusiness.com/product-pricing/.

• Selling a product for $2 that cost you $1 = $1 “mark up” (compared to original cost it’s marked up 100%)
• But the profit margin on that $2 sale = $1 “margin”, which compared to the $2 sale = 50% “margin”

In the Product sheet, calculate a simple Unit Margin as a product’s List Price minus Unit Cost, and %Margin as Unit Margin divided by List Price. NOTE! Margin often is correlated to price, but %Margin may or may not be.

STEPS 3-5. Create PowerPoint slides as described. See Step 5C. Use Section Slides (Product Summary, Sales Summary, etc) to denote steps/focus. Delete graph titles, but add axis titles as needed. Only keep legend if relevant. Adding graphs to slides using Paste Picture or Jpeg to disconnect from Excel. Add a short, self-explanatory title at top of each slide.

STEP 3A: PRODUCT SUMMARIES.

Run Pivot analyses on the Products sheet to summarize products carried.

1. Describe the non-discontinued products in your category by creating a table of each product and its price, cost, and margin. You can use a Pivot table to do this, but there’s no grouping – there’s only 1 record per product, so do NOT leave a label like Sum or Average on the table. Then use a Pivot graph to group records by $10 pricing levels to show product counts (and possibly another graph showing average % margin).
2. Describe all categories of products by creating a couple of graphs that show a count of products and average price per category, as well as a count of products per price level (and again, possibly another graph showing % margin).

STEP 3B: SALES SUMMARIES.

Run Pivot analyses on the Line Item (Details) sheet to summarize past sales.

1. Describe the sales of the non-discontinued products in your category by creating Pivot graphs to show total sales per product and per $10 price level. Sales figures could refer both to quantities sold and revenues, but be careful to combine multiple measures (like quantity and revenue) into a single graph with a single axis ONLY if the magnitude of one does not drown out the other.
2. Describe the sales of all categories of products by creating graphs that show sales (quantity and revenue) per category and per price level (and again, possibly another graph showing % margin).

STEP 3C: ANALYZE SUMMARIES.

Create additional slides to describe patterns or observations pertaining to the product and sales summaries. Create additional graphs as needed to support and emphasize any patterns or trends. Note! Graphing revenue and units together may be difficult due to differences in the magnitude of the data. Many options exist, but one is to look at the % revenue of total and % units sold of total, this expressing both in terms of percentages. In addition, comparisons could be made between product prices and revenue. For example, do products in certain price ranges have higher sales? Such questions can be examined at the individual product as well as category level. Looking for and explaining these interrelationships helps to frame price as a potential driver of sales, which in turn might help to inform inventory decisions.

STEP 4: DATA PREPARATION.

Step 4a. Create a Pivot table (using the Line-Item sheet) that sums the quantity sold in 2020 Q3 for each non-discontinued product in the category and present relevant information in a slide as a basis for identifying minimum units for 2021 Q3.

Step 4b. Create a Pivot table of the quarterly sums of the quantity (units) sold in the category. Filter out partial quarters at the beginning and end of the range, and then add a trend line (showing the equation and R squared). Present relevant information in a slide as a basis for identifying the maximum total units to be purchased in the category.

Step 4c. Prepare a table to be used in Step 5 with the following columns.

• Cols A, B, C: Product Name, Unit Cost, and Unit Margin … from Step 3a
• Cols D, E: Basis (2020 Q3 sales) and “Min” Purchase (1.75 * Basis) … from Step 4a
• Col F: Units to Buy (currently unknown and left blank)
• Col G: Cost to Buy (Units to Buy times Unit Cost)
• Col H: Potential Profit (Units to Buy times Unit Margin)
• Cell for Sum of Col F: Total Units (Sum of the Units to Buy column)
• Cell for Sum of Col G: Total Cost (Sum of the Cost to Buy column)
• Cell for Sum of Col H: Total Profit (Sum of the Potential Profit column)
• Cell for Limit of Units: Total Units Limit in the Category … from Step 4b (use t=8 for equation)
• Cell for Limit of Budget: $25,000 as specified by Northwind management

STEP 5A: PURCHASE PLAN.

Run Solver to determine Units to Buy while maximizing the category’s potential profit using these “constraints”:

1. Total Cost cannot exceed the Budget.
2. Units to Buy must be Units to Buy must be greater than or equal to the Min Purchase for each product.
3. Total Units to buy cannot exceed the Total Units limit in the category.

After running Solver, present relevant information (table of data) in a slide.

STEP 5B: ANALYZE PURCHASE PLAN.

Create additional slides to describe patterns or observations pertaining to the purchase plan. In particular, describe any trends/patterns in the purchase plan quantities with respect to product price, cost, margins, and sales. Create additional graphs as needed to support and emphasize any patterns or trends.

STEP 5C: ORGANIZE THE PRESENTATION.

Remember that you are one of multiple category managers, so make sure your slides and descriptions are completely self- explanatory but efficient. Review your presentation and take note of the following:

• Begin each section of slides with a Section Slide (Product Summary, Sales Summary, Analysis of Summaries, etc).
• Format Excel tables (decimal places, column headings, ). Copy onto slide as HTML; additional formatting as needed.
• Delete graph titles on Excel chart (put those on the slides allows them to be changed as needed).
• Add axis titles to the Excel chart as needed.
• Format (clean up) axes as needed (e.g., decimals rarely needed, commas as needed, counts in whole numbers, etc.).
• Only keep the legend on the Excel chart if relevant (if there are multiple measures in the graph).
• Combine multiple measures in 1 chart only if magnitude of data is similar (i.e., if one doesn’t drown out the other).
• Copy graphs to PowerPoint using Paste Picture or Paste Special / Jpeg so images are disconnected from the Excel sheet.
• Add a short, self-explanatory title at top of each slide.
• Stretch the graph/image in the slide so it fills up the space below the title.
• Put 2 graphs/images on same slide only if related or being compared.

 

For REF… Use: #getanswers2002450