Automated Fee Proposal Workshop

Automated Fee
Proposal Workshop
P R ESENTED BY JE N NI FER K I N G
F LOR I DA DE PA RTMENT OF T R A NSPORTATION
OF F I CE OF P ROF ESSIONA L S E RVICES
M A RCH 1 1 , 2 0 1 4
What is an Automated Fee Proposal (AFP)?
It is a spreadsheet that...
1. Standardizes the proposal process statewide.
2. Streamlines the cost proposal process while reducing
calculation errors.
3. Allows offline proposal preparation.
4. Provides a method for uploading fee proposal data into CITS
while minimizing data entry errors.
2
FDOT Website (www.dot.state.fl.us)
3
Agency Resources / Office Listing
4
Procurement Office Website
5
Procurement Office Website –
Index
6
Negotiating Professional Services
Contracts
7
Negotiations Handbook
8
Professional Services Reports Average Wage Rate
9
Professional Services Reports
10
Professional Services Reports
11
Procurement Office Website –
Professional Services Forms
12
Professional Services Forms
Important Forms
• AFP Information Transfer File
• AFP Spreadsheet
• AFP User’s Guide
• AFP Text Length Verification
Tool
13
Automated Fee Proposal User’s Guide
 Minimum software requirements:
1. Windows 98 or greater operating
system.
2. Excel 2000 or greater
3. Compression software such as
WinZip
4. Internet Access
14
Key things to remember:
 Do not attempt to delete or add cells to columns or rows. Cells will either:
Require the input of new information;
 Contain drop down menus with pre-populated information;
 Contain formulas that should not be altered.

 Never use a space to clear a cell!
 Never delete or add cells, columns or rows. This can result in an error in
the transferring of information.
 Blue cells contain drop down menus.
 Yellow cells are to be typed in.
 White cells contain formulas.
15
Key things to remember:
 To Download the AFP, right click on the
