Cost accounting can be used with any type of manufacturing. While it is not always the most time-efficient way to report income or loss, it is an integral part of keeping inventory costs under control as well as knowing how to assign retail prices. Without knowing the actual per unit cost of a product, it would be nearly impossible to know where the breakeven point is.
What is the breakeven point? That would be taking revenue less expense and end up with zero, which ultimately means no profit and no loss. Expensing items as they are purchased does not enable the company to get an accurate breakeven point. While that is an easier and quicker way to do the books for most companies, it isn’t the best way for a manufacturing company.
What is considered manufacturing?
A manufacturing business is simply a business that creates products. This means that several different tangible items must be used to manufacture one final product. This can be as complicated as an automobile or as simple as a meal in a restaurant.
To keep descriptions as easy as possible, the recipe of a dessert will be used in the following example. Restaurants must purchase many different types of supplies to be used to make meals ordered by customers. While those supplies can be expensed as they are purchased, there must be a way to track what is used in each recipe, so a cost per food item sold can be established. Only then can a retail price be determined to charge the customer.
What is the Best way to Track Inventory?
Assuming the restaurant has only one recipe that it makes for its customers, let’s further assume that it takes five basic ingredients: flour, eggs, milk, sugar and apples. Keeping track of inventory can be accomplished using tracking software (such as the expensive Wasp , iMagic or the more economical BIC, which is $99 after a 30 day trial), a database (Access comes with Microsoft Office) or a simple spreadsheet. Since Microsoft Excel is a popular software that is on most home computers, that will be used in the rest of this article. Best of all, it’s free to those who already have it.
The best way to keep track of the restaurant’s supplies, as well as its recipe and usage would be to create separate sheets for each and use formulas to join the sheets together.
Following is a list of sheets to create on the Excel spreadsheet:
- Main Inventory with cost per unit
- Recipe with product used per serving
- Weekly purchases/sold/disposed of product sheets
Multi-page Spreadsheets can be a Cheap Inventory Control
The main inventory sheet would have the five basic supplies (or ingredients) on it. This sheet would also have the price each item costs and what size package the supply came in. For instance the eggs come in a crate of 48 eggs each. If each crate cost $5, the per-unit cost of each egg is 10.4 cents. The same calculations would be done for the rest of the inventory (see first Excel screen capture).
The next sheet would have the recipe and how much of each ingredient is used. This sheet would pull from the previous Inventory sheet to calculate the per-unit cost of each completed recipe (product). See second Excel screen capture for the formula to do this. Column I has the amount of product used per serving, which will be important when posting how many are sold and/or disposed of on the following sheets.
Next create the amount of sheets following the Inventory and the Recipe sheets based on the time period to be tracked along with how often supplies are usually purchased. If a year will be tracked in one spreadsheet and purchases are made on a weekly basis, then 53 sheets need to be created. For simplicity, let’s assume a month will be tracked and purchases are only made twice during the month. In this case three sheets will be created. The first is for the beginning balances of inventory and the remaining two will be for purchases and sold products.
How to use Excel 3-D Reference Formulas to Calculate Inventory
Simply copying/pasting the inventory to these sheets will save time. Then it’s just a matter of keeping track of how many servings are sold and disposed of (see third Excel screen capture) and entering them in the same cells on each of the sheets. Once these have been created, go back to the first Inventory sheet and update the formulas for the amount of product on hand. To pull from the beginning balances, purchases and sold/disposed of, the following 3-D reference formula would be used (see fourth Excel screen capture):
- =SUM('bb:2'!E6)-SUM('1:2'!K6)
The above formula adds the range of sheets for beginning balances and purchased ingredients less the sheet range for sold/disposed of servings. This gives an accurate inventory of how much of each product remains, so it is easy to see when and how much to order.
Once one spreadsheet has been created, save a master file to be used over and over again. Bringing the ending balances forward with each new spreadsheet will keep it easy to track inventory. Of course the example given is a very basic one, but it can be used for larger scenarios. An inventory tracking system like this would be ideal for those small businesses on a budget.
Judith Lee - Judith has been a writer for over two decades. She is also a blogger and book/movie reviewer.
