Department of Finance Reporting

The California Department of Finance has asked all state agencies to begin reporting costs related to COVID-19 on a monthly basis so that we are well-positioned for potential reimbursement of these expenses by the federal or state government. To ensure we’re capturing all the relevant expenses, the UC Office of the President has requested we submit our data on costs related to COVID-19 to them on a monthly basis, prior to the submission to the Department of Finance. We’re gathering this data monthly in a template, but please also continue to record costs associated with COVID-19 with the new tracking codes. 

Each month, we’ll send out a reminder to complete the Department of Finance template. Once you’ve completed the template, please email it to the Financial Planning & Analysis team at dfl_concierge@berkeley.edu with the subject "COVID-19 Cost Impact" and your division name.

Updated Department of Finance reporting template

Beginning in June 2020 there is an updated template provided by UCOP. This template allows you to provide line-specific detail for items under revenue, incorporates the inclusion of and line item detail for remote working costs, and allows for a separate category for health centers and clinics. When filling out this new template, please ensure that you fill in data for past months so that we are appropriately capturing year to date amounts. Additionally, the “Instructions” and “New Total to Date” tabs include new instructions on where to include detail for amounts included under “Other” in each category and on file naming conventions. Please be sure to review those tabs.

Please ensure you continue to record all of the COVID-19 expenses for each month going forward for your division in the general ledger using the CF1 and CF2 codes where possible. 

Download the revised template 

Department of Finance cost tracking template (2/2021) - follow the link to download the revised file.

Revised template as of February 2021

The DOF template DFLs used for the past few months only went through January 2021. Moving forward, DFLs will need to complete an updated template. 

To do this, DFLs have two options:

  1. Update your existing template to go through February 2022. If you choose to update your existing template, please review the linked job aid which will assist you with updating the formulas and columns.

  2. Download a new version of the template that is available at the link above. Our reporting to UCOP is cumulative, so DFLs will need to transfer the data from previous months into the new template if they choose this option. 

Instructions for completing the template

Background

The California State Department of Finance ("DOF") is asking every State agency to report expenses related to COVID-19 on a monthly basis using the attached template, tab titled "Total To Date", that they have provided. DOF is collecting this information in order to (a) build the strongest possible case for any potential federal reimbursement of COVID-19 costs, and (b) inform decisions about how any available State funds might be distributed across agencies.

In order to make reporting easier and more consistent across the campuses and medical centers, UCOP has developed a monthly worksheet, tab titled "Monthly," which is linked to the State template, to summarize cumulative financial impacts incurred to date in the following categories: 1) lost revenue, 2) emergency medical services, 3) facilities and cleaning costs, and 4) additional online/remote costs.

UCOP has requested that we provide the monthly reports of cost estimates related to COVID-19. We will be compiling results from each division across the campus to report impacts and costs in a single submission on behalf of the UC Berkeley campus to UCOP who will, in turn, submit a consolidated submission to the DOF each month.

Completing the template

1. Please use the "New Monthly" tab to keep track of the 'extraordinary' financial costs/impacts of COVID-19 for each month in each of the four categories of impacts.

Note: 'extraordinary' costs are for activities that are in addition to, or above and beyond, current daily operations. If these are activities that replace or are in lieu of normal operations, then you should exclude them from this report. "


2. Select the 'Date of Report' (drop-down menu in cell C5), which automatically sums up the cumulative total costs incurred to date and automatically populates the 'Total to Date' template. The 'Date of Report' is based on the UCOP submission due date, not the date that it is due back to the center.


3. Select your 'Division' (drop-down menu in cell C6), which will be populated in the "New Total to Date" template.


4.  Be sure that you can document how you estimated or derived the expenses reported on the template, along with the applicable beginning and end dates. Although we are not asking you to provide that information now, it is possible that DOF, the California State Auditor, or another agency will request that information at a later date.


5. Finally, please review the "New Total To Date" template to ensure that your costs/impacts are captured correctly. The only information that you should input on this sheet is the 'Fund Source' (column J) for each category and the costs calculations column (column I) to detail any expenses captured under "Other"; everything else should be automatically populated once you complete the "New Monthly" tab.

Note: The absorbable cost column has been automatically filled in as "No" as the only amounts that should be included in this template should be those that are 'extraordinary'."


6. Submit your completed template to the DFL Concierge email (dfl_concierge@berkeley.edu) with the subject "COVID-19 Cost Impact" and your division name by the due date requested. Please also use the following naming convention starting with your division's 5 digit L-3 code and ending with the monthly submission date: XXXXX_UCB New DOF Template_06252020.xlxs."

Clarifications and Frequently Asked Questions

