Excel Sales homework
Pastel12
AMM 2510A Fashion Retail Buying I
Dr. Yishuang Li, 2021 Fall
Page 1 of 2
Assignment 11: Dollar Plan (SSR) Due: 11:59 pm of November 14
th , 2021 (Sunday)
Points: 20
Electronic Submission only
Introduction
This assignment will help you practice the following skills that are introduced in this week's activity:
• (Core) identify the elements of a six-month merchandise plan.
• (Core) set up a six-month merchandise budget plan table in Excel using the SSR method.
• (Core) plan sales, reductions, inventory, purchases, and markups using the SSR method.
Preparation
Download and open the Assignment 11 Excel file on Canvas. Rename your working file as “AMM
2510A Assignment 11_Your Last Name, Your First Name”.
Dollar Plan Case
A sweater buyer is about to set up a 6-month plan for this fall season (Aug – Jan).
• The total fall sweater sales in LY were $135,000, and the monthly sales distribution was as in the following table. TY, the buyer predicts a 10% increase in total sweater sales, and that the
monthly sales % distribution would be exactly the same as LY. Initial markup goal for this fall
season is determined at 55%.
• Considering market environments, she projects that the total reductions of this fall (including all markdowns, employ discounts, and shortages) will be 8% of Sales. TY’s monthly reductions
distribution (% of seasonal total reductions) is planned to remain the same as LY.
• The sweater buyer is now planning inventory levels for each month using the SSR Method. The monthly SSR’s of TY are planned to be the same as those of LY, as presented in the table. For
January’s (the last month), the buyer plans to make NO PURCHASE during the last month.
Sales% (of Total Sales) Reduction% (of Total Reduction) SSR
Aug 5% 0.00% 3
Sep 15% 10.00% 2.5
Oct 30% 15.00% 1.8
Nov 30% 15.00% 1.6
Dec 15% 25.00% 2
Jan 5% 35.00% 2.7
TOTAL 100% 100%
Steps
Use the “Merchandise Dollar Plan” section of Week 13’s handout for formulas.
1. Using the information given in the above table, fill in cells with blue shades.
AMM 2510A Fashion Retail Buying I
Dr. Yishuang Li, 2021 Fall
Page 2 of 2
2. Plan Sales: complete the sales rows. 3. Plan Reductions: complete the “Reductions” rows 4. Plan BOM & EOM Stock Levels: complete BOM and EOM rows 5. Plan Purchases: complete the purchases rows 6. Plan Maintained Markup: complete the MMU rows 7. Format all $ and % figures. All figures need to have a proper sign and carry two decimal places.
Grading Chart
Items Score
Table Setup
• Three blue shaded rows using the given table. 3
Calculated Cells
• 12 calculated rows (excluding the total column). 12
• 6 total cells (sales, reduction, purchases*2, MMU*2) 3
Format
• $ and % format 1
• Spreadsheet assignment format (font, gridlines, header, landscape, one page, etc.)
1
Total 20
Sample Product
Please check your answer against the key (view or download the PDF document) posted on Canvas.
- Introduction
- Preparation
- Dollar Plan Case
- Steps
- Grading Chart
- Sample Product