In preparation for a new season, a buyer determined a 5 8% initial markup was required for
Question:
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 vendor’s 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
- On a spreadsheet, type in the information given in the above table (Row and column headings look exactly like the above).
- Add 3 columns to the right side of the above spreadsheet to show the following numbers (a) through (c).
- Initial markup% planned for each vendor and total.
- Maintained markup% achieved for each vendor and total.
- Reductions% applied to each vendor’s merchandise as well as total merchandise.
- Type in appropriate headings, and compute figures in the added columns using formulas.
- Format spreadsheet numbers as follows.
- Format dollar figures to Currency $, 0 decimal places
- Format percentages to %, 2 decimal places
- On a Word document, based on the the numbers calculated on the spreadsheet, please answer questions (i), (ii), (iii), and (iv) below.
- Which vendors’ maintained markup percentage performances were better than the overall maintained markup percentage achieved for the total merchandise?
- Compare the total initial markup percentage (total IMU%) that was originally planned and the total maintained markup percentage (total MMU%) achieved.
- What is the total reductions% that caused the difference between the total IMU% and total MMU%?
- 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 buyer’s meeting her gross margin goal? (i.e., Which vendors’ merchandise met or exceeded a gross margin of 50%?)
- 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) |
- 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)
Accounting for Governmental and Nonprofit Entities
ISBN: ?978-0073379609
15th Edition
Authors: Earl R. Wilson, Jacqueline L Reck, Susan C Kattelus