ASSIGNMENT 2: Balancing of Markup

(Maximum: 30 points)

Due: September 26, 2020

In preparation for a new season, a buyer determined a 58% initial markup was required for total purchases for the season in order to meet her gross margin goal of 50%. Her total budget for purchases was $1,923,000 at retail.

Upon completion of purchase orders with all vendors, the buyer reviewed all purchases, which amounted to $807,660 at billed cost.

At the end of the season, a vendor analysis was conducted to evaluate the purchase versus sales performance of each vendors merchandise. The analysis revealed the purchase and sales results shown below:

Vendor	Billed Cost	Original Retail	Net Sales
Vendor A	$38,520	$87,000	$86,270
Vendor B	43,270	102,000	98,420
Vendor C	41,270	94,000	77,720
Vendor D	42,230	98,000	81,660
Vendor E	74,200	179,000	155,750
Vendor F	68,030	162,000	149,940
Vendor G	96,990	235,000	199,870
Vendor H	101,280	250,000	203,800
Vendor I	52,270	130,000	108,990
Vendor J	75,320	187,000	137,780
Vendor K	83,260	201,000	170,980
Vendor L	91,020	198,000	181,850
Total	807,660	1,923,000	1,653,030

Assignment

1. On a spreadsheet, type in the information given in the above table (Row and column headings look exactly like the above).

2. Add 3 columns to the right side of the above spreadsheet to show the following numbers (a) through (c).

1. Initial markup% planned for each vendor and total.
2. Maintained markup% achieved for each vendor and total.
3. Reductions% applied to each vendors merchandise as well as total merchandise.

3. Type in appropriate headings, and compute figures in the added columns using formulas.

4. Format spreadsheet numbers as follows.
   • Format dollar figures to Currency $, 0 decimal places
   • Format percentages to %, 2 decimal places

5. On a Word document, based on the the numbers calculated on the spreadsheet, please answer questions (i), (ii), (iii), and (iv) below.

1. Which vendors maintained markup percentage performances were better than the overall maintained markup percentage achieved for the total merchandise?

2. Compare the total initial markup percentage (total IMU%) that was originally planned and the total maintained markup percentage (total MMU%) achieved.

3. What is the total reductions% that caused the difference between the total IMU% and total MMU%?

4. Given that there were no cash discounts or alteration costs, did the buyer meet her gross margin goal of 50%? If yes, which vendor(s) positively contributed to the buyers meeting her gross margin goal? (i.e., Which vendors merchandise met or exceeded a gross margin of 50%?)

6. Check out the grade criteria below and make sure you did everything graded.

Spreadsheet:

36 Formulas on spreadsheet (.4 pts each, 0 pts without formula sheet: Total 14.4 pts)

36 Fixed data on spreadsheet (.2 pts each: 7.2 pts maximum)

Format headings (column headings - centered & bold; row headings bold & left justified) (0.4 pts)

Format $ figures ($ sign, 0 decimal places) and % figures (% sign, 2 decimal places), and right justify $ and % figures (1 pt)

Format worksheet (font, gridlines, header, landscape, one page, etc.) (1 pt)

Written Assignment:

Question (i) answer (1 pt)

Question (ii) answer (1 pt)

Question (iii) answer (1 pt)

Question (iv) answer (2 pts)

Format written assignment (font, spacing, title, header, etc) (1 pt)

Total Points (30 points maximum)

7. Turn in:
   • Spreadsheet (following the spreadsheet assignment requirements on the syllabus page 3)
   • Responses to questions (i), (ii), (iii), and (iv) on a Word document (formatted following the written assignment requirements on the syllabus page 3)