Fashion Retail Buying I

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.

Save your working file “AMM 2510A Midterm_YourLast Name, Your First Name.”

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

Save your working Excel file as “AMM 2510A Midterm_Your Last Name, Your First Name” and submit

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

Needs help with similar assignment?

We are available 24x7 to deliver the best services and assignment ready within 3-4 hours? Order a custom-written, plagiarism-free paper

Math homework help

Do not let math assignments discourage you from trying your best. One of our math experts can help you with math homework online. Our math tutors can help you with any level of algebra, calculus, or geometry. Ask your question to get the support you need 24/7.

Order Over WhatsApp Place an Order Online

Do you have an upcoming essay or assignment due?

All of our assignments are originally produced, unique, and free of plagiarism.

If yes Order Similar Paper