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 |