Instructions: Complete all problems in the CRMG909 Assignment 2 Excel Worksheet. Rename your file to the following format: CRMG909_Last name, First Initial _ Asnt 2. Follow the instructions for each problem in this assignment sheet and as outlined in the Instructions tab in the CRMG909 Assignment 2 Excel Worksheet. You are expected to use proper excel formulas and cell formatting as outlined in the problem and/or in the Retail Math Formatting guidelines stipulated in the Course Outline.
Refer to the course module when completing this assignment for all concepts, formulas and calculation steps related to each question.
Please submit your excel spreadsheet via the correct D2L submission folder by the due date.
This is an INDIVIDUAL graded assignment. Therefore, students must not discuss or communicate regarding this assignment with anyone, under any circumstances. This includes no sharing of Excel files. Students suspected of academic misconduct will be reported to the Academic Integrity Office.
- You are a planner for a company called Simply Elegant, a store specialty chain that focuses on providing a premium selection of quality tabletop and giftware products. In working with your Merchandise Manager, you have been given following planning parameters for the Spring 2018 Six-Month Merchandise Plan:
- Total Season Sales dollar increase of 4.0%, with allof the sales increase falling in the month of June due to a new promotion being planned for this season.
- Total Season Reduction% has increased to 20% from 16% last year, with all of the dollar increase falling in the month of June due to the markdowns required to run the new promotion. The dollar markdowns for all other months remain the same as LY.
- IMU%is the same as LY for each month and for the total season.
- The stock planning method is the Stock-to-Sales The ratio for each month will be as follows:
Feb: 2.15 Mar: 2.10 Apr: 2.25 May: 2.30 June: 2.40 July: 2.25
- Ending Inventory (EOP$) planfor July is $1,695,000
Note: Last Year Actual results for all financial factors are provided in the Six-Month Plan in the assignment Excel file.
Create a Six-Month Merchandise Plan using Excel formulas taught in this course. Refer to Module 3 and the “Guide to Creating a Six-Month Plan in Excel” for all formulas and calculation steps to help you create proper Excel formulas. All variable cells must be formatted to course standards.
- Create 3 store cluster/ volume groupsgiven the information in the table below. Complete your work and create a final store volume group summary in the Excel worksheet. In the space below the tables (in Excel), explain your decision-making process.
| Category: | Rain Boots |
| Store # | Season to Date (STD) Sales |
| 101 | $82,344 |
| 105 | $80,911 |
| 110 | $76,342 |
| 105 | $74,293 |
| 102 | $72,142 |
| 108 | $70,113 |
| 107 | $68,354 |
| 100 | $65,243 |
| 103 | $63,234 |
| Total | $652,976 |
- An allocator reviews the merchandise financial plans for the upcoming season and needs to develop store plans for this classification. Based on the information given in the table, along with Total Season Sales $ Plan of $2,255,000and Average Unit Retail plan of $55.00, complete the Store Sales Plan in the Excel worksheet.
| Volume Group | # of Stores | % of Sales, each Store | $ Sales per Store | $ Sales for Volume Group | Unit Sales/ Store | Unit Sales/ Volume Group |
| A+ | 3 | 6.0% | ||||
| A | 10 | 4.0% | ||||
| B | 15 | 2.0% | ||||
| C | 12 | 1.0% | ||||
| Total | 40 | 100% |
- An order for 800 units of rain bootsis arriving into the distribution center this week and needs to be allocated to all stores in the chain. Using the percent contribution allocation method, determine how many units of the order to allocate to each store. Complete the allocation plan in the Excel worksheet.
| Classification: | Rain Boots | ||
| Total Order Quantity: |
800 |
||
| Store # | Season to Date (STD) Sales | % of Sales | Unit Allocation |
| 101 | $82,344 | ||
| 105 | $80,911 | ||
| 110 | $76,342 | ||
| 105 | $74,293 | ||
| 102 | $72,142 | ||
| 108 | $70,113 | ||
| 107 | $68,354 | ||
| 100 | $65,243 | ||
| 103 | $63,234 | ||
| Total | $652,976 |
- You have been asked to allocate a 1,200-unit order of coffee makersusing the store volume group allocation method. The order is shipped in pre-packs of 6 units per pack, or 200 total prepacks, and you cannot break the packs into smaller unit increments (you must allocate in multiple of 6). The buyer has also asked that you leave some prepacks in the distribution center to allocate as replenishment to the top performing stores.
Note: There is no specific “right” answer, but you must adhere to the requirement stated in the question as well as logical decision-making.
- Complete your allocation strategy in the Excel worksheet.
| Store Volume Group | # of Stores Per Volume Group | Total Prepacks Allocated per Store | Total Unit Quantity Allocated per Store | Total Unit Quantity Allocated-All Stores |
| A+ | 4 | |||
| A | 10 | |||
| B | 18 | |||
| C | 22 | |||
| D | 10 | |||
| Total | 64 |
- Describe your logic and decision-making for your allocation strategy in the space below your Excel table.
- You need to develop a replenishment plan for your basic stock department to determine the quantity of units of replenishment required for each store. You have been given information in the table below. Complete the replenishment plan for the missing elements in the Excel worksheet.
| Store # | Base Stock
Units
|
Safety Stock
Units |
Total Inventory Units Required | Current Unit Inventory
|
Unit Sales Forecast | Replenishment Units Required |
| 329 | 36 | 12 | 53 | 23 | ||
| 310 | 36 | 12 | 49 | 28 | ||
| 385 | 24 | 6 | 28 | 19 | ||
| 343 | 18 | 6 | 32 | 14 | ||
| 375 | 18 | 6 | 19 | 12 | ||
| 318 | 12 | 6 | 17 | 9 | ||
| Total | 144 | 48 | 198 | 105 |
TO GET THIS OR ANY OTHER ASSIGNMENT DONE FOR YOU FROM SCRATCH, PLACE A NEW ORDER HERE
