How to Calculate Ending Inventory

BUDGET 6  - DEVELOPING YOUR ENDING INVENTORY BUDGET

After the Purchase Budget, the Direct Manufacturing Labor Budget, and the Manufacturing Factory Overhead Budget have been Developed, the next step is to "Develop Your Ending Inventory Budget".

The unsold merchandise a business has on its shelves and in its stockroom at the Beginning of the accounting year is considered Beginning Inventory. The unsold merchandise a business has on is shelves and in its stockroom, at the End of its accounting year (IE its year end), is considered to be Ending Inventory. Since accounting periods following one another, the ending inventory of one month, becomes the beginning inventory of the following month. For instance, the ending inventory on October 31, becomes the beginning inventory on November 1. In addition, since accounting years following one another, the ending inventory, on the last day of a business year, becomes the beginning inventory for the first day of the following business year. Furthermore, a business having a year end of December 31, would calculate its ending inventory as of December 31. This ending inventory on December 31, would become the beginning inventory on January 1 of the following business year.

Most existing businesses determine their ending inventory of merchandise in units by actually counting the number of unsold products on their store's shelves and in their stockroom. They determine the dollar amount of the ending inventory by multiplying the number of unsold units, on their shelves and in their stockroom, by the cost of each unit. The final step involves adding all the different costs, for each type of product, together to arrive at the ending inventory in dollars.

Individuals, currently not in business, will not have actual physical units of product to count. As a result, ending inventory in units and in dollars must be forecasted (budgeted). The Ending Inventory Budget does just that.

Before we get started, it is important to understand exactly what the Ending Inventory Budget calculates. Your Ending Inventory Budget is used to determine your forecasted dollar investment in Inventory at the END of each forecasted period (IE the end of each forecasted business year). Furthermore, if your business year end is December 31 and your forecasting periods are 200A, 200B, and 200C, then the Ending Inventory Budget will be used to forecast your ending inventory in dollars on December 31, 200A, your ending inventory in dollars on December 31, 200B, as well as your ending inventory in dollars on December 31, 200C.

Recall from our Forecasting Case Study, Murray's year end is December 31 and his forecasting periods are 200X and 200Y. Therefore, the Ending Inventory Budget will be used to determine Murray's ending inventory (in dollars) on December 31, 200X and his ending inventory (in dollars) on December 31, 200Y.

Since retailers and manufacturers are distinct in nature, we have organized this section into two parts shown below.  Each method of how to Calculate Ending Inventory is fully discussed below. 

PART 1  -  The Ending Inventory Budget for a Retailer.
PART 2  -  The Ending Inventory Budget for a Manufacturer.

 

PART 1  -  The Ending Inventory Budget for a Retailer:

Below depicts the formula a retailer would use to calculate its Ending Inventory in Units and in Dollars.

Beginning Inventory (in units) XXX
Add: Units Purchased During the Year XXX
Less: Units Sold During the Year (XXX)
Equals: the Number of Units in Ending Inventory XXX
Multiply: the Cost Per Unit (or weighted average product cost) $X.XX
Equals Ending Inventory in Dollars $XXX

In order for you to better understand the formula, lets develop the 200X Ending Inventory Budget for Murray's Scholarship Business. Please Note: Murray would use the above formula because he is considered a retailer (IE retailer of information). In other words, Murray only buys finished products or diskettes; He does not make or manufacture them.

As the formula implies, the first piece of information we need is Murray's Beginning Inventory in Units. Since 200X will be his first year of operation, Murray does NOT have any beginning inventory. Therefore, his Beginning Inventory in Units is Zero (0 diskettes). The second item needed in the formula is the number of units Murray plans to purchase during 200X. Recall from Budget 3 entitled "Developing Your Purchase Budget", that Murray determined he would purchase a total of 4,864 diskettes from his supplier in 200X. The third item needed in the formula is the number of units or diskettes Murray estimates (forecasts) to sell during 200X. Recall from Budget 2 entitled "Developing Your Sales Budget" that Murray intends to sell 4,000 diskettes during 200X.

The final piece of information required is Murray's cost to purchase each unit (cost per diskette) from his supplier in 200X. Recall from Budget 1 entitled "Determining your Selling Price and Your Product Cost (per unit)", that Murray's supplier will charge him $3.00 for each diskette made in 200X. Please note: if Murray planned to sell more than one type of product, he would be required to calculate a weighted average product cost and use that value in the formula.

As you can see, the information needed to develop Murray's Ending Inventory Budget has already been created in his Purchase Budget, in his Sales Budget, and in his Selling Price and Product Cost Budget. Now lets "plug" the figure into the formula to determine Murray's Forecasted Ending Inventory for 200X.

Beginning Inventory of diskettes 0 diskettes
Add: Forecasted Diskettes to be Purchased During the Year 4,864 diskettes
Less: Forecasted Diskettes to be Sold During the Year (4,000) diskettes
Equals: the Number of Diskettes in Ending Inventory 864 diskettes
Multiply: the Cost to Purchase each Diskette $3.00
Equals: Ending Inventory in Dollars (864 x $3.00) $2,592

 

As you can see, the value of Murray's Forecasted Ending Inventory (in dollars) on December 31, 200X, is $2,592. This Ending Inventory amount will appear on his December 31, 200X Forecasted Balance Sheet as a Current Asset. Now lets calculate Murray's Ending Inventory for December 31, 200Y.