file and Save Target as. This will prompt
you with a new dialog box where you
can choose the location to save the
document.
 TIP: Every time you download (save
target as) the AFP file from the web (with
Macros) save it to your computer using a
unique naming convention (i.e...project
#, consultant, or project description.)
16
Macros
 If you still
cannot
open the
file, ask
your
in-house
computer
specialist to
check the
network
security
settings.
 Once you have
opened the file
(with Macros)
save it to your
computer using
a unique
naming
convention
(i.e...project #,
consultant, or
project
description.)
17
Let’s get started!
 Let’s open the Excel workbook labeled:

Rate Info Transfer – Florida Keys
(Florida Keys is fictitious firm that will
be used as the Prime Consultant for
this example.)
18
Consultant Information
 FEID – Federal Employer Identification Number
 These are called Worksheets.
19
Consultant Information - SunBiz
 To obtain your firm’s FEID number, visit www.SunBiz.org.
 Select the link to Look up a Business Name.
 Then, select the link to Inquire by Name.
 Next, look for the FEI/EIN Number.
 On the AFP, type an “F” in front of the FEID number.
20
Consultant Information
Types of Consultants:
P = Prime
S = Subconsultant
21
Consultant Information
Overhead: Indirect costs which will include fringe benefits.
22
Consultant Information
Facilities Capital Cost of Money: Otherwise known as FCCM.
23
Consultant Information
Operating Margin: An operating margin includes profit but not
exclusively profit. It’s also a way for the Consultant to recover
disallowed costs.
24
Consultant Information
Direct Expense: Direct expenses are project specific costs.
25
Consultant Information
Tip: Clicking
on the
Info box willInfo
update
newly
information
andadded
advance
TIP:
Clicking
onUpdated
the Updated
box
willadded
update
newly
the workbook to the next worksheet.
information and advance the workbook to the next worksheet.
26
Employee Worksheet
 Enter all employees
at firm and
corresponding
hourly rate.
 Selections should be
made from drop
down menus when
available.
 TIP: Building a template for your company may save
time from data entry.
 Click Updated Info
when sheet is
complete.
27
Loaded Rates
 Enter Item, Measure
and proposed hourly
rate.
 Click Updated Info
when sheet is
complete.
28
Expenses
 Enter Item,
Unit Cost,
Unit of
Measure and
No. of Units.
 Click Updated
Info when
sheet is
complete.
29
Geotech Rates
 Select items.
 Enter unit of measure and hourly
rate.
 Click Updated Info when sheet is
complete.
30
Transfer Sheet
 Click Updated Info when sheet is complete.
31
Work Effort
 Select Mid from the drop down menu.
 Enter Hours.
 Click Updated Info when sheet is complete.
32
DBE Information (Optional)
33
DBE Information (Optional)
 NAICS – North American Industry Classification System
http://www.dot.state.fl.us/equalopportunityoffice/
34
Equal Opportunity Office, DBE Directory
 Select DBE Certification under
Most Requested.
 Select DBE Directory under the DBE
Certification Links.
 Select DBE Directory.
35
DBE Directory
 Select Certification Type
 Enter Firm Name in Vendor Search.
 Select Location Availability.
 Click Search
36
DBE Directory - Search Results
 Vendor Name
 Contact Information
 NAICS Certification
37
Subconsultant Information (Optional)
 When complete, click Go Back to Work Effort
IMPORTANT – CLICK SAVE!
38
Work Effort Sheet
 Before you use the “Create Transfer File” button, you must first
create a folder in your C:\ Drive named AFP (C:\AFP)
 If you do not do this, you will receive an error when trying to
use the Create Transfer File Button!
 The instructions on the next page are from the User’s Guide.
39
Creating a Folder for the Transfer File
 Now you will have a file
named Rate Info
Transfer (Backup).xls
 You can save this file as
a different name and
send this to the Prime.
(For this example, we
are “pretending to” be
the Prime.)
 Let’s rename the file
Prime.xls
 Note: The
Updated Info
button will not
work in the Rate
Info Transfer
(Backup) file.
However, you can
manually move
through sheets.
40
Change Rate Info Transfer(Backup).xls
to Prime.xls
 Let’s complete the same
steps for the Rate Info
Transfer – Professional
Services Staff.xls file.
 Please add one or two
people to the
employees tab.
 Be sure to save your
changes.
41
Create Transfer File
 Once you click Create Transfer File, please save this as a different name.
 Let’s save it as Professional Services Staff.xls
42
Quick Break?
43
AFP for submittal
 Now we are ready to combine all “mini AFPS” into the main AFP
which will be submitted to FDOT.
44
AFP
 Let’s start with a new Automated Fee Proposal Spreadsheet.
45
Elements
 Clicking Protect prior to entering information
within the workbook will secure any cells
containing formulas.
 As a reference, the AFP User’s Guide can be
accessed by selecting the Guide box on the
Elements tab.
 Any information entered on the Elements
worksheet will appear in the Comp. Elements
drop down menu found on both the Employee
and Loaded worksheets.
46
Consultant Information
 Go to Prime.xls and
select information
from the Consultant
Info Sheet (i.e. cells
C7-K7).
 Copy this information
 Select Cell B14 on the
main AFP on the
Consultant Sheet.
47
Consultant Information
 Once you have
copied the
information, then
PasteSpecial
Values in Cell B14
in the main AFP
(AFPFice.xls) on
the Consultant
Sheet.
48
Consultant Information
 Requirements for a successful upload into CITS:




Ad Number and Contract Number
FM Number
Planned Start and End Dates
District Number
49
Consultant Information
TIP: When completing an AFP for a new Contract, enter the Advertisement No. in the Ad No. cell
and in the Contract No. cell. Be sure District No. aligns with the advertisement. If completing
multiple AFPs for a single Contract, ensure the Upload Seq # correlates with each new AFP.
50
Employee Information
 Go to Prime.xls and select the
information from the Transfer Sheet
(i.e. cells B5-H9) Copy this
information.
 Once you have copied
the information, then
PasteSpecialValues
in Cell F8 in the main
AFP (AFPFice.xls) on the
Employee Sheet.
51
Employee Information
 Select Consultant from drop down menu.
52
Employee Information
 Select Compensation Element from drop down menu.
53
Employee Information
 Enter weight factor, if applicable.
 If using weight factor, percentages must add up to 100%.
54
Other Direct Expense
 Other Direct Expense items are only entered when items have
been excluded from the Direct Expense audited rate.
 Select the firm name from the drop down menu.
 Copy and paste the highlighted cells from Prime.xls.
55
Reminder!
 Don’t forget to select Updated Info when complete.
56
Loaded Rates
 Select Consultant, Unit of Measure and Comp. Element from drop down menus.
 Enter Item, Proposed Rate and Weighted Factor in cells.
 Click Updated Info when sheet is complete.
57
Loaded Rates
 Go to Prime.xls and select the highlighted cells
from the Transfer Sheet. Copy this information, and
paste it in the AFPfice.xls beginning with ITEM.
 Note that the Average $ will show as #N/A until a
Consultant is selected from the dropdown menu.
 Do not forget to select a Comp. Element!
58
Unloaded Rates
 Enter number of hours .
 Select individuals that are eligible for overtime.
 Click Updated Info when complete.
59
Overtime
60
Work Effort - Loaded
 Enter Number of Hours.
 Click Updated Info when complete.
61
Fee Calculations
62
Fee Summary
63
Zipping the AFP
Once you have reviewed the file and are confident it is accurate, you
then would “Zip” the file using WinZip. The “Zipped” file would then
be sent to the district representative.
64
Questions?
65
Future Questions
 First review your AFP Users Guide.
 Contact your district representative. Many of the questions may
have more to do with the contract method than the AFP.
 If the district representative cannot answer your question or
correct the problem then they will forward you to me.
 If the problem is related to mechanics of the spreadsheet,
I will work with Angela Matiyow to resolve.
66
Contact Information
(850) 414-4476
(850) 414-4481
[email protected]
[email protected]
67
District Contacts
 D1 – Kim Cocking
 Kim – 863-519-2440
 D1 – Jamie Brabant
 Jamie – 862-519-2302
 D1 – Ashley Melton
 Ashley – 863-519-2752
 D2 – Robert Williams
 Robert – 386-961-7568
 D3 – Ranae Dodson
 Ranae – 850-415-9333
68
District Contacts
 D4 – Kereisha Ottey
 Kereisha – 954-777-4685
 D5 – Sonny Bryan
 Sonny – 386-943-5523
 D6 – Tiondra Wright
 Tiondra – 305-470-5403
 D7 – José Vazquez
 José – 813-975-6453
 TPK– Judi Fernandez
 Judi – 407-264-3040
 Central – Jennifer King
 Jennifer – 850-414-4476
69
70