Costing Template

							Costing Templates
A description of the form and content used
Version 1.0.

Prepared by Adrian Burchall Project Accountant, Finance and Business Services September 2002

Table of Contents

1.

Introduction................................................................. 3
Wage and Salary On-Costs ............................................... 4 GST ................................................................................... 6 Indirect Costs..................................................................... 6 Multiple Periods ................................................................. 8 Controls ............................................................................. 8 Simple Grant Template...................................................... 9 Simple Consultancy Template ......................................... 10 Simple Course Template ................................................. 12 Detailed Grant Template.................................................. 14 Detailed Consultancy Template....................................... 16 Detailed Course Template ............................................... 17 Hosting of Conferences Template ................................... 22 Manufacturing Projects Template .................................... 25

1.1. 1.2. 1.3. 1.4. 1.5.

2.

The Worksheets ......................................................... 9

2.1. 2.2. 2.3. 2.4. 2.5. 2.6. 2.7. 2.8.

Appendix – An explanation of the text aggregation formula used in the consolidation worksheets ............................. 29

1. Introduction
A series of costing templates have been developed to assist finance officers with the budgeting and costing of special projects, principally managed within the S and Q (research and consultancy) ledger segments. However, the templates should also be suitable for project pricing, costing or budgeting within other ledger segments. Please note that if the worksheets are to be used for University Business Units, modifications to the worksheets may be required to account for non-operating costs. A series of worksheets and workbooks have been created as follows: ♦ Simple Grant Template ♦ Simple Consultancy Template ♦ Simple Course Template ♦ Detailed Grant Template ♦ Detailed Consultancy Template ♦ Detailed Course Template ♦ Hosting of Conferences Seminars Workshops Template ♦ Manufacturing Projects Template The simple templates are single worksheets that are designed for costing or budgeting at a high level (by Budget Class) without using account codes. The detailed templates not only provide for the use of account codes but also contain five separate worksheets together with a (linked) consolidation worksheet. This allows for the aggregation of financial data for separate projects within a program, for example. The detailed template workbooks also contain summary worksheets (with costs summarised by budget class) that may prove useful for attaching to funding applications or proposals. A schematic of the detailed templates follows:
Detailed worksheet 1 Detailed worksheet 2 Detailed worksheet 3 Detailed worksheet 4 Detailed worksheet 5 Summary of worksheet 1 Summary of worksheet 2 Summary of worksheet 3 Summary of worksheet 4 Summary of worksheet 5

Consolidated detailed worksheet

Consolidated summary worksheet

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 3 of 30

The simple templates contain a section at the bottom of the worksheet that allows for the entry of salaries and wages details. This facility is optional, and if the user wishes, they may enter salaries and wages data at the summary level directly in the main part of the worksheet. An extract showing itemised salaries and wages from the bottom of one of the simple templates is shown below (without the year headings):

The detailed worksheets are extensive and somewhat complex. They should really only be utilised by users who have a reasonable knowledge of both accounting principles and Microsoft Excel. If users wish, it is possible to enter a more detailed description of revenues or costs into the simple worksheets by inserting additional rows under the budget class heading. For example, a user may wish to show a breakdown of costs within the Expendible Research Materials budget class. This should not cause a problem so long as users ensure that sub-total formulas capture the additional rows inserted. A totalling formula will also need to be inserted in the total column. Formula calculations in respect of indirect costs and GST make use of sub-totals and not individual rows. An example of some inserted rows in a simple template follows:

Inserted rows

Additional totalling formulas inserted

1.1.

Wage and Salary On-Costs

The detailed salaries and wages section in each template includes formula references to a salaries and wages on-costs section for their automatic calculation. This section (apart from the Hosting of Conferences Seminars and Workshops Template, which uses a simpler version) is reproduced on the following page.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 4 of 30

These percentages can be changed when tax or employer contribution rates change.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 5 of 30

1.2.

GST

