Page tree
Skip to end of metadata
Go to start of metadata

Title: ICE (Incurred Cost Electronically) Model

Brief description:

The Incurred Cost Electronically (ICE), also known as Incurred Cost Submission, or ICS, is a highly formatted spreadsheet model with many worksheets (17 required and 6 optional) that provides a standard electronic package to assist in preparing adequate incurred cost submissions.  The ICE spreadsheet model also assists in audit support.

Unanet customers are able to complete the ICE spreadsheet model from reports such as the GL Summary (Trial Balance) Report, Project Summary by Cost Element, and an Ad Hoc report.

In the process outlined below, you can, at any time, reference an example ICE model called "ICE_MyCo.xls", located in the Additional Information at the bottom of this page. 

What’s covered in this document:

What's the difference between the Forward Rate Pricing Agreement and the Incurred Cost Submission?

The Forward Pricing Rate Agreement (FPRA) https://www.acquisition.gov/far/html/Subpart%2042_17.html is an agreement by which indirect rates are established for a finite period of time. 

The Incurred Cost Submission is based on the Incurred Cost Proposal and Final Rates.  Rates are calculated on the fiscal year which has just ended.

http://www.dcaa.mil/checklist_and_tools.html
http://www.dcaa.mil/DCAAM_7641.90.pdf 

Outline of Schedules

ScheduleTitleDescriptionRequired?
ASummary of all Claimed Indirect RatesSchedule A is a summary of the contractor’s indirect rates.Generally, no data entry is required on this schedule. All data is read from other schedules
 BGeneral and Administrative (G&A) Expenses (Final Indirect Cost Pool)Schedule B contains the details of the contractor’s G&A expenses. Yes.
C(x)Schedules C(1) thru Schedule C(6) - Overhead Expenses (Final Indirect Cost Pool)Schedule C contains the details of the contractor’s final overhead pool expenses if applicableYes, if applicable. Enter Overhead Pool Costs on these schedules. ICE will support up to 5 overheads using Direct Labor as base and 1 overhead using material as base.
D(x)Schedules D(1) thru Schedule D(6) - Intermediate Indirect Cost Pool (ex. Occupancy)Schedule D(x) contains the details of the contractor’s intermediate pool expenses if applicable.  Yes, if applicable. Enter Intermediate Pool Costs on these schedules. ICE will support up to 6 intermediate pools.
FringeFringe Benefits Expenses (Final Indirect Cost Pool)The Fringe schedule contains the details of the contractor’s fringe expenses if applicable.  Yes, if there are fringe costs not allocated to other indirect pool. Enter Fringe Pool costs and allocation bases on this schedule. 
 EClaimed Allocation Bases - by Element of Cost, used to distribute Indirect CostsSchedule E is the overhead and G&A base summary by cost elements.Generally no data entry is required on this schedule. All data is fed from other schedules.
 FFacilities Capital Cost of Money Factors ComputationSchedule F is a replication of the CASB Form CMF for calculating Facilities Capital Cost of Money (COM).No. Data on this schedule is generally read from Sched F-1 however there is input required regarding  interest rates.
F-1Computation of Average Net Book Values for COMEnter asset value detail to be used in the computation of Cost of MoneyNo. The ICE model can accommodate up to seven cost of money rates.  
 GReconciliation of Books of Account and Claimed Direct CostsSchedule G is the reconciliation of direct costs to the General Ledger/Trial Balance.Yes.
