assignment

profilevinilreddy
ITBudgetWorkbook_Student.xlsx
Home>Business & Finance homework help>Accounting homework help>assignment

IT Department Budget

Information Technology Budegt Summary
Department Revenues Last Years Budget This Years Budget % of Total IT Spend % Change from Last Year
Fees for IT Services Performed $31,785,300 $32,859,500 3%
Proceeds from Sales of Assets $135,700 $142,800 5%
Other $5,956 $3,187 -46%
Total Department Revenue $31,926,956 $33,005,487 3%
Totals: $31,926,956 $33,005,487 3%
Information Systems Department Expenses
Personnel Expenses
Full Time Employee Salaries w Benefits $201,789,532 $205,511,424 100.000% 2%
Bonuses & Other Compensation $1,950,000 $0 0.000% -100%
Consultants $2,738,500 $0 0.000% -100%
Training/Seminars $98,750 $0 0.000% -100%
Travel $17,530 $0 0.000% -100%
Employee Relocation $11,600 $0 0.000% -100%
Totals: $206,605,912 $205,511,424 100.000% -1%
Computing Equipment
Computing Equipment Costs- Capital $578,950 $0 0.000% -100%
Computing Equipment Costs- Expenses $603,985 $0 0.000% -100%
Totals: $1,182,935 $0 0.000% -100%
Software
Software Costs- Capital $987,537 $0 0.000% -100%
Software Costs- Expenses $503,798 $0 0.000% -100%
Totals: $1,491,335 $0 0.000% -100%
Enterprise Data Center Facility
EDC Facility Costs- Capital $638,223 $0 0.000% -100%
EDC Facility Costs- Expenses $110,587 $0 0.000% -100%
Totals: $748,810 $0 0.000% -100%
General & Administrative
Legal Council $5,600 $0 0.000% -100%
Utilities $11,895 $0 0.000% -100%
Rent $28,000 $0 0.000% -100%
Membership Dues (PMI, IEEE, Gartner) $275,300 $0 0.000% -100%
Office Supplies $2,040 $0 0.000% -100%
Insurance $44,850 $0 0.000% -100%
Telecommunication Expenses $3,700 $0 0.000% -100%
Parking Fees $17,500 $0 0.000% -100%
Miscellaneous $1,835 $0 0.000% -100%
Totals: $390,720 $0 0.000% -100%
Total Expenses $208,215,002 $205,511,424 -1%
Total Capital Costs $2,204,710 $0 -100%
Total Department Spending $210,419,712 $205,511,424 -2%
Total Department Budget (Net of Revnues) ($178,492,756) ($172,505,937) -3%

IT Personnel & Conntractor Support Staff

IT Department Personnel Monthly Worksheet

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 89 88 88 88 88 88 88 88 88 92 97 100 Contracted Support Staff

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2 2 2 3 3 2 2 2 2 3 3 3

IT Budget

Total Expenses Total Capital Costs 205511424.19999996 0

IT Department Budget Worksheet

IT Department Budget Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Totals
Personnel Expenses
Full-Time Employ Salaries (FLR) $17,066,375 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $17,319,345 $18,111,345 $18,569,595 $205,511,424
Consultant Staff $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Bonuses/Other Compensation $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Training/Seminars $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Travel Expenses $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Employee Relocation $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total Personnel Expenses $17,066,375 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $17,319,345 $18,111,345 $18,569,595 $205,511,424
Computing Equipment Cost-Captial Expenditures
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total Equpiment Costs - Capital $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Computing Equipment Costs - Operational Expenditures
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total Equipment Costs- Operational $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Software Costs - Capital Expenditures
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total Software Costs- Capital $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Software Costs - Operational Expenditures
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total Software Costs- Operational $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Enterprise Data Center Facility - Capital Expenditures
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total EDC Facilities Cost - Capital $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Enterprise Data Center Facility - Operational Expenditures
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total EDC Facilities Costs- Operational $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total EDC Costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
General & Administrative Expenses
Legal Council $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Utilities $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Office Space Rent $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Membership Dues (PMI, IEEE, Gartner) $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Office Supplies $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Insurance $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Telecommunication Expenses $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Parking Fees $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Miscellaneous $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total G&A Expenses $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
IT Budget Workseet Totals
Total Operational Expenses $17,066,375 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $17,319,345 $18,111,345 $18,569,595 $205,511,424
Total Capital Costs $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
Total IT Department Spending $17,066,375 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $16,805,595 $17,319,345 $18,111,345 $18,569,595 $205,511,424

G&A Work Sheet