Once again, the first piece of information we need is Murray's Beginning Inventory in Units. Since the ending inventory in units of one business year becomes the beginning inventory of the following business year, Murray's beginning inventory (in units) on January 1, 200Y will be his ending inventory in units on December 31, 200X. Therefore, Murray's beginning inventory on January 1, 200Y, is 864 (see ending inventory in units on December 31, 200X above). The second item needed in the formula is the number of Units or finished products Murray plans to purchase during 200Y. Once again, recall from Budget 3 entitled "Developing Your Purchase Budget", that Murray determined he would purchase 8,216 diskettes from his supplier during 200Y. The third item needed in the formula is the number of units or diskettes Murray estimates (forecasts) to sell during 200Y. Recall from Budget 2 entitled "Developing Your Sales Budget" that Murray plans to sell 8,000 diskettes during 200Y. The final piece of information required, is Murray's cost to purchase each finished diskette from his supplier in 200Y. Recall from Budget 1 entitled "Determining your Selling Price and Your Product Cost (per unit)", that Murray anticipates each diskette will cost him $3.30 in 200Y.

Using the information from above and by using the formula, Murray can now calculate his Ending Inventory Budget for 200Y.

Beginning Inventory of diskettes 864 diskettes
Add: Forecasted Diskettes to be Purchased During the Year 8,216 diskettes
Less: Forecasted Diskettes to be Sold During the Year (8,000) diskettes
Equals: the Number of Diskettes in Ending Inventory 1,080 diskettes
Multiply: the Cost to Purchase each Diskette $3.30
Equals:  Ending Inventory in Dollars (1,080 x $3.30) $3,564

 

As you can see, the value of Murray's Forecasted Ending Inventory (in dollars) on December 31, 200Y is $3,564. This Ending Inventory amount will appear on his December 31, 200Y Forecasted Balance Sheet as a Current Asset.

Murray's Ending Inventory Budget on December 31, 200X and on December 31, 200Y can be summarized in the following manner.

ENDING INVENTORY BUDGET
For Years Ending December 31, 200X and 200Y

200X
200Y
Ending Inventory In Units 864 1080
Cost to Purchase each unit $3.00 $3.30
Ending Inventory in Dollars $2,592 $3,564

 

 

 

PART 2  -  The Ending Inventory Budget for a Manufacturer:

The following formula is used to develop a Manufacturer's Ending Inventory Budget.

Beginning Inventory in Units XXX
Add: Units Purchased During the Year XXX
Less: Units Sold During the Year (XXX)
Equals: the Number of Units in Ending Inventory XXX
Multiply: the Total Cost Per Unit (or weighted average product cost) $X.XX
Equals Ending Inventory in Dollars $XXX

 

As you can see, the formula is the same as the one used to calculate a retailer's' Ending Inventory Budget. Manufacturers, however, must consider additional variables when calculating their Total Cost Per Unit. The Total Cost Per Unit represents all the costs or expenses required to purchase or produce one finished unit of product. A clothing retailer, for example, would purchase finished products (clothing) from their supplier. Therefore, the retailer's Total Product Cost would consist of the cost to purchase an article of clothing plus the cost to ship each piece of clothing to their place of business. A manufacturing company, on the other hand, does NOT purchase finished products. Rather, a manufacturing company purchases raw materials and, by applying direct labor and factory overhead, converts these raw materials into finished products. As a result, raw material purchases, direct labor costs and factory overhead costs form a manufacturer's Total Product Cost(s). In other words, manufacturers must determine the cost of raw materials needed to make one finished product, the direct labor cost need to make one finished product, and the factory overhead cost to make one finished product. The sum of all these costs will become the manufacturer's Total Product Cost Per Unit.

If you have been developing the Budgets in sequence, then you would have already calculated the three items needed to determine your Total Product Cost (per unit). Furthermore, the Cost of Raw Materials per Unit is calculated in Budget 1 entitled "Determining your Selling Price and Product Cost per Unit" . The Direct Labor Cost per Unit is calculated in Budget 4 entitled "Developing your Direct Manufacturing Labor Budget". And the Factory Overhead Cost per Unit is calculated in Budget 5 entitled "Developing your Factory Overhead Budget"

Assume the XYZ Company determines the following costs are needed to manufacture one unit of finished product:

  • $154.00 worth of raw materials are needed to make each finished product;
  • 2 hours of labor, at $20 per hour, ( IE $40) is needed to make each product;
  • $6.00 of factory overhead is required to produce one finished product.


Therefore, the company's Total Product Cost or total cost to make one finished unit is $200 (IE $154.00 + $40.00 + $6.00 = $200 per unit).


NOTE:  If you plan to manufacture more than one product, a weighted average product cost should be calculated and used in the formula.

ONE FINAL NOTE FOR MANUFACTURERS:

The above Ending Inventory Budget assumes all units are finished and ready to be sold. Manufacturers, however, generally have ending inventory of raw materials (non-finished products) and work in process (partially completed products). Moreover, existing manufacturing firms would determine the costs associated with these items and include them in their financial statements. Individuals determining the feasibility of a manufacturing company, normally omit costs associated with non-finished products and deal entirely in finished units.

     

     

Categories: Forecasting