DOCUMENTATION

PURPOSE

The INDIRECT CALCULATOR is a supplementary tool specific to businesses and organizations that allocate general overhead (operating) costs across different projects, products or grants. These businesses either negotiate a set of indirect rates to be used when invoicing a client for revenue based on billable direct costs, or they may allocate a default blanket rate (i.e. de minimis rate) on eligible direct costs. This is especially common for government contractors, non-profit organizations and grant recipients.

An indirect rate is calculated as follows:

Indirect Rate = (Indirect Cost Pool ÷ Allocation Base) × 100

The Cost Pool represents the indirect operating expenses that are being allocated to an Allocation Base, which includes eligible billable direct costs that are relevant to recovering the Cost Pool. For example, a common Fringe Benefits Rate may have a Cost Pool of all fringe benefit expenses and an Allocation Base of all direct and indirect labor costs.

The INDIRECT CALCULATOR helps to first calculate or set the indirect rates, then calculate the actual indirect revenue amount to be added in the MASTER LEDGER (and applied to a client invoice) or estimate the indirect revenue amount to be added to a forecast. Whether a business or organization already has approved or estimated indirect rates to use, or will be calculating initial rates for the first time, the INDIRECT CALCULATOR will address instructions for both scenarios. While the Indirect Rate Setup tab can help estimate initial rates, many clients will require a comprehensive indirect cost rate schedule for approval dependent on the client’s reporting requirements. Due to the heavy compliance burden, please work with a specialized accounting firm or expert to help perform the required cost analysis and assist with the official indirect rate application process.

Back to Top

Step 1: Import the content from the MASTER LEDGER’S ‘Export Lookup to Nexus’ tab.

Copy and paste the content from the MASTER LEDGER’s Export Lookup to Nexus tab into the Import Lookup Table. The purpose is to bring in account names including updated names for any custom accounts in the MASTER LEDGER’s Chart of Accounts under operating expenses, direct costs and indirect revenue. If manually revising the Import Lookup Table without importing from the MASTER LEDGER, update the names under the required gold header columns (Operating Expense Accounts, Direct Cost Accounts, Indirect Revenue Accounts) but keep the account number as a 4-digit number for the lookup formulas with other tabs.

The custom revenue accounts Indirect Revenue 1 through Indirect Revenue 10 should be used to designate the active indirect cost recovery revenue accounts.

Next: Proceed to tab ‘2 – Indirect Rate Setup TXNS’.

 Back to Top

STEP 1. Import or input eligible indirect cost and direct cost transactions.

To calculate the indirect rates that will be used to determine indirect revenue, you can bring over eligible transactions from the MASTER LEDGER or from HORIZON depending on whether you intend to calculate indirect rates using historical actuals or forecast data. If using actual transactions, copy and paste the content from the Export Actuals tab in the MASTER LEDGER and paste values (Ctrl + Shift + V) into the Transactions Table. To calculate forecasted indirect rates, copy the eligible forecast data from HORIZON’s 3 – Export Forecast tab instead. The data can also be a combination of actuals and forecast, which can occur if re-estimating indirect rates in the middle of a fiscal year.

For simplicity, the INDIRECT CALCULATOR disregards date and determines one set of rates for all the imported transactions, therefore transactions should be limited to the same fiscal periods as the periods (usually year) for which the indirect rate is being calculated.

If inputting transactions manually instead of bringing data over from the MASTER LEDGER or from HORIZON, add manual content under the gold header columns Account # and Amount which are required to proceed with the indirect rate setup. Account # entries should be 4 digits, while Amount entries should all be numerical. The transactions’ Account # should correspond to the accounts in the gold header columns Operating Expense Accounts and Direct Cost Accounts in the tab 1 – Import Lookup.

Next: Proceed to tab ‘3 – Indirect Rate Setup’.

 Back to Top

Purpose

The purpose of the Indirect Rate Setup tab is to designate the indirect cost recovery revenue accounts, distribute the allowable direct cost accounts to the relevant allowable indirect rate allocation bases, and calculate or manually override each indirect rate. The setup in this tab prepares the indirect rates for use in the 6 – Indirect Calculator and 7 – Manual Calculator by Month tabs. Multiple steps and tables are combined in this tab, with blue links and gold arrow icons to help navigate for each setup step.

Step 1 and Step 3 are required for all users of the INDIRECT CALCULATOR. Step 4 is intended to help calculate indirect rates for small businesses or organizations that do not yet have estimates for indirect rates and are not using a default blanket (e.g. de minimis) rate. The manual rate override (Step 2) is intended for the businesses or organizations that already have their indirect rates determined through a highly complicated and intensive process of incurred cost submissions to the client involving extensive documentation and allocation of expenditures into cost pools and approved allocation bases. These businesses still need to set up their indirect cost recovery revenue accounts and direct cost allocation bases in Steps 1 and 3.

STEP 1. REQUIRED: Designate active indirect cost recovery revenue accounts.

