# Download the data file “AMM 2510A Midterm Part II Data” from Canvas.

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

AMM 2510A Midterm Part B Due: 11:59 pm of October 21st, 2020 (Thursday)

Points: 40

Electronic submission only

Preparation

Download the data file “AMM 2510A Midterm Part II Data” from Canvas.

Case 1: Revenue Analysis

Case

Open the sheet “Revenue” for this case.

Figure 1 Case 1 IMU% Table Setup

Miss Jay Tee is a buyer for a specialty store in Chicago. She reviewed the current stock and determined

the following figures for the coming quarter:

• The merchandise on hand (Current) of 500 pairs of leather boots are selling well at \$200 retail with a 52% markup.

• She intended to buy additional 4,000 pairs of leather boots in this quarter. o The first 1,000 pairs (Purchase 1) will be sent by air with a delivered cost of \$55 per pair.

Miss Tee can combine the current stock and this new purchase to make a balanced size

and color. Thus, the price tag for this new purchase will match the current inventory.

o The second 1500 pairs (Purchase 2) have a landed cost of \$50 and will be sent by sea. Miss Tee intends to put a price tag of \$150 on these boots.

o The remaining 1500 boots (Purchase 3) are billed at \$60 per pair. Miss Tee planned an initial markup of 50% for this purchase.

• The overall quarterly maintained markup percentage (%) is planned at 56%.

Table Calculation (11 Points)

Table 1

Follow the steps to fill Table 1. The coloring coding of this table is also explained below.

1. Enter all known figures (12 yellow shaded cells). 2. Calculate the total cost of the current, new purchases, and the entire merchandise (5 grey cells). 3. Calculate the total retail of the current, new purchases, and the entire merchandise (5 grey cells). 4. Calculate the average markup percentage for the entire merchandise (2 blue cells). 5. Calculate the initial markup percentage for the new purchases (2 blue cells).

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Table 2

Complete Table 2 using SUMIF functions.

• Compute units, total cost, and total retail for the current stock and new purchases.

• The minimal requirement is to use SUMIF to calculate the new purchase row.

• Use absolute cell references to secure the range.

Table Format

Number Format

Format all numbers to have appropriate decimals and signs.

• Dollar numbers: show the “\$” sign and zero decimal places.

• Percent numbers: show the “%” sign and keep two decimal places.

• Quantity numbers: show zero decimal places Table Format

• Add borders to the table.

• Change the width of the table borders. The outside border needs to be thicker than the inside borders.

• The double border in the Example (Figure 1) is not required. Conditional Formatting

• Use conditional formatting to highlight the cell with the highest markup%.

• The highlighted cell will have a yellow background color.

Visualization

Insert a pie chart (see an example in Figure 2) to represent the total retail in dollars as a factor of stock.

Chart Data

• The y-axis data reflects the “total retail” column of the table.

• The total retail can be achieved by the entire merchandise is represented by areas of all pies added together.

• The a-axis data reflects the “stock” column of the table. Chart Format

Customize the pie chart:

• The pie slices will have a pattern fill instead of the default color fill. Pick your own color and pattern.

• The chart needs to have a proper title and a legend.

Figure 2 Sample Pie Chart

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Case II. Pivot Table/Chart

Open the sheet “Sales” for this case.

Pivot Table

Create two PivotTables based on a new datasheet, “Sales.”

• The first table (Table 1) shows the total revenue achieved by each salesperson (salesperson × revenue).

• The second table (Table 2) shows the average price per order accumulated by each company (customer name × unit price).

• Both tables should appear on the same datasheet.

Table Format

• Dollar numbers: show the “\$” sign and two decimal places.

• Sort Table 1 according to sales revenue in descending order (i.e., the salesperson with the most sales should appear at the top).

• Sort Table 2 according to the average price per order in ascending order.

Pivot Chart

Create two PivotCharts on the same datasheet (“Sales”).

• Choose “bar chart” for both PivotCharts.

• Create a slicer “region” to filter data in both PivotTables.

• Connect the slicer to both charts/tables.

• Show sales data for the west region.

Chart Formats

• Both charts need to have a proper title and a legend.

• Format the labels by the y axis: show zero decimal places for the dollar numbers.

Submission

it to Canvas.

Evaluation

Items Score

Case I. Markups

Table 1

• Table setup (4 points)

• Total cost and retail columns (5 points)

• IMU% new purchases (1 point)

• Average markup (1 point) Table 2

• Current and total row (2 points)

• New purchases row using SUMIF (2 points)

25

AMM 2510A Fashion Retail Buying I

Dr. Yishuang Li, 2021 Fall

Items Score

Table Format

• Signs and decimal places (2 points)

• Borders (1 point)

• Conditional formatting (2 points)

Visualization

• Pie chart linked to the total retail column. (2 points)

• Color pattern, title, and legend. (3 points)

Case II. Pivot Table/Chart

• Two PivotTables. (5 points)

• PivotTable format. (3 points)

• Two PivotCharts. (5 points)

• PivotChart format. (2 points)

15

Total 40

• Preparation
• Case 1: Revenue Analysis
• Case
• Table Calculation (11 Points)
• Table 1
• Table 2
• Table Format
• Visualization
• Case II. Pivot Table/Chart
• Pivot Table
• Table Format
• Pivot Chart
• Chart Formats
• Submission
• Evaluation