Use the Six Month Dollar Merchandising Plan spreadsheet to create a six-month merchandise plan for a Women's Sportswear department.

Using the percents and dollar amounts provided, plan a 9% increase for the next year. You will maintain the same monthly sales percentages, the same stock-to-sales ratios, and the same markdown percents by month as last year.

Last years spring season figures were as follows:

Departmental Markup: 54% Total Spring Sales Volume: $786,000 End of Season Stock: $257,000. Total Markdowns Planned: 12.5% of Season Sales The table provides the vital figures for last year's Spring Season:

Month

Pct of Season Sales

BOM Stock/Sales Ratio

Pct of Season Markdowns

Feb

12%

3.1

10.9%

Mar

22%

2.1

11.9%

Apr

21%

2.5

23.5%

May

17%

2.5

22.8%

Jun

18%

2.7

19.4%

Jul

10%

3.7

11.5%

First, calculate last years dollar amounts, using the percentages supplied above. Fill in last years information on the Six Month Merchandising Plan spreadsheet. Ensure that you have accurately filled in the following information:

Sales by month. Last years BOM and EOM stock dollars. Last years monthly markdown dollars. Once you have filled in data for sales by month, the BOM and EOM stock dollars, and last year's monthly markdown dollars, calculate the following and fill in the appropriate fields on the spreadsheet:

Last years monthly purchases at retail and cost. Last years average stock. Last year's stock-turnover rate. Finally, fill in the "plan" parts of the spreadsheet. Using last years information as the foundation, apply the percentage increase to overall sales and develop a plan for this year using the same monthly percentages. Use the same stock to sales ratio as last year to calculate stock figures.

Remember, End of Season Stock for this year is planned at $277,560. Be sure to use the same monthly markdown percent and markup percent to complete the calculations for the "plan" parts of the spreadsheet. Ensure that you plan average stock and turnover calculations before moving on to the next Part of the Final Exam.

all calculations must be form in a table