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 |
|