Excel Sales homework

profilePastel12
Assingment11instructions.pdf

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