Counting the beans, while counting the beans: simultaneously track inventory by units and by dollar value - without double-entry accounting. (includes related article on the macro used in the example worksheet) (tutorial)

Counting the Beans, While Counting the Beans

Marvin Gardener Jr., fresh out of business school, is ready to take over his dad's bustling fruit business, Fast Fruit Inc. But because of his dad's cigar-box method of record keeping, he can't tell how many boxes of peaches are on hand, how much the store paid for them, or even whether Fast Fruit is profitable.

To get a handle on how he's doing, Marvin Jr. needs to track the amount of fruit bought and sold each day, the dollar value of the purchases and sales, and the total and unit values of the remaining inventory. Because Fast Fruit buys its stock at constantly varying prices, the average value of fruit on hand is always changing. Full-fledged accounting software, such as Peachtree III or DacEasy, would tell Marvin what he needs to know, but these are complicated double-entry systems, and Marvin isn't sure Fast Fruit needs formal double-entry accounting. On the other hand, simpler, less-expensive single-entry accounting tools, such as Quicken, track only the dollar value of inventory, not the units.

Fortunately, Marvin Jr. learned 1-2-3 at B-school. With the 1-2-3 worksheet described in this article, he can do the job simply and quickly, without buying any new software. This worksheet should help any business that wants to maintain and keep account of inventory in an environment where suppliers' prices fluctuate.

Suppose that, at the beginning of peach season, Fast Fruit buys 40 bushels of peaches at a total cost of $120, or $3 a bushel. There were no peaches on hand before this, so the store now holds $120 worth of peaches. The store sells 15 bushels the first day. Since each bushel is valued at $3, the store now has $75 worth of peaches left on hand. The next day Marvin purchases another 10 bushels at a total cost of $26. Fast Fruit now holds 35 bushels of peaches, with a total value of $101. The first 25 bushels were bought at $3 each, while the last 10 cost $2.60 each. The average value of peaches on hand is $2.89 a bushel. Any peaches sold that day are valued at that amount.

This model automatically tracks all the variables in the above example. A macro assists with the entry of purchases and …