Create bill calculations with a spreadsheet
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 create update spreadsheet
To download a blank spreadsheet:
- From the Setup Spreadsheets Log, click Download.
- Under Create Spreadsheets, choose Chargebacks: Bill Calculations.
- Download the spreadsheet.
- The name format is yyyymmdd-CREATE_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.
Create bill calculations spreadsheet details
Column | Rules |
Calculated Bill Details - Meter | Enter the meter code. |
Calculated Bill Details- Account | Enter the account number. |
Calculated Bill - Vendor Code | Optional if the account number is unique. |
Version Information - Start Month | A default version is created if nothing is entered. |
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. |
Version - Delete Existing Version |
Choose DELETE THIS VERSION to remove the version listed on the row. This is not applicable unless you are working in an update sheet. |
Choose one of the 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 fields turn gray. | |
Calculated Bill Use | |
Channel Readings - Channel Type | Uses readings from a submeter to calculate use. Select from the drop-down. If your meter is linked to a SmartAnalytics data point, you can choose SmartAnalytics data point. |
Channel Readings - Channel Interval | Choose from the drop-down. |
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 | |
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 | |
Calculation - Subtract Meter | |
Calculation - Subtract Meter Group | |
Choose one of the five options for calculating cost. As you enter a value for one of the options the fields for 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 | The unit of measure. |
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 - Channel Type | Choose from the drop-down. |
Channel Readings - Channel Interval | Choose from the drop-down. |
Fixed Amount - Monthly Demand Value | Enter the fixed amount. |
Fixed Amount - Unit | Choose from the drop-down. |
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 from 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 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 from Fixed Amount or Percentage Amount. |
Line Type | Select from the drop-down. |
Label | Add the label to appear on the bill. |
Amount | Enter the amount or percentage. |