Update bill splits with a spreadsheet

What it's for

The Update Bill Splits spreadsheet lets you:

  • Create, update, or delete bill splits using one file.
  • Work with existing splits or start fresh.
  • Manage chargeback workflows and detailed bill components.

Spreadsheet types

  • Create bill splits spreadsheet
    • Downloaded version has empty columns.
    • Use to build new bill splits from scratch.
  • Update bill splits spreadsheet
    • Downloaded version is contains your existing bill splits.
    • Use to view, edit, or delete existing splits.

What you can manage

You can view, add, or edit:

  • Static split percentages
  • Dynamic split percentages
  • Advanced bill split details
  • Chargeback workflow steps

Notes

  • All updates must follow the correct format.
  • Deletions happen by flagging the relevant rows (not by removing them).
  • If you copy and paste information, you must use the paste as values option.

What happens on upload

  • Only rows without errors are processed
  • If there are issues:
    • You'll get an error file with messages for each row.
    • Cells with problems will be highlighted in red.

Update spreadsheets save time and allow you to update multiple items at once.

  • Update spreadsheets typically display two columns for each attribute, updates should be entered in the white columns.
  • Always download a new spreadsheet before you begin adding information because each sheet is populated with data from the database.

Formatting rules

  • All information should be entered as text or dates.
  • You can add sheets if needed, UtilityManagement only processes the sheet named DataEntry.
  • Do not remove columns.

This spreadsheet is a special type of setup spreadsheet that lets you create, update, or delete ALL with one file.

When you download the create spreadsheet all the columns are empty. When you download an update spreadsheet, it is the same format populated with the information from your database.

Download the update spreadsheet

In the Create dialog, click Setup Spreadsheets.

  1. From the Setup Spreadsheets Log, click Download.
  2. Under Update Spreadsheets, choose Chargebacks: Bill Splits.
  3. Set the filters as needed to limit the amount of data in the spreadsheet.
  4. Download the spreadsheet.
  5. The file name format is yyyymmdd-UPDATE_BillSplits.xlsx.
  6. Add your information and save.
  7. Use Import to upload your spreadsheet.
  8. The Setup Spreadsheets Log reports the results and any errors. You can download the error (kickout) spreadsheet from the hyperlink, fix any errors, and upload the error sheet.

Spreadsheet layout

The update bill split spreadsheet is divided into four sections: bill split source information, followed by destination information for each different bill split type. Each source can only be split with one method.

Available update columns are listed below.
Column Rules
Bill Split Source- Meter Code Enter the meter code for the source of the split.
Bill Split Source - Account Number Enter the account number for the source of the split.
Bill Split Source - Vendor Code If the account number is not unique in UtilityManagement you need to enter the vendor code.
   
Version - Start Month

A default version is created if nothing is entered.

 
Version - Start Year
Version - Version Name
Version - Chargeback Workflow Step If the workflow step is created, choose it from the list.
Version - Delete Existing Version Choose DELETE THIS VERSION to remove the version listed on the row.
 

In a static percentage configuration, meters are selected as recipients and the associated bill split percentages are manually entered. The total is not required to equal 100%. If the total does equal 100% the sum of the bill splits exactly match the source bill.

Remember to only enter source information one time to avoid adding an extra chargeback.
Static - Destination Meter Code Enter the meter code for the destination of the split.
Static - Destination Account Number Enter the account number for the destination of the split.
Static - Destination Vendor Code Choose the vendor from the list.
Static - Split Percentage A 100% split is not required.
  In a dynamic percentage configuration, meters are selected as recipients and the associated bill split percentages are proportional to the floor area that each meter's parent building contributes. Differences in energy utilization are configured with a weighting factor.
Dynamic - Destination Meter Code Enter the meter code for the destination of the split.
Dynamic - Destination Account Enter the account number for the destination of the split.
Dynamic - Destination Vendor Choose the vendor from the list.
Dynamic - Weighting Factor A weighting factor is assigned to a meter giving it lighter or heavier importance in a group. A warehouse may have a lighter weighting factor (.25) and a data center may have a heavier weighting factor (1.8).
  Advanced bill split
Advanced - Destination Meter Group Choose a meter group to receive bill splits.
Advanced - Split Use, Cost, or Both Determine what you are splitting: use, cost, or both from the source meter.
Advanced - Split Method: Use or Cost

Determine if the split is allocated proportionally by use or cost.