planing project
GHOST HAM
Mrch2860Inventorysample.xlsxSell Thru %
| Develop Inventory Using Sell Thru | |||||||
| Weekly Inventory by Sell Thru - Example | |||||||
| LY BOW $ | LY Sales $ | LY Sell Thru % | TY BOW $ | TY Sales $ | |||
| Wk 1 | $2,400.6 | $204.3 | 8.5% | $2,467.6 | $210.0 | ||
| Wk 2 | $2,526.1 | $209.4 | 8.3% | $2,593.7 | $215.0 | ||
| Wk 3 | $2,656.4 | $197.4 | 7.4% | $3,027.8 | $225.0 | ||
| Wk 4 | $2,907.3 | $182.3 | 6.3% | $3,269.3 | $205.0 | ||
| Total | $793.4 | $855.0 | |||||
| To Use Sell Thru % to calculate inventory, there needs to be sales plans | |||||||
| **This does not take into account receipts or markdowns | |||||||
| Week | LY Sell Thru | TY BOW $ | |||||
| Step 1 | Step 2 | ||||||
| Week 1 | = Wk 1 LY Sales / Wk 1 LY BOW $ | = Wk 1 TY Sales $ / Wk 1 LY Sell Thru % | |||||
| Week 2 | = Wk 2 LY Sales / Wk 2 LY BOW $ | = Wk 2 TY Sales $ / Wk 2 LY Sell Thru % | |||||
| Week 3 | = Wk 3 LY Sales / Wk 3 LY BOW $ | = Wk 3 TY Sales $ / Wk 3 LY Sell Thru % | |||||
| Week 4 | = Wk 4 LY Sales / Wk 4 LY BOW $ | = Wk 4 TY Sales $ / Wk 4 LY Sell Thru % | |||||
| Weekly Inventory by Sell Thru - Exercise | |||||||
| LY BOW $ | LY Sales $ | LY Sell Thru % | TY BOW $ | TY Sales $ | |||
| Wk 1 | $2,243.3 | $201.9 | $225.0 | ||||
| Wk 2 | $2,380.0 | $202.3 | $254.0 | ||||
| Wk 3 | $2,627.6 | $199.7 | $215.0 | ||||
| Wk 4 | $3,093.3 | $185.6 | $210.0 | ||||
| Total | $789.5 | $904.0 | |||||
| Plan Inventory using LY's Weekly Sell Thru | |||||||
| Should Week 4's Sell Thru for this year increase to keep inventory down? | |||||||
| Develop Inventory Using Sell Thru - Page 2 | |||||||
| Monthly Inventory by Sell Thru - Example | |||||||
| # of Weeks | LY BOM $ | LY Sales $ | LY Month Sell Thru % | LY Avg Week ST % | TY BOM $ | TY Sales $ | |
| Jan | 4 | $2,104.9 | $821.5 | 39.0% | 9.8% | $2,398.3 | $936.0 |
| Feb | 4 | $2,463.7 | $944.7 | 38.3% | 9.6% | $2,297.3 | $880.9 |
| Mar | 5 | $2,845.3 | $963.6 | 33.9% | 6.8% | $2,543.5 | $861.4 |
| Apr | 4 | $2,832.6 | $742.4 | 26.2% | 6.6% | $2,899.0 | $759.8 |
| Month | LY Month Sell Thru | LY Avg Week Sell Thru % | TY BOM $ | ||||
| Step 1 | Step 2 | Step 3 | |||||
| Jan | = Jan LY Sales $ / Jan LY BOM $ | = Jan LY Month ST % / Jan # of wks | = Jan TY Sales $ / Jan LY Month Sell Thru % | ||||
| Feb | = Feb LY Sales $ / Feb LY BOM $ | = Feb LY Month ST % / Feb # of wks | = Feb TY Sales $ / Feb LY Month Sell Thru % | ||||
| Mar | = Mar LY Sales $ / Mar LY BOM $ | = Mar LY Month ST % / Mar # of wks | = Mar TY Sales $ / Mar LY Month Sell Thru % | ||||
| Apr | = Apr LY Sales $ / Apr LY BOM $ | = Apr LY Month ST % / Apr # of wks | = Apr TY Sales $ / Apr LY Month Sell Thru % | ||||
| Monthly Inventory by Sell Thru - Exercise | |||||||
| # of Weeks | LY BOM $ | LY Sales $ | LY Month Sell Thru % | LY Avg Week ST % | TY BOM $ | TY Sales $ | |
| Jan | 4 | $2,243.3 | $789.5 | $850.0 | |||
| Feb | 4 | $2,380.0 | $907.9 | $915.0 | |||
| Mar | 5 | $2,627.6 | $926.1 | $937.0 | |||
| Apr | 4 | $3,093.3 | $742.4 | $785.0 | |||
| Plan Inventory using LY's Sell Thru | |||||||
| Would you change any Sell Thrus for this year? | |||||||
Sell Thru % Answer Key
| Develop Inventory Using Sell Thru | |||||||
| Week | LY Sell Thru | TY BOW $ | |||||
| Step 1 | Step 2 | ||||||
| Week 1 | = Wk 1 LY Sales / Wk 1 LY BOW $ | = Wk 1 TY Sales $ / Wk 1 LY Sell Thru % | |||||
| Week 2 | = Wk 2 LY Sales / Wk 2 LY BOW $ | = Wk 2 TY Sales $ / Wk 2 LY Sell Thru % | |||||
| Week 3 | = Wk 3 LY Sales / Wk 3 LY BOW $ | = Wk 3 TY Sales $ / Wk 3 LY Sell Thru % | |||||
| Week 4 | = Wk 4 LY Sales / Wk 4 LY BOW $ | = Wk 4 TY Sales $ / Wk 4 LY Sell Thru % | |||||
| Weekly Inventory by Sell Thru - Exercise | |||||||
| LY BOW $ | LY Sales $ | LY Sell Thru % | TY BOW $ | TY Sales $ | |||
| Wk 1 | $2,243.3 | $201.9 | 9.0% | $2,500.0 | $225.0 | ||
| Wk 2 | $2,380.0 | $202.3 | 8.5% | $2,988.2 | $254.0 | ||
| Wk 3 | $2,627.6 | $199.7 | 7.6% | $2,828.9 | $215.0 | ||
| Wk 4 | $3,093.3 | $185.6 | 6.0% | $3,500.0 | $210.0 | ||
| Total | $789.5 | $904.0 | |||||
| Plan Inventory using LY's Weekly Sell Thru | |||||||
| Should Week 4's Sell Thru for this year increase to keep inventory down? | |||||||
| Develop Inventory Using Sell Thru - Page 2 | |||||||
| Month | LY Month Sell Thru | LY Avg Week Sell Thru % | TY BOM $ | ||||
| Step 1 | Step 2 | Step 3 | |||||
| Jan | = Jan LY Sales $ / Jan LY BOM $ | = Jan LY Month ST % / Jan # of wks | = Jan TY Sales $ / Jan LY Month Sell Thru % | ||||
| Feb | = Feb LY Sales $ / Feb LY BOM $ | = Feb LY Month ST % / Feb # of wks | = Feb TY Sales $ / Feb LY Month Sell Thru % | ||||
| Mar | = Mar LY Sales $ / Mar LY BOM $ | = Mar LY Month ST % / Mar # of wks | = Mar TY Sales $ / Mar LY Month Sell Thru % | ||||
| Apr | = Apr LY Sales $ / Apr LY BOM $ | = Apr LY Month ST % / Apr # of wks | = Apr TY Sales $ / Apr LY Month Sell Thru % | ||||
| Monthly Inventory by Sell Thru - Exercise | |||||||
| # of Weeks | LY BOM $ | LY Sales $ | LY Month Sell Thru % | LY Avg Week ST % | TY BOM $ | TY Sales $ | |
| Jan | 4 | $2,243.3 | $789.5 | 35.2% | 8.8% | $2,415.2 | $850.0 |
| Feb | 4 | $2,380.0 | $907.9 | 38.1% | 9.5% | $2,398.5 | $915.0 |
| Mar | 5 | $2,627.6 | $926.1 | 35.2% | 7.0% | $2,658.6 | $937.0 |
| Apr | 4 | $3,093.3 | $742.4 | 24.0% | 6.0% | $3,270.8 | $785.0 |
| Plan Inventory using LY's Sell Thru | |||||||
| Would you change any Sell Thrus for this year? | |||||||
WOH
| Develop Inventory Using WOH | |||||||||
| Weekly Inventory by WOH - Example | |||||||||
| LY EOW $ | LY Sales $ | LY WOH | TY EOW $ | TY Sales $ | |||||
| Wk 1 | $2,106.8 | $230.4 | 9.1 | $2,240.3 | $245.0 | ||||
| Wk 2 | $2,293.6 | $205.6 | 11.2 | $2,565.8 | $230.0 | ||||
| Wk 3 | $2,436.5 | $202.3 | 12.0 | $3,191.7 | $265.0 | ||||
| Wk 4 | $2,729.0 | $198.4 | 13.8 | $3,438.8 | $250.0 | ||||
| Total | $836.7 | $990.0 | |||||||
| To Use WOH to plan inventory, there needs to be sales plan | |||||||||
| **This does not take into account receipts or markdowns | |||||||||
| Week | LY WOH | TY EOW $ | |||||||
| Step 1 | Step 2 | ||||||||
| Week 1 | = Wk 1 LY EOW $ / Wk 1 LY Sales $ | = Wk 1 TY Sales $ * Wk 1 LY WOH | |||||||
| Week 2 | = Wk 2 LY EOW $ / Wk 2 LY Sales $ | = Wk 2 TY Sales $ * Wk 2 LY WOH | |||||||
| Week 3 | = Wk 3 LY EOW $ / Wk 3 LY Sales $ | = Wk 3 TY Sales $ * Wk 3 LY WOH | |||||||
| Week 4 | = Wk 4 LY EOW $ / Wk 4 LY Sales $ | = Wk 4 TY Sales $ * Wk 4 LY WOH | |||||||
| Weekly Inventory by WOH - Exercise | |||||||||
| LY EOW $ | LY Sales $ | LY WOH | TY EOW $ | TY Sales $ | |||||
| Wk 1 | $2,243.3 | $201.9 | $222.0 | ||||||
| Wk 2 | $2,380.0 | $202.3 | $223.0 | ||||||
| Wk 3 | $2,627.6 | $199.7 | $215.0 | ||||||
| Wk 4 | $3,093.3 | $185.6 | $205.0 | ||||||
| Total | $789.5 | $865.0 | |||||||
| Plan Inventory using LY WOH | |||||||||
| Should the WOH be planned faster for this year's Week 4? | |||||||||
| Develop Inventory Using WOH - Page 2 | |||||||||
| Monthly Inventory by WOH - Example | |||||||||
| # of Weeks | LY EOM $ | LY Sales $ | Avg Weekly Sales | LY WOH | TY EOM $ | TY Sales $ | Avg Weekly Sales | ||
| Jan | 4 | $2,194.3 | $795.4 | 198.9 | 11.0 | $2,151.8 | $780.0 | $195.0 | |
| Feb | 4 | $2,543.2 | $914.7 | 228.7 | 11.1 | $2,363.3 | $850.0 | $212.5 | |
| Mar | 5 | $2,592.4 | $933.0 | 186.6 | 13.9 | $2,417.4 | $870.0 | $174.0 | |
| Apr | 4 | $2,432.6 | $985.6 | 246.4 | 9.9 | $2,344.7 | $950.0 | $237.5 | |
| Month | LY Avg Weekly Sales | LY WOH | TY Avg Weekly Sales $ | TY EOM $ | |||||
| Step 1 | Step 2 | Step 3 | Step 4 | ||||||
| Jan | = Jan LY Sales / # of wks | = Jan LY EOM $ / Jan LY Avg Wk Sales | = Jan TY Sales $ / # of Weeks | = Jan TY Avg Wk Sales * LY WOH | |||||
| Feb | = Feb LY Sales / # of wks | = Feb LY EOM $ / Feb LY Avg Wk Sales | = Feb TY Sales $ / # of Weeks | = Feb TY Avg Wk Sales * LY WOH | |||||
| Mar | = Mar LY Sales / # of wks | = Mar LY EOM $ / Mar LY Avg Wk Sales | = Mar TY Sales $ / # of Weeks | = Mar TY Avg Wk Sales * LY WOH | |||||
| Apr | = Apr LY Sales / # of wks | = Apr LY EOM $ / Apr LY Avg Wk Sales | = Apr TY Sales $ / # of Weeks | = Apr TY Avg Wk Sales * LY WOH | |||||
| Monthly Inventory by WOH - Exercise | |||||||||
| # of Weeks | LY EOM $ | LY Sales $ | Avg Weekly Sales | LY WOH | TY EOM $ | TY Sales $ | Avg Weekly Sales | ||
| Jan | 4 | $2,243.3 | $789.5 | $790.0 | |||||
| Feb | 4 | $2,380.0 | $907.9 | $935.0 | |||||
| Mar | 5 | $2,627.6 | $926.1 | $980.0 | |||||
| Apr | 4 | $3,093.3 | $742.4 | $750.0 | |||||
| Plan Inventory using WOH | |||||||||
WOH Answer Key
| Develop Inventory Using WOH | |||||||||
| To Use WOH to plan inventory, there needs to be sales plan | |||||||||
| **This does not take into account receipts or markdowns | |||||||||
| Week | LY WOH | TY EOW $ | |||||||
| Step 1 | Step 2 | ||||||||
| Week 1 | = Wk 1 LY EOW $ / Wk 1 LY Sales $ | = Wk 1 TY Sales $ * Wk 1 LY WOH | |||||||
| Week 2 | = Wk 2 LY EOW $ / Wk 2 LY Sales $ | = Wk 2 TY Sales $ * Wk 2 LY WOH | |||||||
| Week 3 | = Wk 3 LY EOW $ / Wk 3 LY Sales $ | = Wk 3 TY Sales $ * Wk 3 LY WOH | |||||||
| Week 4 | = Wk 4 LY EOW $ / Wk 4 LY Sales $ | = Wk 4 TY Sales $ * Wk 4 LY WOH | |||||||
| Weekly Inventory by WOH - Exercise | |||||||||
| LY EOW $ | LY Sales $ | LY WOH | TY EOW $ | TY Sales $ | |||||
| Wk 1 | $2,243.3 | $201.9 | 11.1 | $ 2,466.7 | $222.0 | ||||
| Wk 2 | $2,380.0 | $202.3 | 11.8 | $ 2,623.5 | $223.0 | ||||
| Wk 3 | $2,627.6 | $199.7 | 13.2 | $ 2,828.9 | $215.0 | ||||
| Wk 4 | $3,093.3 | $185.6 | 16.7 | $ 3,416.7 | $205.0 | ||||
| Total | $789.5 | $865.0 | |||||||
| Plan Inventory using LY WOH | |||||||||
| Should the WOH be planned faster for this year's Week 4? | |||||||||
| Develop Inventory Using WOH - Page 2 | |||||||||
| Month | LY Avg Weekly Sales | LY WOH | TY EOM $ | TY Avg Weekly Sales $ | |||||
| Step 1 | Step 2 | Step 4 | Step 3 | ||||||
| Jan | = Jan LY Sales / # of wks | = Jan LY EOM $ / Jan LY Avg Wk Sales | = Jan TY Avg Wk Sales * LY WOH | = Jan TY Sales $ / # of Weeks | |||||
| Feb | = Feb LY Sales / # of wks | = Feb LY EOM $ / Feb LY Avg Wk Sales | = Feb TY Avg Wk Sales * LY WOH | = Feb TY Sales $ / # of Weeks | |||||
| Mar | = Mar LY Sales / # of wks | = Mar LY EOM $ / Mar LY Avg Wk Sales | = Mar TY Avg Wk Sales * LY WOH | = Mar TY Sales $ / # of Weeks | |||||
| Apr | = Apr LY Sales / # of wks | = Apr LY EOM $ / Apr LY Avg Wk Sales | = Apr TY Avg Wk Sales * LY WOH | = Apr TY Sales $ / # of Weeks | |||||
| Monthly Inventory by WOH - Exercise | |||||||||
| # of Weeks | LY EOM $ | LY Sales $ | Avg Weekly Sales | LY WOH | TY EOM $ | TY Sales $ | Avg Weekly Sales | ||
| Jan | 4 | $2,243.3 | $789.5 | 197.4 | 11.4 | $2,244.7 | $790.0 | $197.5 | |
| Feb | 4 | $2,380.0 | $907.9 | 227.0 | 10.5 | $2,451.0 | $935.0 | $233.8 | |
| Mar | 5 | $2,627.6 | $926.1 | 185.2 | 14.2 | $2,780.6 | $980.0 | $196.0 | |
| Apr | 4 | $3,093.3 | $742.4 | 185.6 | 16.7 | $3,125.0 | $750.0 | $187.5 | |
Receipts
| Develop Inventory (BOM) by Using Receipts | ||||||||||
| Receipts - Example | ||||||||||
| Feb | Mar | Apr | May | Jun | Jul | Aug | Total | Avg | Turn | |
| BOM $ | $113.3 | $108.3 | $143.9 | $151.8 | $163.5 | $128.4 | $169.3 | $139.8 | 1.34 | |
| Sales $ | $29.9 | $34.8 | $28.8 | $27.3 | $36.8 | $29.3 | $186.9 | |||
| Receipt $ | $24.9 | $70.4 | $36.7 | $39.0 | $1.7 | $70.2 | $242.9 | |||
| **This does not take into account markdowns | ||||||||||
| If given BOM: | If given RECEIPTS: | |||||||||
| Month | Receipt $ | BOM $ | ||||||||
| Feb | = Mar BOM $ + Feb Sales $ - Feb BOM $ | Feb Receipts do not change Feb BOM $ | ||||||||
| Mar | = Apr BOM $ + Mar Sales $ - Mar BOM $ | = Feb BOM $ - Feb Sales $ + Feb Receipt $ | ||||||||
| Apr | = May BOM $ + Apr Sales $ - Apr BOM $ | = Mar BOM $ - Mar Sales $ + Mar Receipt $ | ||||||||
| May | = Jun BOM $ + May Sales $ - May BOM $ | = Apr BOM $ - Apr Sales $ + Apr Receipt $ | ||||||||
| Jun | = Jul BOM $ + Jun Sales $ - Jun BOM $ | = May BOM $ - May Sales $ + May Receipt $ | ||||||||
| Jul | = Aug BOM $ + Jul Sales $ - Jul BOM $ | = Jun BOM $ - Jun Sales $ + Jun Receipt $ | ||||||||
| Aug | = Jul BOM $ - Jul Sales $ + Jul Receipt $ | |||||||||
| EXERCISE : Find Receipts | ||||||||||
| Feb | Mar | Apr | May | Jun | Jul | Aug | Total | Avg | Turn | |
| BOM $ | $223.3 | $208.3 | $243.0 | $266.5 | $292.5 | $277.5 | $288.9 | |||
| Sales $ | $49.6 | $45.8 | $38.9 | $56.0 | $42.7 | $55.4 | ||||
| Receipt $ | ||||||||||
| Increase Inventory Turn by Decreasing above Exercise Receipts 5% Each Month | ||||||||||
| Feb | Mar | Apr | May | Jun | Jul | Aug | Total | Avg | Turn | |
| BOM $ | $223.3 | |||||||||
| Sales $ | $49.6 | $45.8 | $38.9 | $56.0 | $42.7 | $55.4 | ||||
| Receipt $ | ||||||||||
| Decrease Inventory Turn by Increasing above Exercise Receipts 5% Each Month | ||||||||||
| Feb | Mar | Apr | May | Jun | Jul | Aug | Total | Avg | Turn | |
| BOM $ | $223.3 | |||||||||
| Sales $ | $49.6 | $45.8 | $38.9 | $56.0 | $42.7 | $55.4 | ||||
| Receipt $ |
Receipts Answer Key
| Develop Inventory (BOM) by Using Receipts | ||||||||||
| **This does not take into account markdowns | ||||||||||
| Month | Receipt $ | BOM $ | ||||||||
| Feb | = Mar BOM $ + Feb Sales $ - Feb BOM $ | Feb Receipts do not change Feb BOM $ | ||||||||
| Mar | = Apr BOM $ + Mar Sales $ - Mar BOM $ | = Feb BOM $ - Feb Sales $ + Feb Receipt $ | ||||||||
| Apr | = May BOM $ + Apr Sales $ - Apr BOM $ | = Mar BOM $ - Mar Sales $ + Mar Receipt $ | ||||||||
| May | = Jun BOM $ + May Sales $ - May BOM $ | = Apr BOM $ - Apr Sales $ + Apr Receipt $ | ||||||||
| Jun | = Jul BOM $ + Jun Sales $ - Jun BOM $ | = May BOM $ - May Sales $ + May Receipt $ | ||||||||
| Jul | = Aug BOM $ + Jul Sales $ - Jul BOM $ | = Jun BOM $ - Jun Sales $ + Jun Receipt $ | ||||||||
| Aug | = Jul BOM $ - Jul Sales $ + Jul Receipt $ | |||||||||
| Find Receipts: | ||||||||||
| Feb | Mar | Apr | May | Jun | Jul | Aug | Total | Avg | Turn | |
| BOM $ | $223.3 | $208.3 | $243.0 | $266.5 | $292.5 | $277.5 | $288.9 | $257.1 | 1.12 | |
| Sales $ | $49.6 | $45.8 | $38.9 | $56.0 | $42.7 | $55.4 | $288.4 | |||
| Receipt $ | $34.6 | $80.5 | $62.4 | $82.0 | $27.7 | $66.8 | $354.0 | |||
| Increase Inventory Turn by Decreasing above Exercise Receipts 5% Each Month | ||||||||||
| Feb | Mar | Apr | May | Jun | Jul | Aug | Total | Avg | Turn | |
| BOM $ | $223.3 | $206.6 | $237.2 | $257.6 | $279.5 | $263.1 | $271.2 | $248.4 | 1.16 | |
| Sales $ | $49.6 | $45.8 | $38.9 | $56.0 | $42.7 | $55.4 | $288.4 | |||
| Receipt $ | $32.9 | $76.5 | $59.3 | $77.9 | $26.3 | $63.5 | $336.3 | |||
| Decrease Inventory Turn by Increasing above Exercise Receipts 5% Each Month | ||||||||||
| Feb | Mar | Apr | May | Jun | Jul | Aug | Total | Avg | Turn | |
| BOM $ | $223.3 | $210.0 | $248.8 | $275.4 | $305.5 | $291.9 | $306.6 | $265.9 | 1.08 | |
| Sales $ | $49.6 | $45.8 | $38.9 | $56.0 | $42.7 | $55.4 | $288.4 | |||
| Receipt $ | $36.3 | $84.5 | $65.5 | $86.1 | $29.1 | $70.1 | $371.7 |
