Update bill calculations with a spreadsheet
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.
If you copy and paste information, you must use the paste as values option.
Download the update spreadsheet
In the Create dialog, click Setup Spreadsheets.
- From the Setup Spreadsheets Log, click Download.
- Under Update Spreadsheets, choose Chargebacks: Bill Calculations.
- Set the filters as needed to limit the amount of data in the spreadsheet.
- Download the spreadsheet.
- Name format is yyyymmdd-UPDATE_BillCalculations.xlsx.
- Add your information and save.
- Use Import to upload your spreadsheet.
- 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.
Available update columns are listed below.
| Column | Rules |
| Calculated Bill Details - Meter | Enter the meter code. |
| Calculated Bill Details- Account Number | Enter the account number. |
| Calculated Bill - Vendor Code | Optional if account number is unique |
| Version Information - Start Month | A default version is created if nothing is entered. A version holds the configuration information for a specific chargeback scenario and its effective billing periods. |
| Version Information - Start Year | |
| Version Information - Version Name | |
| Version Information- Chargeback Workflow Step | If the workflow step is created, choose it from the drop-down. If the workflow step is not available it can be populated later. |
| Version - Delete Existing Version | Choose DELETE THIS VERSION to remove the version listed on the row. |
| Choose one of four options for calculating use. It may be helpful to view the application while completing these sections. As you enter a value for one of the options the other options turn gray. | |
| Calculated Bill Use | |
| Channel Readings - Channel Type | Uses readings from a submeter to calculate use. If your meter is linked to a SmartAnalytics data point, you can choose SmartAnalytics data point. |
| Channel Readings - Channel Interval | Choose an item from the drop-down. If your meter is linked to a SmartAnalytics data point, choose SmartAnalytics data point. |
| Fixed Amount - Monthly Use Value | Use is determined by entering a fixed amount. For example, a lighting meter where monthly use is estimated and entered as a fixed amount. |
| Fixed Amount - Unit | The unit of measure for the fixed amount. |
| Copy Use from Another Meter - Percent to Copy | Copy a percent of use from another meter. The meter can be a different commodity if the unit system has a conversion between commodities. For example, calculating sewer use based on water use. |
| Copy Use from Another Meter - meter to Copy | Select the meter you want to copy. |
| Calculation - Add Meter | Add and subtract use from meters or groups of meters. For example, sum the output meters from an energy plant. |
| Calculation - Add Meter Group | Choose either a meter group or a meter. |
| Calculation - Subtract Meter | Choose either a meter or meter group. |
| Calculation - Subtract Meter Group | |
| Choose one of the five options for calculating cost. As you enter a value for one of the options the other options turn gray. | |
| Calculated Bill Cost | |
|
Rate Schedule |
Use the rate assigned to the meter. The effective rate on the first day of the billing period is used. |
|
Fixed Unit Cost - Unit Cost |
The specific unit cost to use in calculations. |
| Fixed Unit Cost - per Unit | |
| Unit Cost from Another Meter | Unit cost from another meter of the same commodity, from the same or adjacent billing period is used in calculations. |
| Fixed Amount | Enter the amount used each month. |
| Copy Cost from Another Meter - Percent to Copy | Use the percentage of the cost from another meter of any commodity for the same billing period. |
| Copy Cost from Another Meter - Meter to Copy | |
| Calculation - Add Meter | Add and subtract cost from meters or groups of meters. |
| Calculation - Add Meter Group | |
| Calculation - Subtract Meter | |
| Calculation - Subtract Meter Group | |
| Calculated Bill Demand | |
| Channel Readings - Type | The summarized reading is the highest value that exists from the start date to the end date of the bill. No demand line is added if the demand channel has no readings. |
| Channel Readings - Interval | |
| Fixed Amount - Monthly Demand Value | Demand is determined by entering a fixed amount. |
| Fixed Amount - Unit | |
| Add Meter Lines | |
| Add Meter Lines. A fixed amount adds an exact cost to the bill. Percentage amount is a percent of the subtotal directly preceding. | |
| Meter Line to Add | Choose Fixed Amount, Percentage Amount, or Subtotal. For example, a 10% tax is applied to the entire bill and another 5% needs applied to the new total. Add a new subtotal before calculating the 5%. |
| Line Type | Choose an item from the drop-down. |
| Label | Add the label to appear on the bill. |
| Amount | Enter the amount or percentage. |
| Add Account Lines | |
| Add Account Lines. A fixed amount adds an exact cost to the bill. Percentage amount is a percent of the total bill. | |
| Account Line to Add | Choose Fixed Amount or Percentage Amount. |
| Line Type | Choose from the drop-down. |
| Label | Add the label to appear on the bill. |
| Amount | Enter the amount or percentage. |