Using the yellow dropdown cells, designate up to 10 indirect cost recovery revenue accounts from amongst the custom accounts in the Indirect Revenue Accounts column in 1 – Import Lookup. Leave blank any unused extra slots.

STEP 2. Required for Manual Rate Override: Manually set the percentage for each Indirect Rate.

To override any cost pool and allocation base calculations, manually input the percentages for the designated indirect rates relevant to each indirect cost recovery revenue account. If not overriding, leave blank (do not input 0%) and skip to STEP 3. Inputting a 0% rate also counts as a manual override.

STEP 3. REQUIRED: Designate allowable direct cost accounts to be part of each indirect rate allocable base.

(a) Use the yellow dropdown cells in the first column to designate which direct cost accounts are allowable as part of the allocable base for indirect rates. Select ‘Yes’ to include, or ‘ – ‘ to exclude. While all direct costs from the MASTER LEDGER’s Chart of Accounts have been included as options, not all direct costs may be allowable in the base for cost recovery. For example, ‘Fines and Penalties’ is suggested as a typically unallowable cost. Work with your financial advisor and contracts officer to abide by your local governing entity’s rules for what costs are considered allowable and which are unallowable.

(b) Use the yellow dropdown cells under each indirect rate column to designate which of the allowable direct cost accounts are part of the allocable base for the indirect rate in each column. When these allowable direct costs are invoiced to the client, they will help recover a portion of the indirect cost pool based on the indirect rates. Select ‘Yes’ to include, or ‘ – ‘ to exclude.

STEP 4. Required to Calculate: Allocate allowable indirect OPEX amounts to each active Indirect Rate.

Can skip this step if using Manual Rate Override from STEP 2.

(a) Use the yellow dropdown cells in the first column to designate which indirect operating expense (OPEX) accounts are allowable as part of the cost pool for the indirect rates. Select ‘Yes’ to include, or ‘ – ‘ to exclude. While all OPEX costs from the MASTER LEDGER’s Chart of Accounts have been included as options, not all OPEX costs may be allowable in the cost pool for cost recovery. For example, ‘Bad Debt Expense’, ‘Compensation – Stock-Based Compensation’, and the 8000-9000 series Other Expense and Income Tax accounts have been suggested as typically unallowable costs. Work with your financial advisor and contracts officer to abide by your local governing entity’s rules for what costs are considered allowable and which are unallowable.

(b) In the white cells with blue font, allocate the amounts in the column OPEX Amount Total for each allowable Cost Pool (OPEX) Account to each impacted Indirect Rate. The allocated amounts become the indirect cost pool of each Indirect Rate. The column Remaining Amount to Allocate tracks the amount left to distribute to one or multiple Indirect Rates. A red ‘OVERALLOCATED’ message appears if an OPEX account’s total has been overdistributed and needs to be reallocated.

The indirect rates (as percentages) can now calculate based on the indirect cost pool totals and allocation base totals using the following formula:

Indirect Rate = (Indirect Cost Pool ÷ Allocation Base) × 100

The Cost Pool represents the indirect operating expenses that are being allocated to an Allocation Base, which includes eligible billable direct costs that are relevant to recovering the Cost Pool. For example, a common Fringe Benefits Rate may have a Cost Pool of all fringe benefit expenses and an Allocation Base of all direct and indirect labor costs.

Next: Proceed to tab ‘4 – Review Rate Lookup’.

 Back to Top

Purpose

This view-only tab is intended for an overview of the final direct cost and indirect rate matrix. The table shows which indirect rates are applied to each allowable Direct Cost Account, as well as the Simple Multiplier applied on each unit amount of Direct Cost. If an Indirect Rate was manually overridden, the top of the column will show ‘Manual Rate Override’ in gold.

The Simple Multiplier is a quick way to calculate how much total revenue a unit of direct cost will generate, which includes recovery of the direct cost base amount as well as indirect costs. A $1000 allowable direct cost with a 1.5 Simple Multiplier will ultimately generate $1500 in revenue, of which $500 is for indirect cost recovery.

In this INDIRECT CALCULATOR model, the Simple Multiplier independently calculates each indirect rate % on the base eligible direct cost and provides a conservative (lesser) estimate of the amount of indirect revenue that can be recovered. In some more complicated organizations, certain indirect rates can also apply on top of the amount of indirect calculated from another indirect rate (a larger multiplier effect). For example, general administrative indirect rates may be applied also on top of the amount of fringe indirect revenue generated independently from a fringe indirect rate. Please work with your financial advisor and accountant to better understand the indirect rate nuances applicable to your company.

To begin calculating indirect revenue, proceed to tab 5 – Add TXNS For Indirect Calc.

 Back to Top

STEP 1. Import or input eligible direct cost transactions for indirect revenue calculation.

To prepare to calculate the indirect revenue to be applied to a set of direct costs, paste or input any eligible direct cost transactions into the Imported Transactions Table. Only the gold four digit Account # and Amount columns are required, though parameters such as Project ID and Invoice ID are needed if users would like to pivot by those parameters.

