Industry: Manufacturing

ESTABLISHING STRUCTURE AND GAINING CONTROL THROUGH TWO LEADING CLOUD-BASED SYSTEMS: DEAR INVENTORY AND XERO ACCOUNTING

Business type: Manufacturing

Industry:  Spirits

Size:  10+ employees, 3 locations

Distribution: South Africa – 90 + Outlets

Region: South Africa

Integrations:  Dear,   Xero

Customers since: June 2017

Website:  www.musgravegin.co.za

Musgrave is an artisanal gin and spirits brand founded by Simon Musgrave.

In 2017 this rapidly growing spirits business approached the systems experts at Creative CFO to help them implement and integrate cloud-based inventory management and accounting systems.

“We contacted Creative CFO because we reached the stage of being an established business. Initially, I started Musgrave as a sideline hobby, hoping that it would become something, and I found myself two years later with a rapidly growing business, a rapidly growing brand, 500% growth and I didn’t really know how to put one and one together.” – Simone Musgrave

 

With Creative CFO’s deep technical knowledge and extensive manufacturing experience in Dear inventory and Xero accounting, Simone could look forward to a seamless, stress-free implementation that would give structure as well as insights into key areas of the business.

In this case study you will discover:

• The key challenges Musgrave faced
• The key solutions Dear and Xero offered to solve their problems
• A Q&A with Simone Musgrave
• The Creative CFO systems implementation process
• Where to book a Creative CFO Systems Analysis.

Download the full case study to find out how Creative CFO’s systems experts helped Musgrave implement a scalable system that creates control and sophistication around their stock, manages multiple locations, supports a large inventory list and provides real-time reports that can be accessed from anywhere in the world.

Case Study – Musgrave Spirits by Louise de Nysschen

Gaining operational independence and control through two leading cloud-based systems: Dear Inventory and Xero Accounting

Business Type: Manufacturing

Industry: Building Materials

Size: 40+ employees, 2 locations

Region: South Africa

Integrations: Dear, Xero

Customers Since: June 2018

Website: www.thetilehouse.co.za

John Almon established The Tile House in 1988 – a hands-on family run business that sources and supplies high-quality tile products to the South African market.

In 2018 they decided to break away from a larger retail group and needed to become fully independent. This was their opportunity to move away from a centralised and bureaucratic laden  Enterprise Resource Planning (ERP) and migrate on to two comprehensive, understandable and affordable cloud-based systems, where real-time information is available, instantly.

The Tile House, subsequently approached Creative CFO’s systems experts to assist them with the implementation. With the Creative CFO’s deep technical knowledge and extensive experience in Dear Inventory and Xero Accounting, John and his management team could look forward to a seamless and stress-free systems implementation and roll out.

In this case study you will discover:

• The key challenges the Tile House faced 
• A deeper look into the key solutions Creative CFO offered and set up through Dear and Xero in order to solve their current problems
• The Creative CFO Systems Implementation Process
• Where to book a Creative CFO Systems Analysis

View the full case study to find out how Creative CFO successfully replaced and upgraded a full-fledged corporate group ERP with Dear and Xero.

Download The Tile House Case Study

Method 3 – Manual Manufacture at the time of Assembly (finished goods represented in inventory)

The idea behind this approach is to buy in raw materials, track the quantity and value of those raw materials and then convert raw materials into tracked finished goods when you do an assembly in your workshop.

Xero transfers the value of the raw materials to the finished goods item so that when you sell a finished good Xero will automatically bring the cost of sales across from the inventory account.

You set this up by:

• Creating tracked inventory items in Xero for all your raw materials
• Creating tracked inventory items for your finished goods (similar to a service item)

The finished good is therefore represented in your inventory once you have done this process and will be visible on stock reports.

Method 3 Example – see video here!

You start the month with no stock.

• Create the following tracked inventory items for your materials:
  • Bicycle frame
  • Bicycle wheel
  • Bicycle seat
• Create the following tracked inventory item for your finished good:
  • Bicycle complete
• Buy in quantities of tracked inventory items with the following prices:
  • Bicycle frame – QTY 5 at R750 each
  • Bicycle wheel – QTY 5 at R250 each
  • Bicycle seat – QTY 5 at R200 each

As Xero treats this as tracked inventory, you will see these values go directly to your balance sheet under inventory.

