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 simply populated with the corresponding information from your database.

 

Download the create update spreadsheet

To download a blank spreadsheet:

  1. From the Setup Spreadsheets Log, click Download.
  2. Under Create Spreadsheets, choose Chargebacks: Bill Calculations.
  3. Download the spreadsheet.
  4. The name format is yyyymmdd-CREATE_BillCalculations.xlsx.
  5. Add your information and save.
  6. Use Import to upload your spreadsheet.
  7. The Setup Spreadsheets Log reports the results and any errors. You can download the kickout (errors) 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.