If importing actual transactions from the MASTER LEDGER, copy and paste the relevant transactions from the MASTER LEDGER’s Export Actuals tab. This could be a single transaction, all transactions related to  a single invoice or project, all transactions for a particular time period, etc. The Actuals pivot table in the following tab 6 – Indirect Calculator will allow users to view the calculated indirect revenue in combinations of months, Project ID and Invoice ID, etc.

If importing forecast transactions from HORIZON, copy and paste the relevant transactions from HORIZON’s 3 – Export Forecast tab. To view indirect revenue by month horizontally for forecast transactions in the format most friendly for copying results back into HORIZON, only add transactions relevant to one Project ID or Product ID before reviewing the results in the Forecast Table on 6 – Calculate Indirect Revenue.

Next: Refresh All and then proceed to tab ‘6 – Calculate Indirect Revenue’.

Navigate to the Data tab on the top Excel ribbon, then under Queries & Connections group, select Refresh All. This will refresh the pivot tables with the new data. After the Refresh All, proceed to tab 6 – Calculate Indirect Revenue.

 Back to Top

Purpose 

This tab shows the resulting indirect revenue amounts based on the direct costs added to the previous tab 5 – Add TXNS for Rev Calc. Two different views are provided, with the first pivot table (Actuals) more appropriate for copy and pasting into the Indirect Revenue accounting module of the MASTER LEDGER. The second table (Forecast) is more appropriate for viewing indirect rate recovery revenue by month, making it easier to copy and paste into HORIZON.

Actuals Table

Better suited for reviewing indirect rate recovery revenue on actual transactions, the Actuals Table pivot table defaults to grouping results by Project ID and Invoice ID, two of the most common parameters when adding indirect revenue to the Indirect Revenue accounting module of the MASTER LEDGER. Use the Project ID and Invoice ID slicers to filter the pivot table if needed, and select the month or months of data for viewing the calculated results.

Once satisfied with the results showing in the Actuals Table pivot, copy the amounts for the specific row(s) or subtotal row(s) to the Indirect Revenue accounting module of the MASTER LEDGER. Best Practice is to include a single month’s calculated results for each unique Project ID and Invoice ID when adding a new transaction to the Indirect Revenue accounting module.

Add additional pivot table fields by opening the PivotTable Analyze tab on the Excel top ribbon and opening Field List under Show, then adding more PivotTable Fields to Rows.

Forecast Table

Start by inputting the Forecast Start Date in the gold cell to set the output date range of the Forecast Table, which starts from the Forecast Start Date and extends to a total of 60 months. Due to the fixed 60-month date setup, the Forecast Table works best when only forecast transactions relevant to one Project ID or Product ID are included in 5 – Add TXNS for Rev Calc.

The calculated amounts for each active Indirect Revenue account are summarized in the Forecast Table by month. Once satisfied with the results shown in the Forecast Table, copy the amounts for each active Indirect Revenue account row to a matching HORIZON forecasting model revenue row.

The bottom Cost Aggregator Table is for reference only and aggregates the amounts for each direct cost account by month to facilitate calculation.

 Back to Top

Purpose 

The Manual Calculator provides flexibility for users to perform quick estimates or calculations on cost data entered in a structure similar to the horizontal monthly structure in the HORIZON forecast models. An additional Sensitivity Factor column allows quick scenario toggling of baseline monthly costs, such as quickly increasing or decreasing a cost row’s manual amounts by 30% across the entire row.

STEP 1. Input Start Date.

Start by inputting the Start Date in the gold cell to set the date range of the Summary Table by Month and Direct Cost Amount Table. The date range begins from the Start Date and extends to a total of 60 months.

STEP 2. Add amounts by month to the Direct Cost Amount Table and modify Sensitivity Factor as needed.

Input amounts for each direct cost account. Users can add amounts by month, or ignore the dates and use each month column as an independent calculation column.

Toggle the Sensitivity Factor for each row as needed. Sensitivity Factor defaults to ‘100%’ and is an added method to model percentage increases or decreases to variables or amounts across an entire row, without needing to set formulas to the cells or overwriting the values for the entire row. The default ‘100%’ equates to keeping the row’s inputs as is. To ignore the sensitivity factor option, keep the Sensitivity Factor column as ‘100%’ (no change). To utilize the sensitivity factor, input any percentage from 0% (removing an amount entirely) or higher on relevant rows as needed. For example, to reduce the row’s input by 25%, input a sensitivity factor of 75% (showing -25% in the % Change column); to increase the row’s inputs by 50%, input a sensitivity factor of 150% (showing +50% in the % Change column). 0% or blank would mean removing the row’s values entirely, as the row’s monthly amounts are multiplied by the sensitivity factor.

STEP 3. View the results in the Summary Table by Month.

The calculated amounts for each active Indirect Revenue account are summarized in the Summary Table by Month.

 Back to Top