Please note when completing the template:

  • Report all numbers in the templates in $ thousands.

  • The template does not include formulas for the lines included below each category. Please enter the amounts in the detailed lines and a formula in the summary line that picks up all the amounts below so that they show up in the “Total to Date” tab.

  • Please only include costs related to the categories listed below (and in the template instructions). For recording expenses in the ledger, please continue to capture all expenses related to COVID-19 under the CF1 or CF2.

  • UCOP hasn't asked for research expenses, so do not include them in the template. Continue to track them in the ledger using only the CF1 value, COVD19, when and where possible.  

  • When recording encumbrances, please include the amounts that have already hit the ledger during the month when you are completing the template. In addition, to the extent that you can, include an estimate of the amounts that will hit the ledger later for that month. When actual data becomes available in the ledger, you can true it up.

  • The template is set up to track cumulative expenses. Use the next month’s report to true-up costs and/or estimates from the prior month. You can overwrite the data that was previously submitted to ensure that you have the most accurate information.

  • Cost and revenue impacts from a divisional perspective may not include accounting adjustments that will not be made until year-end. Capture the costs as accurately as you can. UCOP is aware of adjustments made at year-end.

In order to address questions and clarifications about tracking COVID-19 related expenses, we have created an FAQ page, which we will continue to update as questions and answers emerge. If you don’t see an answer to your question, please submit your question to vcfinance@berkeley.edu.

Guidance on reporting from UCOP

We've outlined a summary of the revised guidance on completing the template from UCOP here. If you would like to review specific questions and examples, please review the FAQ.

UCOP guidance on completing the template

  • Affiliated Entity Reporting - We have a few affiliated 501(c)(3) entities that ultimately record revenue or expenses on our ledger (International House; Haas Executive Education). Divisions should include the associated revenue and expenses in the DOF reports. Maintain records with the details in case we are asked to report on what is core funded vs what is self supporting or non-profit.  

  • Deferred Revenue - Record the estimated lost revenue in the month that you would normally collect the revenue. Do not defer it to the month that the activity will occur. Specifically, for Summer Sessions and units that have summer programs, do not defer the estimated loss for summer revenue to FY21. If you normally receive the revenue now, you should report it as lost revenue now. 

  • Bad Debt Expense associated with lost revenue - There is no need to reduce estimated revenue losses by historical bad debt expense percentages.

  • Payroll ExpensesWe will be reviewing payroll related expenses centrally and work with divisions to understand how they should be categorized based on the following categories.

    • Administrative leaveWe are not tracking these costs for the Department of Finance. 

    • Workforce -  We will only capture extraordinary, non-absorbable costs. Redeployment of the existing workforce will likely not qualify, but direct hires for the purpose of helping with COVID-19 efforts will likely qualify. An example of this is academic divisions who had to hire extra GSIs to help with remote instruction. 

  • Cost savings associated with lost revenue - To the extent that you can, capture any cost savings you are aware of in your estimates for lost revenue. This may be difficult to capture, especially for lost revenue associated with future services. 

  • Remote costs - The updated template now includes a space to include line item detail for costs associated with remote learning and remote work costs. Please utilize all the lines as needed on the "Monthly" tab and continue to detail any costs included under "Other" in the "Total to Date" tab in the "Costs Calculations" column.

  • True-up of expenses - Because the DOF reports are due prior to month-end close, you’ll need to estimate the current month’s expenses based on what you think will hit the ledger this month. True-up the amount in the next month’s submission using the previous month’s column of the report to reflect the actual amounts in the ledger for the previous month. This way, you will be able to tie the closed months back to the ledger.

  • Research revenue and expenses - For sponsored research related to COVID-19, do not include these costs in the DOF report since funding for research comes from grants. The same is true for some qualified payroll expenses that are covered as part of existing grants.

  • Tracking gift funding - Do not include lost gift revenue or any purchases that have been funded by gifts, especially COVID-19 gifts. To the extent that we are receiving funding to cover these costs, they should not be included in the report. 

Categories for reporting in the Department of Finance template

Review the categories list below to get further clarification on the types of information that we need to report. Please note that we are not reporting expenses that would occur during the normal course of business, but only those that are extraordinary and due to the impact of the coronavirus. These categories are exclusive for DOF reporting. COVID-19 expenses incurred outside of them should be captured using a CF1/CF2 in case they are eligible for other types of reporting that may be required. 

List of categories for reporting

Lost revenue due to canceled programs and/or lost enrollment

Examples include:

  • Undergraduate tuition and NRST

  • Graduate tuition and NRST

  • Housing and dining

  • Summer Sessions

  • UNEX

  • Other auxiliary enterprises (athletics, museums, theatres)

  • International sponsored research, conferences, training, travel

  • Other (research, conferences, programs)

 

Emergency medical services (student health clinics only)

Examples include:

  • Patient testing, treatment, and quarantine

  • Testing kits, masks, and medical supplies

  • Staff training and coordination

  • Other

 

Facilities and cleaning costs

Examples include:

  • Emergency housing for returning Education Abroad or UCDC students (14-day quarantine)

  • Additional deep cleaning/disinfecting of facilities

  • Hand sanitizers and other cleaning supplies

  • Public health information

  • Closures, staging and emergency triage

  • Other

 

Additional online/remote courses (specifically for campus closures) 

Examples include:

  • Zoom, WebEx, conference calls

  • Learning management systems (Canvas, Moodle, Blackboard, etc.)

  • Other - include costs associated with quarantined workforce here