Telecommunications Expenses
Telecom Expenses Estimated Costs Billing Frequency Occurrence
Telephone $300 Monthly Reoccurring
Cellular (Data Plans) $500 Monthly Reoccurring
Pagers $125 Monthly Reoccurring
Communication Lines $228 Monthly Reoccurring
Internet Access $2,500 Monthly Reoccurring
Miscellaneous $125 Monthly Reoccurring
Total Telecommunications Expenses $3,778
Office Supplies
Budget line Item Estimated Costs Billing Frequency Occurrence
General Supplies $65 Monthly Reoccurring
Media Costs $15 Monthly Reoccurring
Postage/Freight $45 Monthly Reoccurring
Paper $1,750 Monthly Reoccurring
Printer Cartridges $85 Monthly Reoccurring
Miscellaneous $75 Monthly Reoccurring
Total Office Supplies Expenses $2,035
Professional Subscription Expenses Quantity Unit Price Total Costs Billing Frequency Occurrence Billing Month
Gartner- CIO 1 $73,500 $73,500 Annually Reoccurring July
Gartner IT Leader 4 $50,300 $201,200 Annually Reoccurring February
IEEE Membership 5 $400 $2,000 Annually Reoccurring October
PMI Membership 8 $185 $1,480 Annually Reoccurring January
Total Membership/Subscription Expenses 18 $278,180
Note: Use Total Subscription Expense Figure and divide by 12 for budget worksheet
Other General Administrative Overhead Expenses
Budget Line Item Estimated Costs Billing Frequency Occurrence
Legal Council $475 Monthly Reoccurring
Utilities $1,025 Monthly Reoccurring
Office Space Rent $2,350 Monthly Reoccurring
Insurance $3,750 Monthly Reoccurring
Equipment Depreciation $10,350 Monthly Reoccurring
Parking Fees $1,500 Monthly Reoccurring
Miscellaneous $125

Software-Equipment-Services

Computer Equipment & Related Service Costs (Capital & Operational)
Equipment Item Type Category Expenditure Type Quantity Unit Price Total Price Project Name Business Unit Billing Frequency Occurrence Billing Month(s)
Dell Desktop Computers Hardware Lease Workstation 15 $125 $1,875 Desktop Refresh Phase 2 Finance & Accounting Monthly Reoccurring Each Month
Compaq Laptop Hardware Lease Laptop Computer 25 $145 $3,625 Desktop Refresh Phase 2 Finance & Accounting Monthly Reoccurring Each Month
Cisco Fast Ethernet Switches Hardware Purchase Network Equipment 10 $10,575 $105,750 Infrastructure Upgrades Information Systems One-Time Non-Reoccurring April
Cisco Routers Hardware Purchase Network Equipment 5 $45,750 $228,750 Infrastructure Upgrades Information Systems One-Time Non-Reoccurring June
Enterprise Storage Arrays Hardware Purchase Storage Equipment 3 $275,300 $825,900 Infrastructure Upgrades Information Systems One-Time Non-Reoccurring August
Microsoft Azure Cloud Storage Cloud-Based Services Infrastructure as a Service 1 $2,553 $2,553 Enterprise Data Center Expansion Information Systems Monthly Reoccurring Each Month
Servers Hardware Purchase Enterprise Applications 5 $18,750 $93,750 Employee Time Tracking Software Human Resources One-Time Non-Reoccurring February
Storage Tech LLC Services Systems/Solution Support Systems Support Agreements 1 $7,500 $7,500 N/A Information Systems Monthly Reoccurring Each Month
Cable Masters Inc. Systems/Solution Support Systems Support Agreements 1 $15,833 $15,833 Enterprise Data Center Expansion Information Systems Monthly Reoccurring Each Month
TechNet LLC Systems/Solution Support Systems Support Agreements 1 $29,375 $29,375 N/A Information Systems Monthly Reoccurring Each Month
$0 $0
Software & Related Service Costs (Capital & Operational)
Software Item Type Category Expenditure Type Quantity Unit Price Total Price Project Name Business Unit Billing Frequency Occurrence Billing Month
Kronos ETTS Software Software Purchase Commercial Off The Shelf-Software 1 $275,000 $275,000 Employee Time Tracking Software Human Resources One-Time Non-Reoccurring April
Kronos ETTS Maintenance Support Systems/Solution Support Systems Support Agreements 1 $55,000 $55,000 N/A Human Resources Annually Reoccurring December
O365 Enterprise Business Suite Software Lease Software as a Service 1 $20,835 $20,835 N/A Information Systems Monthly Reoccurring Each Month
Microsoft Server O.S. Software Purchase Commercial Off The Shelf-Software 5 $3,758 $18,790 Employee Time Tracking Software Human Resources One-Time Non-Reoccurring Februray
CAD PSIM Software Software Purchase Commercial Off The Shelf-Software 1 $575,300 $575,300 Engineering CAD Simulation Module Engineering One-Time Non-Reoccurring October
CAD Software Maintenance Systems/Solution Support Systems Support Agreements 1 $8,575 $8,575 N/A Engineering Monthly Reoccurring Each Month
ERP Software Maintenance Systems/Solution Support Systems Support Agreements 1 $10,500 $10,500 N/A Information Systems Monthly Reoccurring Each Month
ISO QA Software Software Purchase Commercial Off The Shelf-Software 1 $53,750 $53,750 ISO Compliance & Audits Quality Assurance & Control One-Time Non-Reoccurring May
ISO QA Software Maintenance Systems/Solution Support Systems Support Agreements 1 $1,100 $1,100 N/A Quality Assurance & Control Monthly Reoccurring Each Month
Enterprise Security Suite Software Lease Software as a Service 1 $10,800 $10,800 N/A Information Systems Monthly Reoccurring Each Month
SAP Finance Software Enhancement Systems/Solution Support Software Enhancements 1 $67,830 $67,830 Tax Legislation Software Enhancement Finance & Accounting Quarterly Reoccurring January, April, July,October
$0 $0
Enterprise Data Center Facilities Equipment & Service Costs (Operational)
Facilities Equipment & Service Item Type Category Expenditure Type Quantity Unit Price Total Price Project Name Business Unit Billing Frequency Occurrence Billing Month
General Building Repairs General Facility-Grounds Services Building Maintenance 1 $1,250 $1,250 N/A Information Systems Monthly Reoccurring Each Month
Emergency Generator Maintenance General Facility-Grounds Services Facilities Equipment 1 $1,483 $1,483 N/A Information Systems Monthly Reoccurring Each Month
Office Cleaning General Facility-Grounds Services Custodial or Pest Control 1 $2,300 $2,300 N/A Information Systems Monthly Reoccurring Each Month
Interior Painting General Facility-Grounds Services Building Maintenance 1 $28,500 $28,500 Enterprise Data Center Expansion Information Systems Semi-Annually Non-Reoccurring June, December
Phone Installations Systems/Solution Support Desktop Support Services 10 $58 $580 N/A Information Systems Monthly Reoccurring Each Month
Bio-Metric Access System Hardware Purchase Physical Security 1 $68,300 $68,300 Enterprise Data Center Expansion Information Systems One-Time Non-Reoccurring September
Security Cameras Hardware Purchase Facilities Equipment 3 $687 $2,061 Enterprise Data Center Expansion Information Systems One-Time Non-Reoccurring November
Electrical Power Systems Facilities Upgrades Facilities Equipment 1 $675,250 $675,250 Enterprise Data Center Expansion Information Systems One-Time Non-Reoccurring January
HVAC System & Controllers Facilities Upgrades Facilities Equipment 1 $11,800 $11,800 Enterprise Data Center Expansion Information Systems One-Time Non-Reoccurring March

