In: Accounting
Fill in all question marked areas of the grid and write the formula for each value together.
Open-To-Buy Worksheet | |||||||
BOM | Plan Sales | Sales % to total | Markdown $ | Md % | OTB | Stock-To-Sales | |
February | 35,000 | $9,000 | 11.5% | $1,000 | ???? | ???? | 3.88 |
March | 41,000 | $15,000 | 19.2% | $2,000 | 13.3% | 20,000 | ???? |
April | 44,000 | $18,000 | 23.1% | $2,000 | ???? | ???? | ???? |
May | 41,000 | $15,000 | ???? | ???? | 10.0% | ???? | 2.73 |
June | 38,000 | ???? | 15.4% | $3,000 | ???? | 12,000 | ???? |
July | ???? | $9,000 | ???? | $2,500 | ???? | ???? | 3.89 |
EOM July | 39,000 | ||||||
Total Spring | $78,000 | ???? | ???? | ||||
Average Stock= ???? | |||||||
Turn= ???? | |||||||
Stock-to Sales Ratio = BOM Stock / Sales for the month | |||||||
BOM | Plan Sales | Sales % to total | Markdown $ | Md % | OTB | Stock-To-Sales | |
February | 35,000 | $9,000 | 11.50% | $1,000 | 11.11% | 16000 | 3.88 |
March | 41,000 | $15,000 | 19.20% | $2,000 | 13.30% | 20,000 | 2.73 |
April | 44,000 | $18,000 | 23.10% | $2,000 | 11.11% | 17000 | 2.44 |
May | 41,000 | $15,000 | 19.23% | $1,500 | 10.00% | 13500 | 2.73 |
June | 38,000 | $12,000 | 15.40% | $3,000 | 25.00% | 12,000 | 3.17 |
July | $35,010 | $9,000 | 11.54% | $2,500 | 27.78% | 15490 | 3.89 |
EOM July | 39,000 | ||||||
Total Spring | $78,000 | 100 | $12,000 | 98.30% | |||
Average Stock= | 74,000 | ||||||
Turnover Ratio | 1.05 | ||||||
OTB Formulla | Planned Sales + Planned Markdowns + Planned End of Month Inventory - Planned Beginning of Month Inventory | ||||||
Feruary= | =9000+1000+41000-35000 | 16000 | |||||
April | =18000+2000+41000-44000 | 17000 | |||||
May | =15000+1500+38000-41000 | 13500 | |||||
July | =9000+2500+39000-35010 | 15490 | |||||
Stock To Sale : | =Current Month BOM/ Plan Sale | ||||||
March | =41000/15000 | 2.73 | |||||
April | =44000/18000 | 2.44 | |||||
June | =38000/12000 | 3.17 | |||||
Markdown % | =Markdown/Plan Sale | ||||||
February | =1000/9000 | 11.11% | |||||
April | =2000/18000 | 11.11% | |||||
June | =3000/12000 | 25.00% | |||||
July | =2500/9000 | 27.78% | |||||
Plan Sale | |||||||
June Plan Sale | =Total Sale Given- Rest Months Total Sale | ||||||
=78000-9000-15000-18000-15000-9000 | =12000 | ||||||
July BOM | =Plan Sale*Stock To Sale | ||||||
=9000*3.89 | =35010 | ||||||
Average Stock | =February Beginning Stock+End of June Month Inventory | ||||||
=(35000+39000) | =74000 | ||||||
Stock Turnover Ration | =Total sales / Aerage Stock | ||||||
=78000/74000 | =1.05 | ||||||