Standard Costing Templates

Standard costing calculation with Excel – Employee/Materials/Equipment databases will update standard cost calculations. Calculates price levels for any product of service necessary to reach target profit margins. Benchmark G&A expense ratios – STDEV & Confidence levels – Budgeting overhead reductions.

Overview

List of spreadsheets

Standard Costing 5.0 makes it easy to gather all operating costs together and assign them to production or services produced, apply recovery of overheads including depreciation to each product/service and calculate prices. Includes targeting overhead ratios for cost recovery programs using Excel.

Costing Basis

The Asset (equipment) database calculates depreciation/book values and the depreciation recovery rate per hour for each asset in use.

The Materials database calculates material unit costs including the financing cost of storage.

The Employee database holds the remuneration records for all employees by department. Calculates the full hourly paid rate per employee.

In the Standard Costing calculator spreadsheet you can set up a standard costing budget for any product or service based on:

• Budget direct employee hours required to produce or deliver a unit of output/service, the number hours worked and total employment costs.
• Budget indirect employee hours required to support (indirectly) the production or delivery of a unit of output/service, the number indirect hours worked and total employment costs.
• Raw materials used to produce or deliver the unit of output, the current cost rates and total material costs.
• The equipment hours used to produce or deliver a unit of output/service, the hourly depreciation rate and the full allocated cost of depreciation.
• Allocation of overhead costs based on either: employee hours worked, or equipment (machine) hours required, or sales value as percentage of total sales value output. The user selects which overhead allocation method is applied to their standard costing calculations.

Screens

Several screens show the trace precedents links within spreadsheet. These are of course not visible in the program unless you turn the auditing toolbar on and select a cell.

Several screens show stars above columns, which denotes that the user is required to enter information into the columns indicated.

Creating Employee expense centers is required to be able to allocate employee costs to departments in the Employee Database.

Creating Asset allocation expense centers is required to be able to allocate equipment depreciation recovery to production outputs for standard costing calculations.

Expense Centers.

The Equipment database provides an asset register of all equipment and machinery in use and calculates monthly depreciation, total depreciation, current book value and the average hourly depreciation cost recovery rate.

Arrows trace precedent cells.

Creating the Materials database is required to allocate individual materials to standard costing budgets for individual products in the costing calculator spreadsheet.

The Materials list spreadsheet includes financing costs to store materials. If you do not wish to include these costs in calculations of standard costs, enter zero percent in the Cost of finance.

Arrows trace precedent cells.

The Employee list makes it easy to calculate full employee costs, including paid vacations, social security, training, recruitment expenses, workers compensation, available work hours per annum leading to average hourly costs.

Scroll right >>.

Excel SumIf totals employee numbers and expenses by departmental cost centers.

Arrows trace precedent cells.

If we have kept your interest thus far, we have shown you how to create cost information databases in Excel (employees, materials, depreciation) that will automate and simplify your calculations of standard costs for any product of service based on:

• Budgeted direct hours worked.
• Budgeted Indirect support hours support.
• Budgeted material costs (including inventory costs if you wish).
• Budgeted utilization of fixed asset costs/depreciation costs.
• Budgeted overhead/fixed cost allocation.

You can choose from three options to calculate the overhead allocation rate:

• Per employee hour worked.
• Per equipment hours worked.
• Per $1 sales output value.

Calculate standard costs by entering the volume (batch size) used to budget the number of direct and indirect hours worked, the budgeted value of materials consumed, and the number of equipment hours into D3. Enter the price into D4.

To adjust prices to achieve the target profit margin, change the price level in D4 until the margin percentage in D8 is the value you want.

You can copy this spreadsheet as many times as you wish (keep copies within the program to access the databases) to retain standard costing calculations for multiple products. When employee costs and material costs change, all your saved standard costing calculations update automatically.

Enter the budgeted direct and indirect employees that you expect to work to produce the product or service, and the number of hours work required, or use an average and enter the total number of employee hours required.

Arrows trace the calculation references for standard costing calculations.

Scroll right >>

Budget individual materials required and quantities. Budget the utilization (hours) of individual equipment/facilities required.

In the Standard Costing Calc page the user defines precisely the actual employees and the actual materials and the actual equipment/facilities employed per batch unit of production or service provided for accurate product costing.

In Quick Standard Costing spreadsheet the program uses overall average costs of direct and indirect employees per hour, the average depreciation cost recovery rate so that you can quickly calculate many standard costs using a percentage rate for the direct material cost of sales.

Scroll right for the standard cost values.

This next spreadsheet uses product/service unit standard costs calculated in the spreadsheet above to calculate target prices required to reach target profit margin percentages.

Reducing Fixed Costs

You can calculate the current range limits of General & Administrative cost burdens your firm needs to maintain to remain competitive by analyzing best practice competitor financial data obtained from security filings or D&B data.

Analyze competitor's financial data with this spreadsheet to produce upper and lower range limits of G&A expense to sales ratios to a 95% confidence level. This provides you with the target cost range to budget overhead expenses.

The demo financial data values are taken from actual 2004 SEC filings of selected US food manufacturers. Our demo firm is AnyCo Technologies.

Budget G&A expenses to meet target benchmark ratio (E5). Enter your own descriptions of fixed cost allocation centers into row 12. Allocate overheads to each cost center that sum to the target G&A rate required in E5.

This also provides a method for uncovering unnecessary overhead costs which are unallocated to any cost heading.

Showing dependants tracing.