You’ll note that this should be enough to make 5 bicycles overall, with 1 frame, 2 wheels and 1 seat used in each assembly. This list is called thebill of materials as if you add up the numbers it should equate to R1400 of materials per completed bicycle.

Before you can make a sale of a tracked inventory item you must have stock of that item. Go test it, you will see in a sales invoice you can only create a draft and not an approved invoice for a complete bicycle.

So, let’s manufacture then.

We have two options to do this, one uses an invoice and one uses an inventory adjustment. Both work, the invoice method may be a bit quicker as you can do all the materials at once, the adjustment method does not leave a trail of ‘internal’ invoices.

• Manufacture 3 bicycles from the materials using an Invoice method
  • Create a sales invoice and sell the correct amount of materials for 3 bicycles to the production contact. The selling price is Zero
    • Note the impact on the profit and loss report. There should be R1400 x 3 = R4,200 in ‘Manufacturing Costs (should be zero)’

• • Create a purchase bill for 3 complete bicycles from the production contact. You need to use the overall value of the costs produced b the step above divided by the number of bicycles. For example, using our numbers above you will purchase in 3 complete bicycles at R1,400 each.
    • Pay off the purchase to the ‘Manufacturing Costs (should be zero)’
      • Note the impact on the profit and loss report. There should be no ‘Manufacturing Costs (should be zero)’ amount remaining.
• Manufacture 2 bicycles from the materials using an Inventory Adjustment method  
  • Sell the correct amount of materials for 2 bicycles to the production contact
  • Purchase 2 complete bicycles from the production contact

Now let’s do a sale

• Sell 4 bicycles to a customer for R5,000 each  
  • Note the impact on the profit and loss report.
    • Sales of R20,000. Cost of sales of R5,600 (4 bicycles @ R1,400 each)
  • Note what remains on the balance sheet and your inventory reports
    • One complete bicycle @ a value of R1,400

Note, you must perform the either of the two manufacture options, invoice or inventory adjustment, after each actual assembly in your workshop otherwise you will not be able to sell the finished goods in Xero.

Method 2 – The monthly manufacturing process (no finished goods are represented in inventory)

The basic idea behind this approach is to buy in raw materials, and track the quantity and value of those raw materials.

Then when you sell a finished good perform an extra step to reduce the raw materials used in that sale to create the correct cost of sale.

You set this up by:

• Creating tracked inventory items in Xero for all your raw materials
• Creating untracked inventory items for your finished goods (similar to a service item)

The finished good is therefore never actually represented in your inventory. Xero calls this an ‘untracked item’, and it’s treated the same as a service offering in that there is no physical quantity on hand and you can sell unlimited amounts.

You can still see how many of these items you sold but it never shows up on a stock on hand report.

Method 2 Example – see the video here

Click here for Part 2, and Part 3 of the video

You start the month with no stock.

You create the following tracked inventory items for your materials:

• Tricycle frame
• Tricycle wheel small
• Tricycle wheel big
• Tricycle seat

You create the following untracked inventory item for your finished good:

• Tricycle complete

You buy in quantities of tracked inventory items with the following prices:

• Tricycle frame – QTY 6 at R450 each
• Tricycle wheel small – QTY 12 at R50 each
• Tricycle wheel big – QTY 6 at R100 each
• Tricycle seat – QTY 5 at R75 each

As Xero treats this as tracked inventory, you will see these values go directly to your balance sheet under inventory.

You’ll note that this should be enough to make 6 tricycles overall, with 1 frame, 2 small wheels, 1 big wheel and 1 seat used in each assembly. This list is called the bill of materials as if you add up the numbers it should equate to R725 of materials per completed tricycle.

You then manufacture and sell a tricycle.

Note – Xero only allows you to sell these complete tricycles as they are treated as untracked inventory items.

After the sale, you can see the sales amount on your profit and loss but there is no cost of sales. If you check your balance sheet you will see the inventory is still there.

To process the cost of sales we need to do a production invoice to recognise that we have used up the raw materials during the month and sold them in the form of a finished bike.

The sale happens at zero value for all the components but this brings in the cost of sales.

When you do your stock count at the end of the month you should find you have the components for 5 tricycles still on hand. If you manufactured more but did not sell them what you will notice though is that since we are not using a method that allows finished goods to be represented you will see that the finished tricycles are still represented on the stock count as raw materials.

