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