These are some of the processes we use to maintain our internal picture of our financial situation. Typically, one MEDLab fellow is designated the accounting point-person at any given time.
Adding Expenses to Accounting Spreadsheets
Updating Accounting Spreadsheets from Reports
Navigate to MEDLabExpenses spreadsheet in MEDLab Cloud, in the Budgeting folder. Here you will find a workbook dedicated to tracking the expenses of every active MEDLab speedtype.
At the beginning of each semester, email firstname.lastname@example.org and request the following monthly reports:
- A financial detail report for each active MEDLab speedtype showing only the activity from the past month. You will have to provide the speedtype numbers in the email.
- A speedtype one-liner report that shows the balance of all active MEDLab speedtypes mentioned in the previous point.
Note: if any of the speedtypes are subsidiaries (funding multiple projects ex: Luce), you will not need to ask for any reports. Nathan will update these spreadsheets with any new expenses.
CMCI Finance will send these reports at the beginning of the next month. (In the first week of March you will receive the reports showing all February expenses)
Referencing the financial detail reports, add any expenses not already on the spreadsheet to the sheet for the corresponding speedtype.
Items you will need to pull from the sheet to add along with the total of the charge:
- Journal ID #: Copy and paste from the “Journal ID” column on the report to the spreadsheet
- Date: Take from “Journal Date” column on report
- Description of transaction: take from “Journal LN Desc” column on report
- Debit/Credit: take from the “Actuals” column on report
- * if the charge is in parentheses ex: (700.00), it is a credit.
- The Balance should update automatically at the top of the spreadsheet.
Once you have updated the spreadsheet to include all transactions, the Budget reflected at the top of each workbook should match the balance shown on the speedtype one-liner report for each speedtype.
Note: if you ever need assistance reading the financial reports (they can be a little confusing to navigate), email CMCI Finance with your questions or set up a meeting.
Creating a New Accounting Spreadsheet
For each fiscal year, you should plan on having a different MEDLabExpenses workbook. The last day of the fiscal year takes place on June 30th and is often referred to as Fiscal Year End (FYE).
You should plan on using a new spreadsheet to track expenses starting from July 1st.
To make a new expense tracking workbook:
- Navigate to the expense workbook from the previous fiscal year in MEDLab master.
- Click the three dots next to the file size > “Move or Copy” > “Copy” (bottom right)
- Change the name of the workbook to reflect the current/new fiscal year.
- Keep the different spreadsheets for the different speedtypes. Delete any inactive speedtypes and add any new speedtypes.
- The easiest way to make a new spreadsheet for a speedtype is to make a copy of an existing one: click the existing spreadsheet speedtype tab & hold down CTRL at the same time > “Move or copy sheet” > “Copy” > choose location > change name > “OK”
- Delete any transaction data from the previous fiscal year but retain the formulas on the spreadsheet.
- Select all the data you want to delete > Click delete button > Select “Text” and “Numbers” and make sure the rest are de-selected > “OK”
- Make any update to the spreadsheet header.
- Repeat for other speedtypes as needed.
- Begin adding new expenses.
- NOTE: Enter the balance at the start of the fiscal year in the first column on each spreadsheet for each speedtype.
- You do not have to edit any of the other tabs aside from the speedtype sheets. The other sheets will automatically update based on information from the speedtype sheets.
For anything related to invoicing, please email CMCIFinance@colorado.edu with any questions. The Procurement Service Center is a great resource for any finance-related information.