Personnel Work Sheet

IT Department Personnel Monthly Worksheet Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Salary Benefits FLR
Chief Information Systems Officer 1 1 1 1 1 1 1 1 1 1 1 1 $225,386 $383,155 $608,541
Chief Technology Officer 1 1 1 1 1 1 1 1 1 1 1 1 $185,500 $315,350 $500,850
Chief Information Security Officer 1 1 1 1 1 1 1 1 1 1 1 1 $205,785 $349,835 $555,620 Other Pesonnel Expenditures
Enterprise Applications Director 2 1 1 1 1 1 1 1 1 1 1 1 $100,300 $160,480 $260,780 Budget Line Item Estimated Cost Billing Frequency Occurrence Billing Month(s)
Enterprise I&O Director 1 1 1 1 1 1 1 1 1 1 1 1 $108,375 $173,400 $281,775 Bonuses/Other Compensation $675,000 Semi-Annually Reoccurring January & December
Enterprise Architecture Director 1 1 1 1 1 1 1 1 1 1 1 1 $110,300 $176,480 $286,780 Training/Seminars $8,550 Monthly Non-Reoccurring Each Month
Procurement & Contracts Director 1 1 1 1 1 1 1 1 1 1 1 1 $97,500 $146,250 $243,750 Travel Expenses $1,550 Monthly Reoccurring Each Month
Telecommunications Manager 1 1 1 1 1 1 1 1 1 1 1 1 $83,400 $125,100 $208,500 Employee Relocation $7,650 Semi-Annually Non-Reoccurring February & October
Application Manager 4 4 4 4 4 4 4 4 4 4 5 5 $76,800 $115,200 $192,000
Security Manager 3 3 3 3 3 3 3 3 3 3 3 3 $93,700 $140,550 $234,250
Network Manager 3 3 3 3 3 3 3 3 3 3 3 3 $95,600 $143,400 $239,000
Data Center Manager 3 3 3 3 3 3 3 3 3 3 3 3 $85,300 $127,950 $213,250
Business Relationship Manager 4 4 4 4 4 4 4 4 4 4 4 4 $70,500 $105,750 $176,250
Solutions Manager 2 2 2 2 2 2 2 2 2 2 2 2 $63,700 $95,550 $159,250
Helpdesk Manager 2 2 2 2 2 2 2 2 2 3 3 3 $57,800 $86,700 $144,500
Data Storage Manager 2 2 2 2 2 2 2 2 2 2 2 2 $73,200 $109,800 $183,000
GIS Manager 1 1 1 1 1 1 1 1 1 1 1 1 $62,500 $93,750 $156,250
Service Manager 4 4 4 4 4 4 4 4 4 4 4 4 $63,800 $95,700 $159,500
Application Architect 2 2 2 2 2 2 2 2 2 2 2 2 $86,500 $129,750 $216,250
Database Architect 2 2 2 2 2 2 2 2 2 2 2 2 $85,300 $127,950 $213,250
Security Architect 2 2 2 2 2 2 2 2 2 2 2 2 $92,500 $138,750 $231,250
Data Architect 2 2 2 2 2 2 2 2 2 2 2 2 $83,700 $125,550 $209,250
Network Architect 2 2 2 2 2 2 2 2 2 2 2 2 $92,500 $138,750 $231,250
Telecommunications Architect 2 2 2 2 2 2 2 2 2 2 2 2 $83,700 $125,550 $209,250
Enterprise Soulutions Architect 2 2 2 2 2 2 2 2 2 2 2 2 $97,500 $146,250 $243,750
Business Analysts 5 5 5 5 5 5 5 5 5 5 6 6 $53,200 $79,800 $133,000
Security Analyst 4 4 4 4 4 4 4 4 4 5 6 6 $51,700 $77,550 $129,250
Systems Analyst 3 3 3 3 3 3 3 3 3 3 3 3 $50,300 $75,450 $125,750
Database Administrator 3 3 3 3 3 3 3 3 3 3 3 3 $67,800 $101,700 $169,500
Systems Administrator 3 3 3 3 3 3 3 3 3 3 4 4 $47,800 $71,700 $119,500
IT Policy Specialists 2 2 2 2 2 2 2 2 2 2 2 2 $51,300 $76,950 $128,250
Senior Application Developer 5 5 5 5 5 5 5 5 5 5 6 7 $87,300 $130,950 $218,250
Junior Application Developer 8 8 8 8 8 8 8 8 8 9 9 10 $63,500 $95,250 $158,750
IT Technician 5 5 5 5 5 5 5 5 5 6 6 7 $32,500 $48,750 $81,250
Full-Time IT Employees Totals 89 88 88 88 88 88 88 88 88 92 97 100 $2,986,545.50 $4,635,050 $7,621,595
Contracted Support Staff Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Salary
IT Security Auditor 0 0 0 0 0 0 0 0 0 1 1 1 $95,750
Supply Chain System Integration Consultant 0 0 0 1 1 1 1 1 1 1 1 1 $123,500
SAP Financial Module Consultant 1 1 1 1 1 0 0 0 0 0 0 0 $117,500
Storage Area Network Consultant 0 0 0 1 1 1 0 0 0 1 1 1 $107,300
Systems Support Engineers 1 1 1 0 0 1 1 1 0 0 0 $112,500
Contracted Support Staff Totals 2 2 2 3 3 2 2 2 2 3 3 3 $556,550

