Sheet Costs 01
Contents
Sheet “Costs 01” is dedicated to making inputs and providing calculations regarding:
1. Cost of materials/packaging and goods required as well as
2. Inputs and calculations regarding the target inventory (stocks).
Cost of materials/packaging and goods required
The section cost of materials/packaging or goods required offers three different planning methods for each product, which can be used either individually or in combination.
| 1. | Direct Input: For each product, enter costs manually for each period (month) |
| 2. | Costs x Quantity: Quantities are linked from the “Sales” sheet. In addition, you need to define the cost of materials/goods per unit for each period. |
| 3. | Percentage of Sales: Using this method you define a percentage of the sales of the corresponding product. The “cost-of-sales ratio” (in percent) can be set individually for each year to take into account cost reductions due to increased production volumes, price advantages due to rising purchasing volumes, or other factors. |
|
Note All assumption values should be entered net of input/output taxes (like VAT, GST or Sales Taxes) |
Inventory Planning
To activate the automatic inventory planning you must globally switch on this functionality on the “Inputs” sheet. One can then enter an inventory target for each product. Target input is in % of net sales of the given product.
The resulting changes in inventory are calculated automatically for each product. The actual changes may differ from the target value aimed for, if, for example, no sales are planned for a month, the corresponding inventory cannot be reduced.
|
Example If you would like to have inventory for a special product for a quarter and your “cost-of-sales ratio” is 30%, you have to enter 90% (3 months x 30%) as inventory target. |
The corresponding balance sheet account (Inventory/Stock (manufacturing & merchandise)) for each product is on the “Costs 01” sheet. The initial values are the input values defined in the "Opening Balance" section on the “Inputs” sheet..
|
Tip To be even more flexible in inventory planning you are free to overwrite the formulas in the row “Inventory target” of each product (columns J to BU). You can either manually enter the required values or use a formula to calculate for instance inventory for the next x months based on your sales planning (see example and screenshot below). Assuming you receive (and pay for) materials only once per quarter, put in the figures in the month of purchase and set the inventory target for the following two months to zero. In this case, the model reduces the inventory in month 2 and 3 accordingly, and won't effect liquidity further during this period. You can implement numerous other possible combinations and applications. We recommend experimenting with this feature, using a new, almost empty planning file to get a better feeling for the impact on inventory and liquidity (=> keeping in mind creditor payment targets on the “Inputs” sheet). |
The following screenshot shows a simple example. Assumptions: Sales quantity: 50 desktop computers per month; Sales price: EUR 1,000 each (set on the "Sales" sheet). The assumed ratio of cost of materials to sales is 60%, i.e. EUR 30,000 per month. We have replaced the formulas in row 128 (inventory target => columns J to BU) by input cells (see applied cell style) and have set cell F128 to zero (cell style: empty cell).
Now we can fine-tune inventory planning, for example purchase of material/goods only once a quarter. In month 2 and 3 and 5 to 7 inventory is reduced accordingly (see balance sheet account at the bottom of the screenshot).
Click image to enlarge
The situation is also correctly reflected in the corresponding P&L and Cash Flow Statement (not shown here).