Six-Month Plan Assignment for a Retailer

Part 1: Six Month Plan Directions

This assignment has 2 parts.  You will complete the six-month plan spreadsheet using information you learned in unit 5 and through this course, and you will also write a short summary of your finished plan that explains what these numbers mean for the retailer as compared to last year and industry standards.  The plan template shows LY information for this store.  You will only fill in Plan information, you never adjust LY.  That means, all of your work should go in the blue highlighted spaces.  Note, that like the income statement you looked at in unit 1, all these numbers are in thousands.  That means that LY sales for the fall season were $735,000 but have been listed as $735.0 to shorten the amount of digits used.  Please keep the numbers in the format consistent with LY as shown.

Part 1: Six Month Plan Directions

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.Check yourself, 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 – do this 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.  You need to remember this formula on your own

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

Make sure everything is formatted correctly, check print preview to be sure it fits to one page.

Congratulations, you’ve just completed your first 6-month plan!

Part 2: Short Summary

Write a short summary explaining what this plan means for the retailer.  Answer the following questions:

1.How difficult will a 6.5% sales increase be to achieve?

2.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?

3.Do you think the 3% planned reductions in MDs will be easy to achieve?  Answer this by taking into account the planned inventory in comparison to last year.

4.Is the retailer’s turnover for the 6-month period better or worse than last year?  Is it in line with industry standards?