RMG909 Lab 3- Worksheet

xlsx

School

Toronto Metropolitan University *

*We aren’t endorsed by this school

Course

909

Subject

Industrial Engineering

Date

Feb 20, 2024

Type

xlsx

Pages

Uploaded by AmbassadorElephantPerson1068

RMG909- Lab #3-Store Planning, Allocation, and Replenishment Before you begin, carefully read through Assignment Sheet, located in the Assignments section o Complete all questions in the "Questions" sheet in this excel file. You do not need to create any a Complete each question as required, using proper excel formula. Where indicated, you may be re You are responsible for formatting the cells to the proper numerical format (this has not been do

of D2L. additional sheets. equired to show the steps taken to arrive at your final answer, or show the formula one for you)- i.e $ sign, proper rounding and number of decimal points etc.

RMG909- Assignment #3-Store Planning, Allocation, and Replenishment Name: Q.1 Volume Group # of Stores $ Sales per Store A+ 3 6.0% A 10 4.0% B 15 2.0% C 12 1.0% Total 40 100.0% Planned Sales $2,255,000 Average Unit Retail $55 Q.2 Classification: Rain Boots Total Order Quantity: 800 Store # % 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 % of Sales, each Store Season to Date (STD) Sales

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Total $652,976 Q.3 Store Volume Group A+ 4 A 10 B 18 C 22 D 10 Total 64 B) In the green text box below, describe your logic and decision-making of your allocation strategy.. Q. 4 Store # 329 36 12 310 36 12 385 24 6 343 18 6 375 18 6 318 12 6 Total 144 48 Q. 5 Your Answers- type the formula in words, show the steps and use Excel formulas to calculate e Number of Stores Per Volume Group Unit Quantity to Allocate to each Store in Volume Group Total Unit Quantity to allocate, all Stores in Volume Group Base Stock Units Safety Stock Units Total Unit Inventory Required

a) Forecasted Annual Sales: b) Forecasted Sales for November: Q.6 Your Answers- type the formula in words, show the steps and use Excel formulas to calculate e a) Calculate forecasted Sales for the Week b) Calculate the dollar variance between Forecasted Sales and Planned Sales. W c) Calculate the % (percent) variance between Forecasted Sales and Planned Sa Q. 7 Week 1 Week 2 Week 3 % of Period Sales 19.0% 21.0% 20.0%

Weekly Sales $ Plan $780,863 $863,059 $821,961 Your Answers- type the formula in words, show the steps and use Excel formulas to calculate e c) Calcuate the dollar variance between Forecasted Sales and Planned Sales. Wi d) Calculate the % (percent) variance between Forecasted Sales and Planned Sa Q. 8 a) Week Sales $ LYA 5 $295,435 …. $299,856 6 $245,392 7 $265,943 8 $276,424 Total Period $1,083,424 Round Lift to 2 decimal points b) Calculate the % (percent) variance between Forecasted Sales and Last Year Sa Q. 9 Given: EOP $ Plan $429,342 Actual BOP $ $653,942 On Order $ $233,411 Weekly Sales $ Actual/ Forecast Week-over- week Lift TY Actual/ Forecasted Sales $

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Forecasted Sales $ $680,573 Forecasted Reductions $ $90,655 Your Answers- type the formula in words, show the steps and use Excel formulas to calculate O Q. 10 Given: EOP $ Plan $2,000,835 Actual BOP $ $2,678,594 Received Orders $ $588,944 On Order $ $824,577 Forecasted Sales $ $1,986,422 Forecasted Reductions $ $85,999 Your Answers- type the formula in words, show the steps and use Excel formulas to calculate O

$ Sales for Volume Group Unit Sales/ Store Unit Sales/ Volume Group

53 23 49 28 28 19 32 14 19 12 17 9 198 105 each: Current Unit Inventory Unit Sales Forecast Replenishment Units Required

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

each: Will you achieve the Planned Sales? ales. Week 4 Week 5 Total Period 19.0% 21.0% 100.0%

each: ill you achieve the Planned Sales? ales. ales (LYA).

OTB: OTB:

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

RMG909 Lab 3- Worksheet

Related Documents