G-1Reconciliation of Claimed Direct Costs to JCLSchedule G-1 is the reconciliation of direct costs from the General Ledger to the Job-Cost-Ledger and is linked to Schedule G.No.
Summary Schedule HSummary Sched H - Direct Contract/Subcontract Costs/IR&D/B&PSummary Schedule H summarizes contract direct costs and IR&D/B&P costs.Yes.
HSchedule of Direct Costs by Contract/Subcontract including, Direct IR&D/B&P Costs and Claimed Rates AppliedSchedule H is the schedule of claimed/not claimed direct costs by contract/subcontract including direct IR&D/B&P and Overhead, G&A, and COM at the claimed rates.  Yes.
H-1Subsidiary Schedule of Government Participation PercentagesSchedule H-1 presents a general overview of the extent that cost type and flexibly priced contracts participate in the absorption of indirect expenses.  Yes.
 ISchedule of Cumulative Direct and Indirect Costs Claimed and Billed by Contract and SubcontractSchedule I is a schedule of cumulative direct and indirect costs claimed and billed from inception to date. Yes.
 JSubcontractor InformationSchedule J provides subcontractor information.Yes, if company has incurred costs for subcontractors.
 KSummary of Hours and Amounts on Time and Material (T&M)/Labor Hour ContractsSchedule K provides a summary of hours and amounts billed on T&M and Labor Hour contracts.Yes, if company has T&M or LH contracts.
 LReconciliation of Total Payroll to Total Labor DistributedSchedule L is a schedule which reconciles labor costs from the General Ledger to the quarterly IRS 941 Tax Returns for the contractor’s FY.  Yes.
 MListing of Decisions/Agreements/Approvals and Description of Accounting/Organizational ChangesSchedule M is a schedule that identifies decisions/approvals involving memorandums issued by Administrative Contracting Officers and disclosure of any accounting or organizational changes.Yes.
 NIndirect Cost CertificationSchedule N represents an example of an indirect cost certificate.  Yes.
 OContract Closing Information for Contracts Completed in this Fiscal YearSchedule O is used to identify cost type, T&M, flexibly priced, and level of effort type contracts that will be closed after this current incurred cost submission is audited and the final indirect rates are agreed upon.Yes.
Supplemental A1Comparative Analysis of Indirect Expense Pools Detailed by Account to Prior Fiscal Year and Budgetary Data - Overhead ExpensesSupplemental Schedule A-1 contains the comparative analysis of the claimed overhead costs with the prior FY if applicable.  Yes, if applicable.
Supplemental A2Comparative Analysis of Indirect Expense Pools Detailed by Account to Prior Fiscal Year and Budgetary Data -G & A ExpensesSupplemental Schedule A-2 contains the comparative analysis of the claimed G&A costs with the prior FY.Yes.
Supplemental A3Comparative Analysis of Indirect Expense Pools Detailed by Account to Prior Fiscal Year and Budgetary Data - Occupancy ExpensesSupplemental Schedule A-3 contains the comparative analysis of the claimed intermediate pool costs with the prior FY if applicable.  Yes, if applicable.
Supplemental A4Comparative Analysis of Indirect Expense Pools Detailed by Account to Prior Fiscal Year and Budgetary Data - Direct CostsSupplemental Schedule A-4 contains the comparative analysis of the claimed direct costs with the prior FY.Yes.
Supplemental BGeneral Organizational Information and Executive Compensation for the Five Most Highly Compensated ExecutivesSupplemental Schedule B provides general organizational information and information on Executive Compensation. Yes.
Supplemental CIdentification of Prime Contracts Under Which the Contractor Performs as a SubcontractorIdentification of Prime Contracts Under Which the Contractor Performs as a Subcontractor.Yes, if applicable.
Supplemental OContract BriefingsSupplemental Schedule O contains the typical format for contract briefings.Yes.


Unanet Reports Used

Create and save the three reports below for inclusion in the ICE workbook:

  • GL Summary (Trial Balance)
    • This report is located in: Reports > Financial > General Ledger > GL Summary (Trial Balance)
    • Ensure that the report criteria uses dates for the prior fiscal year.
  • Project Summary by Cost Element
    • This report is located in: Reports > Project Accounting > Summary > Project Summary by Cost Element
    • Ensure that the report criteria:
      • uses dates for the prior fiscal year.
      • is set to Include Posted Data only & Show Cost Element Details
      • has "Sorting Options" set to "Sort by Owning Org, Billing Type"

  • Project Summary by Cost Element Criteria Page


  • Project Summary by Cost Element Sample Report Based on Criteria Above


  • Ad Hoc on Projects