As only the raw materials are represented on your inventory list this method clearly works well when you manufacture to order or don’t hold many finished goods in stock over a month end.

This method is a step up from the stocktake method in terms of cost of sales accuracy as the only manual component now is entering the materials used in the manufacture of the finished goods.

Xero is calculating the average cost of your inventory so you don’t have to estimate the cost of the materials anymore. If you buy some materials at one price and some at another then Xero will keep track of this.

You can perform the cost of sale step after every finished good sale or once a month based on all finished goods sales for that month.

If you need to represent finished goods on your stock count please use Method 3.

Xero Inventory

Xero’s inventory module allows you to purchase in and sell inventory items using the average cost method.

This means if you purchase inventory, Xero will store the quantity on hand and the average cost of that inventory, and represent the sum of those amounts on your balance sheet under inventory.

This is preferable over immediately recognising any stock purchases as an expense, as when the item is sold the average cost of that inventory item will automatically be moved from the inventory account on the balance sheet to your profit and loss under the cost of sales.

This is important as it improves the reporting on your profit and loss report, and shows you the sales and related cost of sales in the same month.

Getting this timing right, sales and cost of sales in the same month is the main part of using an inventory module and allows you to work out your profit margins.

Here are some resources from Xero on how the inventory module works:

• Xero’s video: https://vimeo.com/122495680
• Xero’s blog: https://www.xero.com/blog/2015/03/introducing-new-and-improved-inventory/
• Xero’s managing inventory page: https://central.xero.com/s/article/Options-for-managing-inventory-in-Xero?userregion=true
• Xero’s tracked inventory guide: https://central.xero.com/s/article/Track-your-inventory?userregion=true

The options available to manufacture in accounting systems

In general, there are 3 main options to do manufacturing in Xero.

Method 1 – The monthly stocktake adjustment

In this method, you would expense all raw materials when you buy them. This results in a very high cost of sales in the month of purchase. At the end of the month, you do a stock take, assign values to all the items on hand, using purchase prices or your best average estimate, and reverse out some of the cost of sales to an inventory account on your balance sheet.

Example – see the video here!

You start the month with no stock. You buy R8,500 of stock and it shows up as an expense of R8,500 in the Cost of Sales section.

You then make some sales, let’s say R17,500 of sales using R4,500 of stock.

At this point, your profit and loss show R17,500 in sales and R8,500 in cost of sales. A cost margin of 48.57% (8,500/17,500)

When you do your stock count at the end of the month you should find you have R4,000 on hand still. You then post a journal to reverse out R4,000 from the Cost of Sales and add it to your balance sheet under Inventory.

Now your sales show R17,500, your cost of sales shows R4,500. This is a cost margin of  25.71%, which is a lot better than before. Your R4,000 of stock is sitting as inventory on your balance sheet.

This is a very rudimentary method and means that you cannot track individual sales against their cost of sales. It also means that your inventory balance and cost of sales amount are based on the estimated values of your stock count.

It is the simplest to perform, however, can be done in any accounting system, and is best suited when there is a single person in charge of the business and stock who already knows the margin and cost of sales for each item they sell.

The manual manufacturing process in Xero

Manufacturing cannot be done in Xero as a standard inventory workflow. This means:

• There is no bill of materials functionality to allow the manufacture or perform an assembly of, several raw material inventory items into a single finished good item.

• There is no automatic calculation for cost of sales when a finished good, comprising of a number of separate raw materials in inventory, is sold.

What Xero does allow however is for you to purchase raw materials into stock, track the quantity and cost of that inventory.

With this function, you can use a manual manufacturing process to get your cost of sales and sales aligned in the same month.

There are two ways of doing this after you have bought in the raw materials as tracked inventory items:

• Once a month, and based on what was sold, you expense the raw materials to cost of sales(method 2a).Finished goods set up as untracked ‘service’ items

• After each manufacturing run convert the raw materials used in the process into a specific quantity of available finished goods. This reduces raw materials on hand and increased finished goods(method 2b).Finished goods set up as tracked items

Method 2b requires you to follow the process after every assembly you make as you can only sell a tracked inventory item once it’s been assembled and is in stock. Xero will not let you sell the item if the quantity is zero.

If you do not have the financial team members to drive this assembly process in the system, then method 2a is preferable as you will always be able to make the sale as untracked items do not have a quantity on hand.

Learn more about Method 2