The worksheets also contain checkboxes to elect whether income/revenues/sales are subject to GST. If GST is receivable and the check box is ticked the total billable amount is calculated automatically for reference. This feature has been incorporated to ensure that users are aware of net and gross amounts receivable and to help ensure that net amounts receivable cover all associated costs. GST calculations are not made in respect of costs. An example of the GST election box is shown below (without the year headings - from the Simple Consultancy Template):

1.3.

Indirect Costs

The application of University and Department indirect costs (overheads) varies depending on the type of project that is being undertaken. Many grant providers stipulate that their funding does not provide for coverage of indirect costs and that funding is to cover direct costs only. Where applicable, the Department indirect cost percentage has been set at 17%. Where applicable, the General University indirect cost percentage has been set at 8%. A memorandum sent to Dean and Directors by the Deputy Vice Chancellor (Research), dated 19 July 2002 provides guidance on the application of indirect costs. A copy of the memo is provided on the following page.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 6 of 30

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 7 of 30

The templates have been constructed with the following indirect cost applications: Indirect Costs do not apply Indirect Costs do apply Application of indirect costs is optional or depends on the terms of the funding arrangement Simple Course Template Detailed Course Template Hosting of Conferences Template Manufacturing Projects Template As stated in the memorandum on the previous page, guidance on the application of indirect costs can be obtained by referring to Section 1.6 ‘Costing and Pricing’, in the document ‘Policy on Applying for and Accepting Research Grants, Contracts and Consultancies’ on the Research Services Office web page at (http://www.anu.edu.au/rso/policy/680_2000.pdf). Examples of the application of indirect costs are shown in examples of the templates provided in the rest of this document.

Simple Grant Template Detailed Grant Template

Simple Consultancy Template Detailed Consultancy Template

1.4.

Multiple Periods

Apart from the Hosting of Conferences Template each worksheet caters for the optional inclusion of up to five years of income/revenues/sales and associated costs. This is to cater for projects that last longer than a single year. An optional use for the multiple columns might be to compare various scenarios within a single worksheet. For example, if a project lasted for no more than two years, it would be possible to compare revenues and costs for two scenarios side by side (e.g. using columns 1 & 2 for scenario 1 and columns 3 & 4 for scenario 2). Different scenarios might include possible variances relating to pricing or volumes or costs.

1.5.

Controls

In some cases, the worksheets rely on control boxes to check whether GST or indirect costs (overheads) apply. If a check box is ticked, a ‘true’ statement is generated at the very bottom of the worksheet. A formula then reads whether the statement is true (check box ticked) or false (check box unticked) and performs a calculation if necessary.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 8 of 30

2. The Worksheets
A description of each of the worksheets is provided as follows. Each of the worksheet extracts contains fictitious revenue and cost data.

2.1.

Simple Grant Template

The Simple Grant template is designed for use with small or simple grant applications with income and costs being recorded at a summary level. A single line is provided for income, although if the user so chooses it is possible to insert additional income lines and adjust the total income formula. The option exists within this worksheet to enter more detailed salary and wages costs at the bottom of the worksheet or to simply enter estimated academic, non-academic and salary & wages on-costs in the summary rows provided. Summary lines exist (using the ESP Budget Control categories) for non-salaries and wages. As mentioned above, it is possible to insert additional rows. The Simple Grant Template does not cater for the inclusion of indirect costs (overheads), as these are usually not permitted under grant applications. The Simple Consultancy Template should be used instead where the inclusion of indirect costs (overheads) applies. The Simple Grant Template provides for the calculation of GST if it applies to the receipt of grant income. The main part of the Simple Grant Template is reproduced on the following page.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 9 of 30

2.2.

Simple Consultancy Template

The Simple Consultancy template is designed for use with small or simple Consultancy projects with revenue and costs being recorded at a summary level. A single line is provided for revenue, although if the user so chooses it is possible to insert additional revenue lines and adjust the total revenue formula. The option exists within this worksheet to enter more detailed salary and wages costs at the bottom of the worksheet or to simply enter estimated academic, non-academic and salary & wages on-costs in the summary rows provided. Summary lines exist (using the ESP Budget Control categories) for non-salaries and wages. As mentioned above, it is possible to insert additional rows.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 10 of 30

The Simple Consultancy Template automatically calculates indirect costs (overheads), as these are usually applicable. If an exemption is granted or a reduced indirect cost rate negotiated with the University Executive then either a zero or another amount can be typed into the cells containing the formulas. The Simple Consultancy Template provides for the calculation of GST if it applies to the receipt of consulting income (this would usually be the case). The main part of the Simple Consultancy Template is shown below:

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 11 of 30

2.3.

Simple Course Template

The Simple Course template is designed for use with short courses or less complex course programs with income and costs being recorded at a summary level. Five income lines are provided if required, although it possible to delete all but one income line and adjust the total course income line. The option exists within this Template to enter more detailed salary and wages costs at the bottom of the worksheet or to simply enter estimated academic, non-academic and salary & wages on-costs in the summary rows provided. Summary lines exist (using the ESP Budget Control categories) for non-salaries and wages. As mentioned above, it is possible to insert additional rows. The Simple Course Template caters for the voluntary inclusion of indirect costs (overheads) if it is considered that these are applicable. The Simple Course Template provides for the calculation of GST if it applies to the receipt of course income. The GST section of this Template provides a link to the Finance & Business Services web pages for advice on the application of GST to the provision of courses. An ATO tax ruling on the applicability of GST to education courses is also imbedded in the worksheet (in a Word document format) and is simply accessed by double-clicking on the following symbol in the worksheet:

GSTR 2001-1.rtf

A box in the GST section prompts users to tick the GST checkbox against an income line (at the top of the worksheet) if GST is applicable to course income. The main part of the Simple Course Template is reproduced on the following page.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 12 of 30

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 13 of 30

2.4.

Detailed Grant Template

The Detailed Grant Template is designed for use with larger and more complex grant applications with income and costs being recorded at a detailed level. Six lines are provided to cater for a variety of income sources as shown below:

A detailed salary and wages section is incorporated in the main part of the worksheet, rather than being optional at the bottom of the worksheet as used with the Simple Grant Template, although the formats are the same. Salaries & wages oncosts are calculated automatically by using a reference formula. An extract from the salaries section of the Detailed Grant Template is shown below:

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 14 of 30

Detailed account lines are also used in respect of non-salaries costs. An extract from the non-salaries section of the Detailed Grant Template is shown below:

The Detailed Grant Template does not cater for the incorporation of indirect costs (overheads), as these are usually not permitted under grant applications. The Detailed Consultancy Template should be used instead where the inclusion of indirect costs (overheads) applies. The Detailed Grant Template includes a section for the calculation of GST if it applies to the receipt of grant income, as shown below:

The Detailed Grant Template includes five identical detailed worksheets that (automatically) consolidate into a single (linked) worksheet. A worksheet such as this may prove useful where there are several related projects being undertaken within the one program and where it is necessary to identify both individual project and consolidated program income and costs. The workbook also contains summary worksheets for each of the detailed worksheets (including the consolidation worksheet) for attaching, for example, to funding submissions.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 15 of 30

2.5.

Detailed Consultancy Template

The Detailed Consultancy Template is designed for use with larger and more complex consulting projects with revenue and costs being recorded at a detailed level. Six lines are provided to cater for a variety of revenue sources. The revenue part of the Detailed Consultancy Template is reproduced below:

A detailed salary and wages section is incorporated in the main part of the worksheet, rather than being optional at the bottom of the worksheet (as with the Simple Consultancy Template, although the formats are the same). Salaries & wages on-costs are calculated automatically by using a reference formula. Detailed account lines are also used in respect of non-salaries costs (as shown in the description of the Detailed Grant Template above). The Detailed Consultancy Template includes the mandatory calculation of indirect costs (overheads) as determined by the Memorandum sent to Dean and Directors by the Deputy Vice Chancellor (Research), dated 19 July 2002 (reproduced above). If a waiver or reduced rate is authorised by the DVC (Research), a zero or another figure can be inserted in place of the formulas in the indirect costs section. The Indirect Costs section of the Template is reproduced below:

The Detailed Consultancy Template allows for the calculation of GST if it applies to the receipt of consulting revenue (as would usually be the case). The Detailed Consultancy Template includes five identical detailed worksheets that (automatically) consolidate into a single (linked) worksheet. A worksheet such as this may prove useful where there are several related consultancies being undertaken

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 16 of 30

within the one major consultancy and where it is necessary to identify both individual consulting and a consolidated program of consultancy revenues and costs. The workbook also contains summary worksheets for each of the detailed worksheets (including the consolidation worksheet) for attaching, for example, to a proposal.

2.6.

Detailed Course Template

The Detailed Course Template is designed for use with larger and more complex course structures with income and costs being recorded at a detailed level. The Template allows for income to be recorded for five different courses. An extract from the income section of the Template is reproduced below:

A detailed salary and wages section is incorporated in the main part of the worksheet, rather than being optional at the bottom of the worksheet (as with the Simple Course Template, although the formats are the same). Salaries & wages oncosts are calculated automatically by using a reference formula. Detailed account lines are also used in respect of non-salaries costs. The Detailed Course Template allows users to nominate costs as either fixed or variable by typing in ‘F’ or ‘V’ against each cost line. Recording costs in this way will prompt users to think about which costs will change as a result of changes in student enrolments. A contribution margin is calculated to determine the extent to which income covers fixed costs. For the fixed and variable cost categorisation feature to be effective it is necessary for users to make a nomination against each direct cost line. An extract is shown on the following page.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 17 of 30

A section at the bottom of the worksheet automatically aggregates cost categories (by using the Excel DSUM function) and calculates a contribution margin as follows:

The contribution margin is calculated by deducting direct variable costs from income. The result shows the amount remaining to cover fixed costs. If fixed costs amount to less than the contribution margin a profit will be made. If fixed costs amount to more than the contribution margin a loss will be made. The breakeven point is where the contribution margin equals fixed costs. The Detailed Course Template includes a section for the optional calculation of indirect costs (overheads), as these may not apply in all circumstances. For guidance, please refer to Section 1.6 ‘Costing and Pricing’ in the document ‘Policy on Applying for and Accepting Research Grants, Contracts and Consultancies’ on the Research Services Office web page at (http://www.anu.edu.au/rso/policy/680_2000.pdf). The memorandum sent to Dean and Directors by the Deputy Vice Chancellor (Research), dated 19 July 2002 (as reproduced above) also provides guidance. If a waiver or reduced rate is authorised

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 18 of 30

by the DVC (Research), a zero or another figure can be inserted in place of the formulas in the indirect costs section. An extract of the section follows:

As with the Simple Course Template, the Detailed Course Template allows for the calculation of GST if it applies to the receipt of course income. The GST section of the template prompts users to tick the check boxes against each of the income lines (at the top of the worksheet) if GST is applicable. The Template also provides a link to the Finance & Business Services web pages for advice on the application of GST to the provision of courses. An ATO tax ruling on the applicability of GST to education courses is imbedded in the worksheet (in a Word format) and is simply accessed by double-clicking on the following symbol in the GST section of the worksheet:

GSTR 2001-1.rtf

An extract of the GST section of this worksheet is reproduced below:

The Detailed Course Template includes a section at the bottom of the worksheet that allows some break-even analysis to be undertaken. Users can nominate projected

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 19 of 30

course fees or projected enrolments and the work sheet will calculate course fees or enrolments required to break-even. If more than one course income line is used this function will only calculate the total income for a range of courses or average course fee across a range of courses to break-even. This is because costs are not attributed to individual courses. If users wish to calculate break-even points for an individual course, it is possible to use a single income line per worksheet and then allocate costs to an individual course. The consolidated worksheet can then be used to determine income, costs and breakeven points for a range of courses. An extract follows where enrolments have been nominated and the average course fee required to break-even automatically calculated:

An extract is shown on the following page where course fees have been nominated and the enrolments required to break-even automatically calculated:

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 20 of 30

The Detailed Course Template includes five identical detailed worksheets that (automatically) consolidate into a single (linked) worksheet. A worksheet such as this may prove useful where there are several related courses offered within the one major program and where it is necessary to identify both individual course and consolidated program income and costs. The workbook also contains summary worksheets for each of the detailed worksheets (including the consolidation worksheet) for attaching, for example, to a program proposal.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 21 of 30

2.7.

Hosting of Conferences Template

The Hosting of Conferences Template is designed to cater for the budgeting or pricing and costing of hosting a conference or workshop etc. The Income section of the worksheet provides for up to six different sources of sponsorship income as well as six types of registration income. Users can nominate projected numbers of registrants together with projected registration fees and compare these with projected costs to determine a projected profit or loss. An extract from the income part of the worksheet is reproduced below.

Directs costs are summarised into Direct Personnel, Catering and Other. Users can also nominate projected numbers and unit costs for catering to do some ‘what-if’ analysis. An extract from the direct costs section is reproduced on the following page.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 22 of 30

The Hosting of Conferences Seminars Workshops Template includes a section for the optional calculation of indirect (overhead costs), as these may not apply in all circumstances. For guidance, please refer to Section 1.6 ‘Costing and Pricing’ in the document ‘Policy on Applying for and Accepting Research Grants, Contracts and Consultancies’ on the Research Services Office web page at (http://www.anu.edu.au/rso/policy/680_2000.pdf). The memorandum sent to Dean and Directors by the Deputy Vice Chancellor (Research), dated 19 July 2002 (as reproduced above) also provides guidance. Users are asked to tick the applicable checkboxes as shown on the following page.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 23 of 30

A box in the GST section of the template prompts users to tick the checkboxes against each of the sponsorship income lines if GST is applicable. The applicability of GST in relation to sponsorship income depends on whether the sponsor will obtain a reciprocal benefit. If, for example, a sponsor displays a corporate logo they are obtaining a reciprocal benefit and GST will be payable in relation to sponsorship income. If GST applies in respect of registration income users are also prompted to tick the checkboxes against each of the registration income lines at the top of the worksheet. Where some of the income is not subject to GST users are asked to provide a reason in the space provided. An extract of the GST section of the Hosting of Conferences Seminars Workshops Template is reproduced below:

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 24 of 30

The Hosting of Conferences Seminars Workshops Template includes five identical detailed worksheets that (automatically) consolidate into a single (linked) worksheet. A worksheet such as this may prove useful where there are several related workshops offered, for example, within the one series, and where it is necessary to identify both individual workshop and consolidated program income and costs.

2.8.

Manufacturing Projects Template

The Manufacturing Projects Template is designed for use with complex manufacturing projects with sales and direct costs being recorded at a detailed level. The Template allows sales to be recorded for five different product lines within a project. An extract from the sales section of this Template is reproduced below:

A detailed salary and wages section is incorporated in the main part of the worksheet, (as with the Detailed Grant and Consultancy Templates). Salaries & wages on-costs are calculated automatically by using a reference formula. Detailed account lines are also used in respect of non-salaries costs. The Manufacturing Projects Template allows users to nominate costs as either fixed or variable by typing in ‘F’ or ‘V’ against each cost line. For this feature to be effective it is necessary for users to make a nomination against each cost line. An extract is shown on the following page.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 25 of 30

A section at the bottom of the work sheet automatically aggregates cost categories (by using the Excel DSUM function) and calculates a contribution margin as follows:

The contribution margin is calculated by deducting direct variable costs from sales. The result shows the amount remaining to cover fixed costs. If fixed costs amount to less than the contribution margin a profit will be made. If fixed costs amount to more than the contribution margin a loss will be made. The breakeven point is where the contribution margin equals fixed costs. The Manufacturing Projects Template includes a section for the optional calculation of indirect costs (overheads), as these may not apply in all circumstances. For guidance, please refer to Section 1.6 ‘Costing and Pricing’ in the document ‘Policy on Applying for and Accepting Research Grants, Contracts and Consultancies’ on the Research Services Office web page at (http://www.anu.edu.au/rso/policy/680_2000.pdf). The memorandum sent to Dean and Directors by the Deputy Vice Chancellor (Research), dated 19 July 2002 (as reproduced above) also provides guidance. If a waiver or reduced rate is authorised by the DVC (Research), a zero or another figure can be inserted in place of the formulas in the indirect costs section. An extract is shown on the following page.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 26 of 30

The GST section of the template prompts users to tick the check boxes against each of the sales lines (at the top of the worksheet) if GST is applicable. An extract follows:

The Manufacturing Projects Template includes a section allowing users to conduct some break-even analysis. Users can nominate projected prices or projected numbers of units sold and the work sheet will calculate prices or volumes required to break-even. If more than one sales line is used this function will only calculate the total volume for a range of products or an average price across a range of products to break-even. This is because costs are not attributed to individual products. If users wish to calculate break-even points for an individual product, it is possible to use a single sales line per worksheet and then allocate costs to an individual product. The consolidated worksheet can then be used to determine sales, costs and break-even points for a range of products. An extract is shown on the following page where volumes have been nominated and the prices required to break-even automatically calculated:

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 27 of 30

In the following example prices have been nominated and the volumes required to break-even automatically calculated:

The Manufacturing Projects Template includes five identical detailed worksheets that (automatically) consolidate into a single (linked) worksheet. A worksheet such as this may prove useful where there are several related projects within a program and where it is necessary to identify both individual project and consolidated program sales and costs.

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 28 of 30

Appendix – An explanation of the text aggregation formula used in the consolidation worksheets
Within the detailed templates the detailed consolidation worksheet contains a complex nested ‘IF’ statement to aggregate the text entries contained in the detailed worksheets. For example, if worksheets 1 to 5 each have DEST as the fundor in the first income line, the consolidated worksheet will return DEST in the first income line. Alternatively, if worksheet 1 has DEST as the fundor in the first income line but worksheet 2 has the Commonwealth Department of Foreign Affairs as the fundor in the first income line, the consolidated worksheet will return ‘Various’ in the first income line because funding comes from separate sources. The formula used in the Detailed Grant Template is reproduced below: =IF('Detailed_Project 2'!C6:H6="",'Detailed_Project 1'!C6:H6,IF('Detailed_Project 2'!C6:H6<>'Detailed_Project 1'!C6:H6,"Various",IF('Detailed_Project 3'!C6:H6="",'Detailed_Project 2'!C6:H6,IF('Detailed_Project 3'!C6:H6<>'Detailed_Project 2'!C6:H6,"Various",IF('Detailed_Project 4'!C6:H6="",'Detailed_Project 3'!C6:H6,IF('Detailed_Project 4'!C6:H6<>'Detailed_Project 3'!C6:H6,"Various",IF('Detailed_Project 5'!C6:H6="",'Detailed_Project 4'!C6:H6,IF('Detailed_Project 5'!C6:H6<>'Detailed_Project 4'!C6:H6,"Various",'Detailed_Project 4'!C6:H6)))))))) It is important to remember that the ‘IF’ statement allows a maximum of 7 levels of nesting. An example follows from the Detailed Grant Template. Income line1 fundors from worksheets 1 and 2 are the same (and worksheets 3 to 5 are not used):

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 29 of 30

Income line1 fundors from worksheets 1 and 2 are different (and worksheets 3 to 5 are not used):

R:\WWW\Costing Templates\Templates_Documentation_V1.0.doc 30 of 30