Process for creating the ICE workbook


Download ICE Model at http://www.dcaa.mil/ice_model.html

Complete the Setup Tab in the ICE Model (see screenshot below)

    • Company Name

    • Company Location

    • Fiscal Year End Date

    • Accounting Practices

    • Select the Number of pools/rates

    • Run Setup


Insert a new tab into the workbook and Rename this tab "GL Summary".  


  • Run your GL Summary report (referenced above) on prior fiscal year, then Copy and Paste that report data into the "GL Summary" tab.  
  • Delete Account Type Headers and Total Rows (see screenshot below)
  • Highlight worksheet and unmerge cells (Home - Merge & Center)
  • Insert a column after Column A
  • Use Text to Columns to separate the Account code from the Account Description (Data - Text to Columns - Fixed Width - Next - Finish - OK)
  • Delete Extra Columns


When you are finished with #3, the GL Summary tab should look something like this:


SCHEDULES B-D(x), L, and supplemental schedules A1-A3

Follow the Instructions on appropriate schedule, inserting Rows as needed for each Cost Pool via the following steps:

    • On the Sched B tab, for example, Link Column A (Account Number), Column B (Description) and Column C (Amount) to the GL Summary tab as in the screenshot below
      • Alternatively, you can use the VLOOKUP function to find the accounts and descriptions.
    • Copy formulas in columns H & J into newly inserted rows


Insert a new tab/Worksheet into the workbook and Rename it "Project Summary by Cost Element)

    • Copy and Paste the Project Summary by Cost Element Report (which you created above) for the Previous Fiscal Year (as well as other criteria specified above)
    • Delete Report Header, Owning Organization Headers, and Totals rows
    • Sort in the worksheet by Billing Type
    • When finished, the Project Summary by Cost Element tab should look something like this:

SCHEDULE H

Follow the Instructions on Schedule H, Inserting Rows as needed for each Unanet Project 

  • Replace the Direct Cost Categories on Schedule H with the Cost Elements on your Project Summary by Cost Element tab
  • Link Column A (Job Order) on Schedule H to Column A (Project) on the Project Summary by Cost Element tab for each billing type
  • Manually enter the Contract Number and Subcontract Number in Columns B & C

SCHEDULE I

Follow the instructions on Schedule I

    • Insert rows and copy/map formulas data into columns D-F, K-M, and O.  As a note, yellow cells in the model are linked to other worksheets; blue cells have calculations.
    • All other columns linked to Schedules H and K

SCHEDULE J

Follow the instructions to complete Schedule J.  NOTE: This Schedule is not linked to other schedules


SCHEDULE K

    • Insert a new tab/Worksheet into the workbook; Run the "ICE Sched K" Ad Hoc report.  

Ad Hoc Report Definition Sample

If you need to create the ad hoc report follow the steps below, or alternatively DOWNLOAD THIS AD HOC REPORT DEFINITION: Sample - ICE Sched K Ad Hoc report.xml (also below in Additional Information)

Once the report definition is downloaded, you can import the report definition into Unanet and save the report definition by going to Reports - Ad Hoc - +Ad Hoc, and then Import as in the screenshot below.


When importing an Ad Hoc Report definition, you don't need to specify the Data area or View.

Once you have imported the report definition, be sure to Save it:

  1. Ad Hoc Report Creation (if Sample in step above is not used)

  2.   
  3. The steps below outline how to create from scratch the Ad Hoc Report or to review how the Ad Hoc sample above was created.
  4. You can skip the steps in 9a and go down to 9b if you instead downloaded and imported the Ad Hoc report definition called "Sample - ICE Sched K Ad Hoc report" above. 

  5. Create a new ad hoc report by clicking the +Adhoc Report link on the upper right.Select the Data Area: "Person" and the View: "Time"

