Part B. (30%) Submit in Week 12.
Using the figures provided below, you will calculate your stores open to buy.
Use the Excel Spreadsheet provided to complete this part. I must be able to see your calculation in the sheet. Show all work on an Excel spreadsheet in a traditional OTB grid format as the example indicates. I have completed the first month for you.
Assume you have an OTB of $1,025,000 at retail to spend to stock your store for the Spring 2025 season, which will encompass Feb, March, April, May, June and July of 2025.
B1. Calculate the open to buy for each month using the following calculations.
Planned sales + Planned reductions + Planned EOM Stock = Total monthly needs
Total monthly needs – BOM Stock = Planned purchases
Planned purchases – Merchandise on order = Open to buy
B2. Calculate average monthly sales
B3. Calculate average monthly on order
B1
OTB TERM PROJECT SPING 2025 Season
MONTHS
FEBRUARY
MARCH
APRIL
MAY
JUNE
JULY
TOTAL
PLANNED SALES
$400,000
$300,000
$200,000
$150,000
$100,000
$125,000
$1,275,000
EMPLOYEE DISCOUNT PERCENT
0.02
0.03
0.04
0.02
0.02
0.03
EMPLOYEE DISCOUNT DOLLAR
$8,000.00
MARKDOWN PERCENT
15%
MARKDOWN DOLLARS
$60,000
$45,000
$30,000
$22,500
$15,000
$18,750
$183,750
SHORTAGE DOLLARS
$6,000
SHORTAGE PERCENT
0.015
0.020
0.020
0.050
0.040
0.030
EOM
$300,000
$250,000
$150,000
$100,000
$75,000
$100,000
TOTAL MONTHLY NEEDS
$774,000
BOM
$300,000
$300,000
$250,000
$150,000
$100,000
$75,000
PLANNED PURCHASES
$474,000
MONTHLY ON ORDER
$87,500
$75,000
$50,000
$37,500
$25,000
$31,250
$306,250
OPEN TO BUY
$386,500
$1,025,000
B2
AVERAGE MONTHLY SALES
B3
AVERAGE MONTHLY ON ORDER
