Visual Cash Focus - User Tip 41
Work in Progress and Finished Goods
How to budget WIP for service based organisations
How to link WIP to Finished Goods for manufacturers
The Scenarios
1. We are a Chartered Accounting firm that generates WIP continuously but only
invoice our clients on the completion of the consulting or compliance related
task.
2. We produce Honey in various grades. There is a seasonal harvesting and
production process to create the various finished grades. Sales occur all year
round.
3. We run a Mining Operation and continually stock pile raw material before it’s
eventually processed and becomes finished goods. We sell the finished goods
as our clients order it.
How do you model these different scenarios in VCF?
Background
Like the CA firm, Mining companies, Manufacturers, all Growers and Producers have
in common a production cycle that’s different to their products selling cycle. In some
cases the timing difference is very small but in others it can be several months or
seasons.
In the case of growers like wineries, fruit, honey, and other horticulture and
agricultural producers there are seasonal “growing and production” periods followed
by harvesting and sales.
The issue in common to these industries is the timing difference between incurring
direct production and processing expenses and generating the respective revenues.
In VCF to correctly match revenue streams with the “cost of generating those
revenues” a transfer is made to a balance sheet WIP (inventory) holding account
during the “non sale” periods. For the CA firm and other service providers WIP is
then released via Cost of Sales to match revenue streams. In the case of the
manufacturers WIP is transferred to Finished Goods then sold down via Cost of Sales.
Let’s look at two scenarios.
Scenario 1: Expenses recovered to WIP and released via COS
Scenario 2: Transfer WIP to Finished Goods and the sale of Finished Goods
via COS
Visual Cash Focus “How to” CFO time saver tips.
www.strategicfocus.co.nz
The Solution
Scenario 1: Expenses recovered to WIP and released via COS
This scenario applies to CA firms, other service providers and smaller producer
businesses that do not hold a stock of finished goods for sale.
Step 1 – Budget all Direct Expenses
Under Direct Expenses budget all the direct expenses associated with the revenue
generation.
In the case of the CA/Legal firm this would include all the Salaries and Wages of the
employees except for those dedicated to Administrative tasks. The list should include
notional salaries for the partners.
Step 2 - Add a line called Transfer costs to WIP
In the picture below for a “Honey producer” we have called this line “Harvest Costs
Capitalised to WIP”. A CA/Legal firm could call this “Transfer costs to WIP”.
Visual Cash Focus “How to” CFO time saver tips.
www.strategicfocus.co.nz
Step 3 – Amend the “Budget Set up” for the transfer line.
From the Budget Setup tab
• Select the budget option “% Expense or COS (variable on other
Expense/COS)”
• Click “Change” and select all the related Direct Costs, in this case we selected
all the Harvesting costs.
Note that there are three reasons for recommending this set up
•
•
•
To automate the calculation and transfer to WIP i.e. when ever a direct
expense is updated VCF will automatically recalculate the transfer to WIP
We are removing the profit impact of the selected Direct Expenses.
The margin on Sales will be calculated via a Cost of Sales account.
Tip - note that I have put a space in front of all the “Direct expense” account
descriptions. This makes it easy to distinguish between Direct and Overhead
Expenses when selecting the “Change button”. (See the following picture).
With the space they are automatically listed in a block at the top of the selection.
Another advantage is that they will also be listed in a block on the Cash Flow
Statement.
Visual Cash Focus “How to” CFO time saver tips.
www.strategicfocus.co.nz
Step 4 – Enter the budget %
Enter the budget % as -100%. The effect of this negative budget % is to subtract
the total of the direct expenses selected so that they have a nil profit impact.
Note that the expense lines will still appear in the cash flow. The transfer will not.
Step 5 – Add a new Cash Flow profile
•
•
•
Right click the default Cost of Sales expense profile and choose “Add New”
Give the new profile the same name as the transfer account description. In
this case the profile is called “Harvest cost Capitalised”. In the CA/Legal firm
scenario this could be called “Transfer costs to WIP”.
Change the profile to read 100/99/CI/Bank/Inventory as illustrated below.
Note that I have already been to the balance sheet inventory account and renamed
the default inventory account to “WIP” as shown (refer Step 6).
Visual Cash Focus “How to” CFO time saver tips.
www.strategicfocus.co.nz
Step 6 – WIP account setup
•
•
Exit Direct Expenses and go to Balance Sheet Inventory accounts.
Either rename the default Inventory account description or add a new
inventory account called “WIP”.
Tip - If you rename the default inventory account to WIP the A/P link in the Transfer
profile above will change automatically. If you have added a new WIP account go
back to direct costs and edit the transfer profile and change the A/P link to your new
WIP account.
Under Inventory
•
•
Edit the Budget Setup for the WIP account and select “Purchase Target
Specified” from the four options.
Go to the “budget per period” tab and click in the budget window to confirm a
purchase budget of zero.
Note - You need to click on at least one zero to confirm it. The reason for the “zero
budget” is to turn off any unwanted inventory purchases. (Cash Focus automatically
purchases negative inventory balances). The purpose of the WIP account is to hold
the net result of balances being transferred in and out.
An activation error will occur if there are any negative WIP account balances. This
situation will only happen in the unlikely event that WIP is over recovered.
Step 7 – Cost of Sales
The final step is to create a new Cost of Sales account.
• Click New to add a cost of goods sold account
• Under Account Properties link the COS account the WIP account.
Here we see a COS account for each product type. Note that it is linked to the WIP
account.
Visual Cash Focus “How to” CFO time saver tips.
www.strategicfocus.co.nz
•
•
Under Budget Setup select “% variable on Sales”
Enter the “cost per dollar of sales” as a %.
Note that there are no timing assumptions required for Cost of Sales
accounts.
In the case if the CA firm (1-COS) % is the expected margin per dollar of
sales.
In the case of the Honey producer, the COS% will be based on a detailed
product costing (refer ABC Focus).
Step 8 – Activate and confirm the results
A demonstration model illustrating this set up is available on request to
User Support Subscribers
Visual Cash Focus “How to” CFO time saver tips.
www.strategicfocus.co.nz
Scenario 2: Transfer WIP to Finished Goods and the sale of Finished Goods
via COS
Let’s extend the above scenario by first looking at how to transfer WIP to Finished
Goods and then secondly how to link the Finished Goods accounts to Cost of Goods
Sold.
The transfer from WIP to finished goods is usually done according to a production
budget. This method is used by Wineries, Mining Operations, Processors and other
Manufacturing companies that produce finished goods for resale.
The methodology to be used as follows.
1. Direct production expenses are transferred to WIP, i.e. Dr WIP, Cr Direct
Expenses (as in scenario 1 above)
2. WIP is transferred to Finished Goods according to a production/processing
budget, i.e. Dr Finished Goods, Cr WIP
3. Finished Goods are sold via a COGS account i.e. Dr COGS, Cr Finished Goods
We will use the Honey producer to illustrate this methodology
Steps 1 – 6 are as above in scenario 1
Step 7 – Finished Goods
•
Under the Inventory section add the required Finished Goods account(s).
See in our Honey example below there is a “Finished Goods” account for each grade
of Honey produced and our previously created WIP. The consumables account was
created for the purpose of holding an opening balance.
Step 8 – Select Purchase Target Specified
•
Change the Budget Setup of all Finished Goods accounts to Purchase Level
Specified
Visual Cash Focus “How to” CFO time saver tips.
www.strategicfocus.co.nz
Step 9 – Enter a production or processing budget
•
Enter a production budget. This budget is the $ cost of honey produced for
each grade entered into the respective production periods.
This budget could be driven/based on the number of tones x quantity from the
Non Financial Misc area.
Note that the production/processing budget illustrated is based on a seasonal pattern
peaking in December and January. Other manufacturers like Wineries may have a
continuous flat processing budget representing the transfer of Bulk wine to bottles as
required for resale.
Visual Cash Focus “How to” CFO time saver tips.
www.strategicfocus.co.nz
Step 10 – Add a new Cash Flow Profile
The default inventory setting for VCF is to automatically purchase/replace inventory
as it is sold. The options allow the user to determine when the purchase takes place
relative to sales and how to pay for the purchases. The trigger for the purchase and
payment is the credit entry from COGS.
So that Finished Goods purchases are taken from our WIP account(s) we will add a
new Cash Flow profile for the Finished Goods account and change the default link
from Accounts Payable to WIP.
•
•
Add a new Cash Flow profile “Transfer from WIP”
Edit the “Transfer to WIP” profile and change the profile to read
100/99/CO/Bank/WIP as illustrated below. Ignore the PU line.
Note that the 99 makes this a non cash transaction. Uncheck the “show A/P accounts
only” to select WIP.
•
Close the profile and right click to copy to all periods
Step11 – Cost of Sales
The final step is to create a new Cost of Sales account.
• Click New to add a cost of goods sold account
• Under Account Properties link the COS account to the respective Finished
Goods account.
Visual Cash Focus “How to” CFO time saver tips.
www.strategicfocus.co.nz
Here we see a COS account for each product type. Note that it is linked to Finished
Goods for 5+
Step 12 – Activate and confirm the results
A demonstration model illustrating this set up is available on request to
User Support Subscribers
Visual Cash Focus “How to” CFO time saver tips.
www.strategicfocus.co.nz