Click Continue.

Click on the Custom Columns tab and add two custom columns.  Call them Total Billed Amount and Timesheet Cell Hours.

Use the formulas shown in the right graphic for each.  

Click Save.

These custom columns will become available in the Report Columns tab for selection once saved.

 

On the "Report Columns" tab below are the data elements you can choose from.  Find the columns initially on the left and highlight them (control/left click).  Once all are selected, click the blue >> to move them from "Available Columns" on the left to "Report Columns" on the right.

 


On the Sorting tab, Sort based on the Sort Columns shown here.

In the Criteria tab, enter the following lines to filter data by fiscal year and project type (billable).


Choose to sum (foot) the Timesheet Cell Hrs and Total Billed Amount.


Once run, the report should look something like this:



b.  Run and either download as a CSV or copy and Paste the output from the the Schedule K_Ad Hoc report directly into the Worksheet (Reports - Ad Hoc - Schedule K_Ad Hoc).

 


c.  Follow the instructions to complete Schedule K, Inserting Rows and Columns as needed for each Unanet Labor Category and Project Code.

d.  Link the Contract No. to the Unanet Project Code on the Schedule K_Ad Hoc tab, using IF(AND()) command, link your COntract No. and Task to the data downloaded into the Schedule K_Ad Hoc Report worksheet.

This formula looks in the Sched K Ad Hoc tab for records where the project is a match and provides the LCAT.

e.  Link the Hours and Rate columns to the Hours and Timesheet Cell Bill Rate on the Schedule K_Ad Hoc tab.  Repeat the previous step to find the LCAT Rate on the Schedule K_Adhoc Report worksheet.  

This formula looks in the Sched K Ad Hoc tab for records where the project, task and LCAT are a match and provides the Rate.

f. Finally, repeat the above step for a third time, linking the LCAT with the Project and task from the Schedule K_Ad Hoc Report Worksheet.

This formula looks in the Sched K Ad Hoc tab for records where the project, task and LCAT are a match and provides the Hours.

    • Link the Other Costs (Material, Travel, ODCs) to Schedule H
    • Manually enter the task ceilings
    • Repeat the above steps for each project/task combination, keeping in mind anchored cells and the horizontal format of the Schedule K.

SCHEDULE L

Follow the instructions to complete Schedule L, Inserting Rows as needed for each Labor Account on Schedules B,C,D,E, or Fringe

    • Link the Acct No. to the Account Number on the Fringe Schedule.  Consider whether there is any unallowable labor that might need to be added to reconcile.
    • Link the General Ledger Amount in column A to the Claimed Cost on Schedules B,C,D,E, or Fringe 
    • Manually enter the Quarterly Wages from 941s 
    • The prior year accrued payroll should come from the beginning balances on the GL Summary Tab; the Current Year Accrual should come from the ending balances on the same tab.

SCHEDULES M, N, O

Follow the instructions to complete Schedules M, N & O. NOTE: These Schedules are not linked to other schedules

SUPPLEMENTAL SCHEDULES

To complete the Supplemental Schedules, select the Build Schedules button.  These can be linked to the Trial Balance or other schedules, as required.

Should I use a Refreshable Web Query? (RWQ)

We recommend that you simply run the reports instead of setting up a RWQ, since you will only run this process once a year and since it is likely that the following year there may be changes to the GL.

 

Note:  this is functionality outside the scope of Unanet and not supported by Unanet Customer Support as it relates to third party tools (Excel).


Additional Information

Webinar - ICE (Incurred Cost Electronically) MODEL

DCAA Government page on ICE

ICE_MyCo.xlsm

Sample - ICE Sched K Ad Hoc report.xml (also above in the Schedule K section)


  • No labels