R18-179 1 RESOLUTION NO. R18-179
2
3 A RESOLUTION OF THE CITY OF BOYNTON BEACH,
4 FLORIDA, APPROVING TASK ORDER NO. UT-1C-02 WITH
5 CAROLLO ENGINEERS,INC.,IN ACCORDANCE WITH RFQ NO.
6 046-2821-17/TP, GENERAL CONSULTING SERVICES, FOR
7 WORK ON MIGRATING THE UTILITIES MANAGEMENT
8 OPTIMIZATION PROGRAM TO AN IMPROVED PLATFORM
9 AND OTHER UPGRADES TO THE SYSTEM NOT TO EXCEED
10 $184,900.00; AND PROVIDING AN EFFECTIVE DATE.
11
12 WHEREAS, Carollo Engineers was awarded a General Consulting Services Contract,
13 Scope C, on August 7, 2018 in accordance with RFQ No. 046-2821-17/TP; and
14 WHEREAS, the update of the Utilities Management Optimization Program will be
15 implemented through two major phases;
16 WHEREAS, staff is recommending that the City Commission approve Task Order No.
17 UT-1C-02 with Carollo Engineers, Inc., for work on migrating the Utilities Management
18 Optimization Program to an improved platform and other upgrades to the system in an amount
19 not to exceed $184,900.00 utilizing the City's existing contract RFQ 046-2821-17/TP
20 "GENERAL CONSULTING SERVICES".
21 NOW, THEREFORE, BE IT RESOLVED BY THE CITY COMMISSION OF
22 THE CITY OF BOYNTON BEACH, FLORIDA, THAT:
23 Section 1. The foregoing "Whereas" clauses are hereby ratified and confirmed as
24 being true and correct and are hereby made a specific part of this Resolution upon adoption
25 hereof.
26 Section 2. The City Commission of the City of Boynton Beach,Florida does hereby
27 approve Task Order No. UT-1C-02 with Carollo Engineers, Inc., for work on migrating the
28 Utilities Management Optimization Program to an improved platform and other upgrades to the
29 system in an amount not to exceed $184,900.00 utilizing the City's existing contract RFQ 046-
C:\Users\StanzioneT\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.1E5\52FYTEOE\Carollo Engineers_Task Order_No_UT-1C-02=_Reso.docx
1 2821-17/TP "GENERAL CONSULTING SERVICES", a copy of the Task Order is attached
2 hereto as Exhibit"A".
3 Section 3. This Resolution shall become effective immediately upon passage.
4 PASSED AND ADOPTED this 4th day of December, 2018.
5 CITY OF BOYNTON BEACH, FLORIDA
6
7 YES NO
8
9 Mayor—Steven B. Grant v
10
11 Vice Mayor—Christina L. Romelus
12
13 Commissioner—Mack McCray ✓
14
15 Commissioner—Justin Katz
16
17 Commissioner—Aimee Kelley
18
19 c,
20 VOTE 5—0
21 ATTEST:
22
23 ��'�I
24 Z, ,
25 Judit . Pyle, CMC
26 City erk
27
28
29
30 (Corporate Seal)
31
32
OY N TU''�
1020 : -
C'\Users\StanzioneTAppData\Local\Microsoft\Windows\Temporary Internet Files%Content.IE5\52FYTEOE\Carollo_Engineers_Task_Order_No_UT-1 C-02__Reso.docx
1-) vs -A-Ic\
ca ' ,
Engineers...Working Wonders With Waters
2056 Vista Parkway.Suite 400,West Palm Beach,Florida 33411
P.561.868.6400 F.561.868.6401
November 16,2018
Mr.Colin Groff,Assistant City Manager
City of Boynton Beach
124 East Woolbright Road
Boynton Beach,FL 33435
Subject: Fee Proposal for Utilities Management Optimization Model Update—Phase 2A(UMOP-2A):
Task Order Number UT-1C-02
Dear Mr.Colin:
Per your request,Carollo Engineers in association with McGovern McDonald Engineers(MME)are pleased to
submit this proposal to the City of Boynton Beach Utilities(City)for developing the Utilities Management
Optimization Model—Phase 2A, Platform Migration.
This letter proposal summarizes the scope of services, budget,and schedule of this work.If acceptable,this
letter may be used to establish an agreement between the City and Carollo for the subject effort.
Purpose
The focus of this update effort for UMOP is in building upon the initial success of the initial Utilities
Management Optimization Model (UMOP)software by greatly improving overall speed and
performance, and fully integrating the engineering and financial planning functions into one seamless
management tool
Project Phasing
The update of UMOP will be implemented through two major phases: Phase 2A,and Phase 2B.
Phase 2A is the first of the two phases,and will migrate the UMOP model software graphic user interface
from Tableau to Excel 2016/Power Bl.This powerful Excel update has recently been introduced,and is now
rapidly becoming the industry standard for advanced financial and engineering computation models.
Specifically,this new standard is allowing for extremely sophisticated and large(5+million records)financial
investing and engineering and process optimization models.In addition to more computational speed and
power, it is significantly cheaper and easier to program using MS Excel 2016/Power BI than Tableau,or other
post-processing GUI's.
This migration of the operating and user interface platforms will:1)greatly improved run speeds, 2)simplify
the process of changing programming features and adding functionality,3)allow the ability to quickly
import and integrated City engineering,financial,and monitoring data,and 4)will streamline the ability to
add automated-master planning in Phase 2B.
Also in Phase 2A,as part of the migration from Tableau to Excel 2016/Power BI,significant programming
improvements will be made.These include refinement of existing features,and addition of new features and
functionality.These include:
1. Improving the use of inflation adjustments to show impacts on costs only,so that the"gap"
between revenues and costs can be viewed,and appropriate adjustments in rates can be made
2. Fully incorporating the changes in escalation of capital costs• ns of OP cas
needs,and on rates
UMOP Phase 2 TO.docx WAT E R
OUR FOCUS
OUR BUSINESS
OUR PASSION
Mr. Colin Groff,Assistant City Manager
City of Boynton Beach
November 16, 2018
Page 2
3. Improving the ease of revising O&M and capital cost allocations,and allocations to billable
constituents by user class
4. Ability to more easily implement incremental, algorithm-driven rate adjustments
5. The ability to more easily set reserve level minimums and maximums to help preserve a"pay as
you go"strategy and to help guide the need and timing of new debt issuances.
Phase 2B will fully integrate the engineering and financial linkages needed to allow for automated master
planning functionality.This functionality automatically shift the need and timing of projects(along with
projected annual capital and O&M costs,and projected rates and charges)as population projections are
updated;as new treatment requirements are implemented;and as R&R projects approach the end of their
useful lives.
This capability will allow the City to stay current with their Capital Improvement Programs(CIP)cost
projections,and to better assess the need and timing of projects for wastewater,water,and stormwater
utilities and projected operating budgets.
To realize these new capabilities,new programming will be added during Phase 2B to create CIP projects by
simply updating the four primary inputs that drive the need and timing of new/rehabilitated facilities:
1)population projections, 2)future regulatory requirements,and 3)condition assessment(which drives
repair and replacement requirements), and 4)the adoption of new,more efficient technologies that improve
performance and reduce costs.
Phase 2A Goals.The primary goal of Phase 2A is to migrate the graphic user interface from Tableau to
Excel 2016/Power BI.This will make the existing model run faster, improve programming flexibility,and will
also achieve better,faster data import and management capabilities. In addition, Phase 2A will add new
features and functionality.Both of these goals are the first step,and will prepare the model for the Phase 2B
enhancements.
Phase 2A Development Approach.The project team will conduct a series of mini-working sessions with the
Boynton Utilities management and staff.This will expedite development of the Phase 2A software migration
and enhancements,while delivering a better custom user interface.
Phase 2A-Scope of Services
1. Programming Development Working Sessions.Conduct a series of six(6)mini-working sessions
with Boynton management team and staff.These will be relatively short(-2 hr)hands-on working
sessions where the MME/CE team will run the developing 2A computer model in parallel with staff
running the older UMOP model.This will allow greatly improve communications between Boynton
and the consultant team in converging on the desired model"bug"fixes,enhancements, and the
look and feel of the new model
2. Software Platform Migration.The purpose of this task is to re-code and migrate the existing UMOP
operating system to a more stable, robust,and user-friendly software platform.The new platform
will be MS Excel 2016/Power BI with its Power Query and Power Pivot capability.
a. Platform Migration. Perform a line-by-line code conversion(where applicable)from the
existing UMOP version(with Excel/Tableau structure),to fully integrated"back-end"and
graphic user interface"front-end".
carollo.com
Mr.Colin Groff,Assistant City Manager
City of Boynton Beach
November 16, 2018
Page 3
b. Algorithm Development.Create new,and modify existing algorithms,to the extent required to
fix"bugs",add selected enhancements,and to redesign the working interface to add flexibility
to smoothly incorporate the Phase 2B work to fully integrate the master-planning and project
development feature.
c. Calibration/Sensitivity Analysis.The purpose of this task is to calibrate the final Working
UMOP:
i. Develop a reasonable range of expected high and low("bookend")values for the major
inputs and assumptions in the model.
ii. Considering the expected ranges,create scenarios to be evaluated by the Model.
iii. Evaluate the results of the scenario runs to identify general system-wide correlations for the
major input variables,and to check for reasonableness and accuracy.
3. Improved and New Features and Functionality.As part of the migration to MS Excel 2016/Power BI,
make improvements and add programming and functionality.These new features and
functionalities include:
a. Variable Cost Allocations.The ability to make changes in the allocations of costs for fixed and
variable costs for each utility will be added.Also, improve the ability and ease of assigning the
allocation of project capital costs to new and existing users.
b. Improve Integrated Rate Adjustments.Connect and link changes in the annual percentage
rate increases for all four utilities(water,wastewater,stormwater,and recycled water),so that a
change in one reflects in an overall change of the cost-weighted average of all four rates.
c. Automated Rate Adjustments.This includes model improvements to more easily adjust for
changes in budget vs.actual,and other improvements to achieve fully automated rate setting.
For example,the ability to more easily identify and adjust"pass thru"increases for power costs
and consumables.
d. Improve Inflation Adjustment Function.This change will allow for the user to first escalate
projected capital and/or O&M costs,and to compute the impact upon net revenues.Then,as
desired,the user can also increase rates, if needed.
e. Improve Setting Allowable Reserve Levels.Improve the ability to set minimum and maximum
financial reserves for:1)operating reserves, (with the ability to set the minimum and maximum
number of months of operating costs that will be held in the operating reserve),and 2)capital
project reserves,(with the capability to set minimum and maximum reserve levels for capital
facilities).
f. Other Features,As Requested.This is a budget-driven subtask to provide for flexibility to
make improvements as yet unidentified, based on available budget remaining.
4. Staff Training&Tutorials.The purpose of this task is to develop a Quick Start Guide,and to conduct
a training session for City staff.
a. Develop a"Quick Start Guide"that provides a step-by-step start up and run exercise,and
provided responses to commonly asked questions.Submit a draft to the City for review.
Incorporate comments, and submit a final Quick Start Guide.
b. Provide one six-hour(6 hr)training session at the Boynton Beach Utilities Department on the
new features and functions of the updated UMOP.
5. Documentation&Summary Presentation
a. Provide a written narrative summary of the software design intent,model structure and
workflow,major assumptions,and key features and functionality of the UMOP 2
carollo.com
Mr.Colin Groff,Assistant City Manager
City of Boynton Beach
November 16, 2018
Page 4
b. Develop a PowerPoint graphical presentation summarizing the features and benefits of the new
UMOP software update.The intended audience is for City management and Commission level
presentations.Submit a draft PowerPoint presentation to the City for review. Incorporate City
feedback and review comments,and submit a final presentation.
6. Project Management. Monthly progress reports,tracking and managing the budget variance based
on actual versus budgeted,maintaining the overall schedule,and coordination of subconsultants.
a. On-going coordination of team,drive for innovation,and creation of"work around"solutions to
achieve project goals.
b. Maintain budget,schedule,overall QA/OC,and assure responsiveness to the City.
c. Submit monthly progress reports.
Schedule
Time is of the essence.The projected schedule for completion of the study is to be before January 31,2019.
Budget and Compensation
Work will be performed as described under a lump sum basis,and will be billed based on percent complete.
The budget is presented in the following table, including all labor and other direct costs.
Task No. Description Hours Budget($000)
1 Program Development Working Sessions 197 $66.4
2 Software Platform Migration&Calibration 259 $62.5
3 New Features and Functionality 67 $17.0
4 Staff Training/Tutorials 103 $19.3
5 Documentation&Summary Presentation 96 $13.5
6 Project Management 24 $6.2
TOTALS 746 $184.9
The attached table details labor rates and hours by proposed staff.Work shall not be performed in excess of
the not-to exceed ceiling without written authorization from the designated City Project Manager.
Please review,and feel free to contact us should you have any comments or clarifications.Thank you for this
opportunity to work with you.
Sincerely,
CAROLLO ENGINEERS, INC.
Elizabeth Fujikawa, P.E.
Vice President
EF:cjp
Enclosures:UMOP 2A Budget
carollo.com