Financeof Kalispell
Post Office Box 1997 • Kalispell, Montana 59903-1997 • Telephone (406) 758-7700 • FAX (406) 758-7758
Date: March 3, 2000
To: Mayor Boharski and Council Members
From: Amy Robertson, Finance Director __j
RE: Priority Session item -Computer Software Goals
The saga continues with United Software Technology (UST) and American Data
Group (ADG). UST continues to serve us for our financial accounting package including
payroll, accounts payable, budgetary reporting, fixed assets, check reconciliation and general
ledger reporting. We purchased the Utility Billing program from ADG a year ago when UST
did not have their program written in time for Y2k compliance. Much research went into the
selection of ADG for this program and we have been extremely happy with it. The problem with
UST is that changes in focus for the company resulted in all of their employees being terminated
to create an online company. Most of their customers also left. Currently there is only one
person contracted with UST for maintenance. Lucky for us he is here in Kalispell. The owner's
father does the billing out of his home in Billings. The owner of the company manages the data
processing center for MSU and is not involved. The company at one time had more than a dozen
employees and assisted us with all phases of data processing including hardware, networking and
software. We have been with UST since 1984. (Before my time). Now they only maintain their
existing software package. Their one maintenance person does programming when he has time.
Kalispell was the test site for their payroll program. It's been two years and Marti still has to call
him for one thing or another every payroll cycle. We also pay them $9,240 per year for
maintenance which gives us up to 4 hours per month of technical help. In contrast we pay ADG
$800 per year for unlimited technical help.
So the first question is, do we hope UST stays in business and there is someone
available when we need help or do we look to switching all of our programs to ADG? ADG's
programs are written in the same 4rh generation language called "Progress" as the UST programs.
We did that by design so that we would not have to license different operating systems. The
hardest part of changing software vendors is the conversion. Generally it is pretty intense for
training as well. When ADG came for the utility install, the technician worked really long days
for two weeks, including the weekend. Attached are prices which we got last year from ADG.
One of the things we would like to do is implement the cash drawer with receipt printer function
so that we would not have to handle everything twice. UST does not have this function available
and it wouldn't work unless the utilities and the general ledger were on the same programs. We
currently receipt all of the utility receipts to the customer ledger and pull the totals to the general
ledger with a journal voucher interface.
The next question is funding. I have set aside in the Data Processing Internal Service
Fund money to replace the software and make annual hardware replacements. Currently the
replacement fund has a balance of $86,000. Last year we replaced the file server and upgraded
the network For me its very difficult to find the time to do the conversion. Trying to fit this
between Budget preparation and preparation of the Annual Financial Report will be difficult for
me. Of course, doing it on an emergency basis would be even worse. I do not want to convert
payroll until we have someone else who can learn the payroll program. The ideal time to do
payroll is near year end so that we can run concurrent systems and start the new calendar year on
the new system. I also doubt that we will get much cooperation from UST to help with a
conversion. The fellow who takes care of our network problems is a former UST employee and
his help will be invaluable should we do this conversion. He worked hand in hand with ADG on
our utility conversion. Our annual maintenance costs would be about half of what we pay UST
now. My recommendation would be to budget and plan to upgrade the software programs next
fiscal year.
Quote for City of Kalispell Good for
90
from:
Days
Mrs. Amy Robertson Financial information September 27, 1999
Management System
Description Price Unit Total:
American Data Group Software:
Financial Information Management System
General Ledger - Budget Preparation - Financial Statements -
Accounts Receivable - Accounts Payable - Purchase Orders
w/Encumbrances - Inventory Control - Centralized Cash
SUB TOTAL
American Data GrouD Services
Installation, Setup and Conversion*
Training"
Customer's Option for Custom Modifications*
SUB TOTAL
Progress 4GL RDSMS
Development Systems
4GL Development System
Deployment Product
Client Networking
Query/RESULTS Report Writing
Database Product
Workgroups Server
SUB TOTAL
Maintenance and Support Plan
Financial Management Information System (all modules)
Progress 4GL RDBMS Software Cost
SUB TOTAL
Future Options
Cash Drawer with Receipt Printer
Fixed Asset with Fleet Maintenance
Building Permit System
Business License System
Electronic Requisition System
Electronic Time Card Entry System
Imaging System Software
(may have to increase number of Progress users to access system)
$16, 000
Estimated Hours
$100 10 $1,000
$100 60 $6,000
$100 10 $1,000
$8,000
Customer has and will provide additional
Progress Deployment Products
$2,780 0
$125 0 $0
$575 0 $0
$200 0 $0
$0
Quote Total Pricing: $24,0001
$16,000 15%
$0 17%
$1,200 2
NOTE:
*The above services are billed at $100/hour plus per diem expenses
such as travel, meals and lodging. We estimate the approximate hours
to give a fair look at total cost. Please note you only pay for services actually incurred.
Conversion is based on your submission of data to us in text form.
$2,400
$0
$2,400
$2,400
$6,000
$6,000
$6,000
$6,000
$6,000
$6,000
American Data Group
Page 1 of 1 800/481-1790 Prepared by: Jack Ewing
Client File: 9City of Kalispell.xls
Quote for City of Kalispell Good for 90
Mrs. Amy Robertson Days from:
Financial information September 27, 1999
Management System
Description Price Unit Total:
American Data Group Software:
Financial Information Management System
Payroll/Personnel with Laser Check Printing and Reconciliation
SUB TOTAL
American Data Group Services
Installation, Setup and Conversion*
Training*
Customer's Option for Custom Modifications*
SUB TOTAL
Progress 4GL RDBMS
Development Systems
4GL Development System
Deployment Product
Client Networking
Query/RESULTS Report Writing
Database Product
Workgroups Server
SUB TOTAL
Maintenance and Support Plan
Financial Management Information System (all modules)
Progress 4GL RDBMS Software Cost
SUB TOTAL
Future Options
Cash Drawer with Receipt Printer
Fixed Asset with Fleet Maintenance
Building Permit System
Business License System
Electronic Requisition System
Electronic Time Card Entry System
Imaging System Software
(may have to increase number of Progress users to access system)
$13, 000
$13,000
Estimated Hours
$100 30 $3,000
$100 70 $7,000
$100 10 $1,000
$11,00 1
Customer has and will provide additional
Progress Deployment Products
$2,780 0
$125 0 $0
$575 0 $0
$200 0 $0
$o
Quote Total Pricing: $24,0001
$13,000 15% $1,950
$0 17% $0
$1,950
$1,200
NOTE:
*The above services are billed at $100/hour plus per diem expenses
such as travel, meals and lodging. We estimate the approximate hours
to give a fair look at total cost. Please note you only pay for services actually incurred.
Conversion is based on your submission of data to us in text form.
$2,400
$6,000
$6,000
$6,000
$6,000
$6,000
$6,000
American Data Group
Page 1 of 1 800/481-1790 Prepared by: Jack Ewing
Client File: 9City of Kalispell.xis
FEATURES OF SOFTWARE
e Fund Management
(Some modules sold separately)
• General Ledger
• Budget Preparation
• Financial Statements
• Accounts Receivable
• Accounts Payable
• Purchase Orders w/Encumbrances
• Electronic Requisitions w/approval ladder
• Multi Warehouse Inventory Control
• Cash Receipting
• Payroll/Personnel
• Electronic Time Cards w/approval ladder &
electronic signature
• Human Resources
• Job Costing (Project/Grant Accounting)
• Check Reconciliation
o Fixed Assets & Preventative Maintenance
• Fixed Assets
- Loan Amortization/Payment Calculation
- Tracks custodial responsibility
- Tracks sources of funds
- Tracks purchases, transfers, adjustments,
status changes, depreciation, insurance and
maintenance
• Preventative Maintenance
- Develop multiple maintenance plans
- Attach maintenance plans to items for service
- Automatic scheduling based on plans
- Scheduling of vehicles & equipment
- Tracks types of costs incurred by items
a Utility Billing
• Performs cycle final & corrective billings
• Bills for unlimited # of services per property
• Bills for flat, metered, or meter -related services
• Service types include
- Water/electric/sewer/sanitation/
natural gas/administrative
• Meter reading input
- Automatic/hand-held reading devices
- Manually from meter books
• Meter inventory
• Cash register interface
• Work Orders issuance for tracking by property
a Court Management
• Tracks all information for a municipal court
- Tickets/Cases/Bonds,Tscrows
- Officers/attorneys/judges/court rooms
- Dispositions/fines/costs
- Cash register interface
- Print letters, ready for mailing
- Speed keys for input while court is in session
- Prints Plea by Mail letters w/amended charge
s Sales Tax Collections & Business Licenses
• Manages local tax collections, including
- Sales/Use/Accommodation taxes
- Other user -defined taxes
- Amount reported by filing period
- Receivable balances
System performs the following functions:
- Missing filers tracking
- Multiple vendor contacts
- Posts taxes and receipts
- Prints licenses and renewal letters
Business Licenses
- Handles multiple units of calculation
- Prints licenses
• Building Permits
• Tracks
- Permit applications & Issued permits
- Contractor licensing & printing
- Land parcel data
- Legal documents
- Valuation & fees
- Inspections
® Imaging
• File scanned documents electronically
• User -defined categories
• Can be added to any ADG module
i Special Features
• True Windows product; Windows 95 and
Windows NT 100% compliant
• Runs in Client/Server, Unix, or Stand-alone modes
• 4GL database which can be transported among a
variety of platforms and networks: Windows
(including NT), DOS, Unix, DEC/VAX, Novell
• Automatic data recovery upon power failure
• On-line manuals; can personalize on-line SOP's
• Password Protected/Restrictecd Access
Clients include (Partial List)
• City of Brunswick, GA
• City of Louisville, CO
• SJWD, Lyman, SC
• Ware County, Waycross, GA
• Highlands Ranch Metropolitan Districts, CO
• Crestview Water District, CO
• Beaufort -Jasper Water and Sewer Authority, SC
• City of Largo, FL
• Indian River Sheriffs Office, FL
• Polk County Sheriff's Office, FL
• Orange County Clerk Office, FL
• City of Forrest Park, OH
• City of Aztec, NM
• City of Avondale, AZ
• City of Federal Heights, CO
• Hillsborough County Tax Collector, FL
• City of Alachua, FL
• City of Ocoee, FL
• St. Lucie County Sheriff s Office, FL
• Okeechobee County Government, FL
For more information, please call (800) 481-1790
American Data Group Offices - Enaiewood. CO - Fort Mvers. FL -Marietta. GA
City of Kalispell
Post Office Box 1997 • Kalispell, Montana 59903-1997 • Telephone (406) 758-7700 • FAX (406) 758-7758
Date: March 3, 2000
To: Mayor Boharski and Council Members
From: Amy Robertson, Finance Director
RE: Priority Session item- Investment Policy
Please find attached a formal investment policy for the City of Kalispell. This is the
policy which we have been using - it is straight out of Montana Statutes. The Council has never
formally adopted a resolution, however, that is certainly an option. The City may make the
policy more restrictive than State law. Some of the items which we can invest in such as
warrants and revenue bonds of other cities and counties are unlikely investments for the City of
Kalispell. This policy is almost verbatim to the County Policy adopted in 1989.
There seems to be some question about using the services of STIP. The State
Investment Pool is where the State puts it's money and they allow local governments to also
benefit from their expertise. The Stip program invests in long-term and short-term investments
which allows us to have a better rate. They also have more diversification than we could have on
our own. Currently we would have to go out well over a year to beat the rate that stip is paying -
6% last month. We had two repurchase agreements which came due in December -UDAG
money which we invested. We ended up making less than if we had left the funds in STIP. Stip
has agreed to set up a separate account for tracking of our UDAG investment. Of course, we will
be loaning half of it out shortly. Repurchase agreement bids which we received last year
ranged in 4.5 to 4.7%, STIP was never below 5.25%.
One other consideration investing is the implementation of GASB #33 for reporting
of investments. This statement is effective for financial reports prepared after June 15, 2000,
however, early implementation was encouraged and I did implement this statement last year. It
only affected the UDAG investment. Funds invested in STIP are exempt from this reporting
requirement. All other investments must be recorded at market value. So if I have investments
in government backed repo's which reflect a loss then I will have to write down the fund's cash
account and record a loss. Currently we don't have an appropriation to reflect a loss. Of course,
gains are also recorded. It makes it impossible to pool money for investment from different
funds. The investment, like UDAG, would have to reflect just that fund's money. This can get
very time consuming and at our level of staffing can't be accomplished very well.
If Council wishes to restrict City investments from what the State allows, then a
formal resolution would be necessary.
Investment Policy of the City of Kalispell
The City of Kalispell will use repurchase agreements in the City investment program, unless a
financial institution agrees to pledge securities in the amount of:
(1) 100% of such deposits if the institution in which the deposit is made has a net
worth to total assets ratio of less than 6%; or
(2) 50% of such deposits if the institution in which the deposit is made has a net
worth to total assets ration of 6% or more and has filed with the City Finance
office its latest financial statement demonstration such ratio.
If repurchase agreements are used, securities will be purchased by the City of Kalispell subject to
repurchase by the investing institution.
The following types of securities will be acceptable to meet pledging requirements or will be
purchased by the City under the repurchase agreements. MCA 17-6-103
(1) Direct obligations of the United States Government.
(2) Securities as to which the payment of principal and interest is guaranteed by the
United States Government.
(3) Securities issued for fully guaranteed by the following agencies of the united
states government, or their successors:
a. Commodity Credit Corporation;
b. Federal Home Loan Mortgage Corporation
C. Federal Land Bank
d. Bank for cooperatives;
e. Federal Home Loan banks;
f. Federal National Mortgage Association
g. Government National Mortgage Association;
h. Small Business Administration
i. Federal Housing Administration
(4) General obligation bonds of the State of Montana, or of any County, City or
School District within the State of Montana.
(5) General Obligation bonds of other states and municipalities, counties, and school
districts of other states, provided , however, that any such our -of -state bonds
must be rated BAA or higher by Moody's, or its equivalent if rated by another
agency;
(6) revenue bonds of any county, city or other political subdivision of the state, when
backed by the full faith and credit of the subdivision or when the revenue pledged
to the payment of the bonds is derived from a water or sewer system and the
issuer has covenanted to establish and maintain rates and charges for the system in
an amount sufficient to produce revenue equal to at least 125% of the average
annual principal and interest due on all bonds payable from the revenue during the
outstanding term of the bonds;
(7) Interest -bearing warrants of the state or of any county, city, school district or other
political subdivision of the state issued in evidence of claims in an amount that, with all other
claims on the same fund, does not exceed the amount validly appropriated in the current budget
for expenditure from the fund in the year in which they are issued.
The Finance Office is encouraged to deposit funds in such a manner as to maximize the amount
of investments that will be federally insured.
Public money not necessary for immediate use shall be offered on a bid basis to banks and
savings and loan institutions located within the City of Kalispell and will be deposited with the
high bidder(s) upon confirmation that such a bidder(s) has pledged securities to the City in the
amount required above or has deposited sufficient qualifying securities to effect a repurchase
agreement.
The City will reserve the option to invest in U. S. Treasury bills, qualifying securities guaranteed
by the United States Government, or the State of Montana Investment Pool( STIP).
Securities which are either pledged or are the subject of repurchase agreements must be in the
possession of an independent third party and that third party (i) must be bonded in an amount at
least equal to the amount of the repurchase agreement, and (ii) must affirm to the City Finance
office that the securities are in its possession.
The City shall take the necessary and appropriate steps to implement and administer this policy.
LEGAL MONTANA INVESTMENTS
WHAT ARE?
U.S Treasury Bills - U.S. Treasury bills are commonly used money market instruments in the public
sector since they are considered to be virtually risk -free and very liquid. For this reason, their yields
tend to be lower than other money market instruments. Treasury bills are short-term obligations
with maturities of one year or less. Bills are issued at a discount from par (face) value. The return to
the investor is the difference between the discounted purchase price and the face value at maturity (or
between the purchase price and sale price accepted in the secondary market.) Treasury bills are sold
in minimum amounts of S10,000 and in multiples of $5,000.
U.S. Treasury Notes and Bonds - U.S. Treasury notes and bonds known as coupon securities. The
coupon is the stated interest rate on the security. Notes are issued with original maturities of 10 years
or less, while bonds are issued with maturities longer than 10 years. Treasury notes and bonds are
sold in minimums of S1,000 and in multiples of S1,000, except for notes with maturities of less than
four years. These notes are sold- in minimums of 55,000 and multiples of S5,000. Coupon securities
with maturities of less than five years are generally quite marketable (highly liquid.)
Zero -Coupon Bonds - Zero --coupon bonds are bonds that pay no interest until its final maturity.
These securities are created by separating the coupons from the principal and interest payments of a
bond. The stripped bonds are then offered to investors at discounted prices from the maturity value.
Zero -coupon bonds are attractive to investors because investors avoid the need to reinvest coupon
interest payments, which can be difficult for small investors to do efficiently. Zero -coupon bonds
allow investors to lock in a specific interest rate. Examples include:
Separate Trading of Registered Interest and Principal
Securities (STRIPS) - Principal or interest payments from selected Treasury notes and bonds
Kith maturities of 10 years or more. These securities are issued with a Treasury CUSIP
(Committee on Uniform Securities Identification Procedures) number and are therefore not
classified as "derivatives."
State and Local Government Series (SLGS)
Interest Only Strips (IOS) - Interest component stripped from underlying mortgage -backed
securities..
Principal Only Strips (POS) - Principal component stripped from underlying mortgage -
backed securities.
U.S. Government Agency Securities - Those securities backed by the full faith and credit of the U.S.
government. Generally, the underlying security associated with a U.S. agency is considered to be as
risk -free as direct Treasury securities. The key difference in risk with these instruments is their
liquidity and marketability, which is diminished as a result of smaller, irregular, and less familiar
issues. These are not money market instruments (Le. the maturities exceed one year). These are legal
investments under Montana law only ifthe investment is a general obligation of the agency and has a
fixed or zero -coupon rate and does not have prepayments that are based on underlying assets or
collateral (which makes them a derivative.)
1
LEGAL MONTANA INVESTMENTS - co n t.
U.S. Government Instrument Securities - Instrumentalities, also known as government sponsored
enterprises (GSEs), which are financial intermediaries established by the federal government to fund
loans to certain groups of borrowers, such as homeowners, farmers, and students. GSEs are privately
owned corporations with a public purpose. The most active issuers of debts securities are:
Federal Home Loan Banks (FNLB)
Federal Home Loan Mortgage Corporation (FI-ILMC of Freddie_Mac)
Federal National Mortgage Association (FNMA, or Fannie Mae
Federal Farm Credit Banks
Repurchase Agreements (REPO) - A repurchase agreement (REPO) is a simultaneous transaction
whereby an investor purchases securities (collateral) from a bank or a dealer for cash and the bank or
dealer contractually agrees to repurchase the collateral security at the same price (plus interest) at a
mutually agreed -upon future date. When the agreement is executed, the parties agree to a specified
interest rate. Collateral used most often are U.S, government securities. From an investor's point of
view, the best collateral instruments are short-term U.S. Treasury bills, which are liquid and not
subject to severe price changes.
Reverse Repurchase AZreements - Reverse repurchase agreements are the opposite of the transaction
undertaken through a repurchase agreement. In a reverse repurchase agreement, the investor owns
the securities (often referred to as collateral) and the financial institution or dealer temporarily
exchanges cash for the securities, for a specific period of time at an agreed -upon interest rate. These
agreements are often used to avoid liquidating securities to meet unexpected or immediate cash flow
requirements. This use is accepted by most public finance officers as a legitimate cash management
practice.
Depository Instruments - Investment instruments offered by financial institutions such as:
Interest bearing demand accounts - A negotiable order of withdrawal or money market
deposit account.
Certificates of deposit - A time deposit that bears a specified interest rate, for a specified
dollar amount, for a specified time period. These may be negotiable or nonnegotiable.
Sweep accounts - Accounts that "sweep" balances from all of the entity's accounts into one
account. These funds then are invested in overnight interest -bearing instruments, such as
money market mutual funds or repurchase agreements. Sweep accounts are excellent for
operational (checking) accounts to maximize earnings.
2