Budget: $3,500,000. You can go under this amount, but not exceed it. This is a three year project, so you must plan accordingly. Working with the RFP_Spreadsheet.xlsx, you will find worksheets with an employee list and room numbers from which to build your Employee Salaries worksheet. In addition to these two worksheets, your Spreadsheet will include the following 5 worksheets that you will create, with worksheet tabs colored and named accordingly:
1. Employee Salaries
2. Technology/supplies
3. Office Rental
4. Office parameters - this worksheet will include information listed below and from which you will build your Office Rental worksheet using multi-sheet references.
5. Summary (this sheet you will create last, but place first in your workbook)
Worksheet 1 - Employee Salaries - You will create the Employee Salaries worksheet with the following columns:
Table 1 - Employee Salaries worksheet
|
COLUMN
|
EMPLOYEE INFORMATION
|
|
2
|
Employee Name
|
|
3
|
Room number
|
|
4
|
Position title
|
|
5
|
Status
|
|
6
|
Base salary
|
|
7
|
Year 1 salary (Base salary multiplies by status)
|
|
8
|
Year 2 salary
|
|
9
|
Year 3 salary
|
Select from the Employee names worksheet your 20 employees and paste them in the Name column. Add from the room number worksheet, room numbers for the employees. Any list of names and numbers will do. This data forms the foundation for your Employee_Salaries worksheet.
Employees fit into the following categories.
1. At least six (6) fulltime, salaried employees;
2. Five (5), halftime (.5 ) employees;
3. Two (2) hourly, fulltime employees, paid $12.00/hour.
4. One (1) receptionist only
5. The remaining (6) distribution of staff is up to you - any combination of salaried, halftime and hourly.
6. Each employee gets a 3% increase in salary for year 2 and 3
Based on the types of positions you select, assign each employee a Position Title, Status (salaried, halftime, or hourly), and base salary. Remember, you have some flexibility in determining the number and types of position, but you must fit salaries within the three-year budget. Remember, your budget must cover technology and space rental too.
Table 2 - Positions Types and Salaries
|
Position Title
|
Salary range
|
|
1. Systems administrator
|
$50,000 to $60,000
|
|
2. Lead Programmer
|
$50,000 to $75,000
|
|
3. Lead Programmer 2
|
$40,000 to $55,000
|
|
4. Senior researcher
|
$65,000 to $85,000
|
|
5. Research assistant (part time)
|
$25,000 to $30,000
|
|
6. Database manager
|
$35,000 to $40,000
|
|
7. Database Programmer
|
$35,000 to 45,000
|
|
8. Web developer
|
$40,000 to $65,000
|
|
9. IT support technician
|
$28,000 to $32,000
|
|
10. Technical writer
|
$40,000 to $55,000
|
|
11. Receptionist
|
$25,000
|
|
12. Outreach/public relations
|
$30,000 to $42,000
|
|
13. Personnel Officer
|
$42,000 to $55,000
|
|
14. Project manager/grant developer
|
$65,000 to $72,000
|
You will include in the status column whether they are salaried (1) or halftime (.5). If they are hourly, you will need to calculate what their wage would be for the year.
Add three additional columns for the salaries for year 1, 2 and 3. Remember, salary in year 1 is the product of status and salary; year two is 3% greater than year 1; and year 3 is 3% greater than year 2.
Worksheet 2 -Technology/supplies-Your organizational budget will have the following characteristics:
1. Each of the 20 employees has at least one computer and or laptop
2. Desktop computers and laptops are purchased in year 1.
3. Servers are rented annually.
4. Miscellaneous/Salary & Expenses costs (printer paper and toner, long distance calls, etc.) between $10,000 and $20,000 per year.
5. Developers (programmers, web developers, database managers) require higher-end workstations;
6. project managers, receptionists, personal officers, require midlevel, standard desktop machines;
7. researchers, public relations and technical writers utilize mobile technology (laptops)
8. Rental fees for servers increase 3.5% each year.
9. You backup your data, paying per megabyte. (see table)
The technology you buy depends on your personnel. See table below for cost of specific technology. While backups fluctuate per/month, we will calculate backup costs per year.
Your Technology/supplies worksheet will have the structure below, with additional columns for years 2 and three.
Table 3 - Technology, Quantity and Cost
|
Technology
|
Quantity
|
Cost
|
|
Standard Workstations
|
(depends on staff)
|
$1,000
|
|
High-end workstations
|
(depends on staff)
|
$2,500
|
|
Laptops
|
(depends on staff)
|
$1500
|
|
File server
|
1
|
$6000/yr
|
|
Applications server
|
1
|
$6,000/yr
|
|
Web server
|
1
|
$4,000/yr
|
|
Router
|
1
|
$4,500
|
|
Switch
|
2
|
$3,000
|
|
Printers
|
4
|
$650/yr
|
|
Backups
|
- 465GB first year
- 1TB 2nd year
- 1.5TB 3rd year
|
- $.02/MB first year
- $.015 2nd year
- $.01 3rd year
|
Worksheet 3 - Office rental -Your organization requires at least 15 offices:
1. Research: Five (5) offices 10 feet x 10 feet
2. Data Processing: Three (3) offices are 12 x 7
3. Administrative: Two (2) offices are 10 x 22
4. Web/technical writing and outreach: Three (3) offices are 8 x 9
5. Information Technology: Two (2) Offices are 9 x 9
Worksheet 4 - Office parameters - The office parameters are on a separate worksheetso that you can reference the cost per square-foot when you are building your Office Rental worksheet.
1. The cost of rental per month is 1.25/sqft
2. The reception area = 300 sqft
3. IT room = 10' X 15'
4. Rent will increase by 2% for the second and third year
Worksheet 5 - Summary worksheet
The summaryworksheet is the first worksheet in the workbook, which includes totals from each of the three other worksheets (must use multi-sheet references), including one chart that represents the summary table.
Attachment:- rfp_spreadsheet.rar