References

Type Category Business Unit Expenditure Type Projects Billing Frequency Occurrence
Software Purchase Network Equipment Executive Offices CAPEX Desktop Refresh Phase 2 Monthly Non-Reoccurring
Hardware Purchase Enterprise Applications Human Resources OPEX Infrastructure Upgrades Bimonthly Reoccurring
Hardware Lease Commercial Off The Shelf-Software Finance & Accounting Employee Time Tracking Software Quarterly
Software Lease Software as a Service Sales & Marketing Engineering CAD Simulation Module Semi-Annually
Equipment Maintenance Infrastructure as a Service Engineering Enterprise Data Center Expansion Annually
Systems/Solution Support Workstation Manufacturing & Production N/A One-Time
Building Maintenance Laptop Computer Quality Assurance & Control ISO Compliance & Audits
Facilities Upgrades Tablet Devices Information Systems Tax Legislation Software Enhancement
General Facility-Grounds Services Mobile Phone Customer Support
Cloud-Based Services Multi-Function Print Device Legal
Other Server Procurements & Contracts
Other Storage Equipment
Peripherals
Telecommunications Equipment
Facilities Equipment
Custodial Services
Electrical Power System
Physical Security
Emergency Power
Software Enhancements
Building Maintenance
Infrastructure Support Services
Software Development Services
Desktop Support Services
Systems Support Agreements