Ma and Pa's Purchase Needs Calculation Return To Tutorial In this illustration were going to calculate Cost Of Sales, Purchases, and Ending Inventory For a Three Month Period Step 1 Enter Estimated Sales and Beginning Inventory Estimated Sales From Sales Forecast Worksheet 109,564 32,756 36,145 40,663 34,450 43,487 Beginning Inventory 21000 Purchases Cost Of Sales Ending Inventory We begin this worksheet by entering our estimated sales and the actual beginng inventory as of the end of the prior period. Step 2 Calculate Cost Of Sales In Step 2 we're going to calculate our estimated Cost Of Sales Amounts Using the Percentage Rate we calculated from our Prior Year Analysis. The rate used in  this example is 55.77 % Estimated Sales From Sales Forecast Worksheet 109,564 32,756 36,145 40,663 34,450 43,487 Beginning Inventory 21000 Purchases Cost Of Sales 18268 20158 22678 Ending Inventory Cost Of Sales Calculation Sales 32756 36415 40663 Multiplied By 55.77 % 0.5577 0.5577 0.5577 Calculated Cost Of Sales 18268 20158 22678 Step 3 Calculate Ending and Beginning Inventories In our example were going to calculate our estimated ending inventory based on our calculated inventory turnover and the number of month's supply that we normally maintain for future sales normal (inventory levels). Our assumed Inventory Turnover is 1.01 which means that on the average we convert our average inventory into sales once a month. We convert this calculation into number of months supply on hand by dividing 12 by our Inventory Turnover. Next we develop our assumptions regarding what Cost of Sales Amount and Period where going to use as an estimate for a Months Cost Of Inventory Sold. Our Example uses the following assumptions: If our calculated Months Supply is <=1 Cost Of Sales For Next Period X Number Of Months Supply If our calculated Months Supply is >1 and <= 2 Average Cost Of Sales For Next Two Periods X Number Of Months Supply If our calculated Months Supply is >2 and <=3 Average Cost Of Sales For Next Three Periods X Number Of Months Supply If our calculated Months Supply >3 Average Cost Of Sales For Number Of Periods Estimated X Number Of Months Supply Our Calculated Months Supply is 1.01 so our Calculation will Use The Following Two  Months Cost Of Sales Note how  we also carry our calculated inventory forward to the next month as the Beginning Inventory for That Month Estimated Sales From Sales Forecast Worksheet 109,564 32,756 36,145 40,663 34,450 43,487 Beginning Inventory 21000 21650 21172 Purchases Cost Of Sales 18268 20158 22678 19213 24253 Ending Inventory 21632 21155 21950 Formula To Calculate Ending Inventory = Average Cost Of Sales For Next Two Months X Months Supply Average Cost Of Salses Next Month 20158 22678 19213 Second Following Month 22678 19213 24253 Average 21418 20946 21733 Average Cost Of Sales X  Months Supply Average Cost Of Sales 21418 20946 21733 Months Supply Factor 1.01 1.01 1.01 Calculated Ending Inventory 21632 21155 21950 Average Cost Of Sales X Months Supply Factor Step 4 Calculate Needed Purchases Step 4 is just a simple matter of doing some math. If you recall, we earlier discussed the simple inventory formula and how that if you knew three of the variables you can easily calculate the fourth. You do recall this don't you ? If not, Ending Inventory = Beginning Inventory + Purchases - Cost Of Sales In this case we want to calculate the amount of our Purchases By Period. Estimated Sales From Sales Forecast Worksheet 109,564 32,756 36,145 40,663 34,450 43,487 34,450 Beginning Inventory 21000 21632 21155 Purchases 18900 19681 23473 Cost Of Sales (rounded to whole dollars) 18268 20158 22678 Ending Inventory 21632 21155 21950 Our Formula For Calculating Purchases: Note that the values we now know are: Beginning Inventory Ending Inventory Cost Of Sales So to calculate Purchases we use the following formula: Purchases = Ending Inventory - Beginning Inventory + Cost Of Sales Ending Inventory 21632 21155 21950 Less: Beginning Inventory 21000 21632 21155 Plus: Cost Of Sales 18268 20158 22678 Calculated Purchases 18900 19681 23473 This is the calculation used in Bean Counter's Cash Forecasting Workbook. Of course, there are many other methods, ways, and formulas that could be used to calculate inventories and purchases. A very simple method would be to calculate your Purchases as a % Of Sales. Note: I don't know if you noticed, but our calculations were only for a three month period. Do you have any idea why we included sales and cost of sales calculations for an additional two months ? The reason is that our formula that calculates ending inventory is based on the next two months of sales and cost of sales amounts. Return To Tutorial