1.Plan Total Sales -Plan total sales at a 6.5% increase to last year.This goes in space J11.

2.Plan monthly sales - find each month's percent to last year's total then take this year's sales times that percent. (So the formula for AUG = (D13/$J$13)*$J$11)

3. if you are correct, each month should have a 6.5% increase over LY.

4.Plan your BOMs using a 4.5 Stock to Sales Ratio for all months except for December which should be a 3.0 SS Ratio.

5.Do not figure any BOM for the total.As BOM and EOM are only monthly figures, they cannot be calculated as a total.

6.Calculate MD's.This year's markdowns will be planned down 3% from LY.Calculate this year's total markdowns and type in cell J25.

7.Plan monthly MD's - the same way you did step 2 (monthly sales).

8.Calculate the MD % for each month and the total - you need to remember the formula for MD% yourself

9.Calculate the shortage $ for the 6-month period.Shortage % will be planned the same as LY at 2% for the year.This will go in cell J31.Remember the shortage $ formula from unit 4.

10. You will be taking physical inventory in the same month you did LY, type the same amount you just got (the actual number) in cell I31. Remember that shortage is only figured after physical inventory is taken.This store takes it once a season so all months are blank except the month when the inventory is completed.

11. Calculate the shortage % in cells I34 and J34.

12. You know your EOM numbers through DEC (remember that EOM = BOM of the next month).Type them in now.Your Jan EOM = $435.Type that in now as well.

13. Plan your purchases at retail through December.Use the purchase formula from unit 5.

14. Plan your IMU on purchases.Your IMU for each month will be 71%.Type that in every month and the total (starting in cell D22 through cell J22).Remember that stores plan an IMU and though each item varies, the IMUs are averaged with the goal of achieving the planned IMU.

15. Plan purchases at cost for each month, remember if you have retail and MU$ you can find cost.

16. Find this year's average inventory for each month (remember you cannot copy this formula as the number of inventories changes each month it's figured)

17. The total average inventory number should = January's average inventory

18. Calculate each month's SS Ratio (the total column should be blank)

19. Figure each month's turnover as well as the total turnover

• How difficult will a 6.5% sales increase be to achieve?
• Do you think it was a good idea for the retailer to reduce their inventory by planning for a smaller stock to sales ratio?Is the planned stock to sales ratio better or worse than last year?Is it in line with industry standards?
• Do you think the 3% planned reductions in MDs will be easy to achieve?
• Is the retailer's turnover for the 6-month period better or worse than last year?Is it in line with industry standards?

\f