IT Budget Worksheet
INSTRUCTIONS
MDE_Budget.xls Description: The IT Budget worksheet is an Excel spreadsheet that can be used to develop your annual IT operating budget. There are four different worksheet tabs. Instructions - This section includes simple simple instructions to help you develop a budget. More detailed insight is provided in the publication Developing an IT Business Plan in the IT Manager Development Series. Assumptions - A convenient place to maintain your assumptions assumptions for each budget item. item. A starter set is included to assist your efforts. Operating Budget - This worksheet is used to develop your monthly monthly IT revenues and expenses. Two line items are calculated automatically for you and inserted from two of the other worksheets, Salary and Consulting Fees. Salary - Used to list all employees employees and their salaries. The total is automatically plugged into the the Salary line item in the Operating Budget spreadsheet. Consulting Fees - List all consulting consulting engagements and associated costs planned for the year. The total line is automatically plugged into the Operating Budget line item for Consulting Fees. Make copies of this spreadsheet for other revenue and expense categories for which you need to develop detail backup. Insert the appropriate cell references into the Operating Budget cells to pull in the total expense line.
Instructions: As you develop your budget, it will help you later if you document the assumptions you use to create each budget line item. Use the tab named Assumptions to list them. Check the tab for examples
IT Budget Worksheet
INSTRUCTIONS
MDE_Budget.xls Step 8
Step 9 Step 10 Step 11
the Consulting expense line in the Operating Budget spreadsheet. Do a File Save. Research each line item in the Operating Budget spreadsheet and fill in the monthly amounts. Tips to help you in this process include: A. Use a % of salary figure and calculate the Employee Benefit expenses. Accounting or upper management can tell you what percentage to use. B. Anticipate peak workload times that require Temporary Labor , if any, and plug the dollars into the appropriate monthly cells. C. You can calculate Seminars and Training based on actual events you plan to spend money on. An easier method is to multiply a dollar factor times the number of employees that you have and spread the total cost over the 12 months. D. To calculate the Recruiting fees, multiply the annual salaries of newly hired employees you plan for by a percentage you normally see from recruiting companies (typically 15% to 25% of an annual salary). Place the fee in the month you expect to hire the new employee. You should add two to three more recruiting fees throughout the year for fees needed to replace existing employees that leave. E. Estimate Relocation fees and include in your budget if you expect a relocation. F. You should maintain a Computer Lease and WAN Lease log. Pull the numbers from your log and be sure to anticipate changes in your future needs. G. Hardware and Software Maintenance should be listed in a Vendor Contracts list. This tool is included in the IT Manager ToolKit. Anticipate changes, price increases, and look for annual billing items. H. With many expenses, you can estimate monthly expenses by reviewing past year's P&L statement trends for your department. Be sure to anticipate special situations that are required to handle new projects and initiatives during the new year. Compare with past year P&L actuals and validate budget line items for reasonableness. Review project plans for the next year to assess whether you have all exception costs planned for. Add additional adjustments as needed to maintain a conservative, yet realistic budget.
IT Budget Assumptions It helps to maintain a log of all of your budget assumptions. It will benefit you later whan you analyze your actual results. In addition, the assumptions that you develop for this budget will help reduce your budgeting time for next year's budget. These are sample ideas to give you a start in developing your own assumptions. Revenue: 1. Estimated product sales revenue per month. 2. Price increase planned for month of _________. 3. Average service revenue per month. 4. Planned revenue growth per _________
%
Expenses: 1. Salary raises projected by individual or a standard percentage average for each employee. 2. Recruiting expenses: $ per new employee. 3. Bonus Plans: Employee Name 1 2 3 4 5 Total
Bonus Plan
Estimated Bonus
0
8. Rent Office Location 1 2 3
Monthly Rent
0
Total 9. Computer SW/HW Maintenance Item 1 2 3 4 5 Total 10. PC Software Purchases Item 1 2 3
Comments
Maint $$
Assumption
Annual/Monthly (A/M)
Monthly Cost
Anniv. Month
IT Salaries - 2002
Sample Employee ($60K) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) Employee Name ($ ) New Employee start date New Employee start date New Employee start date Total Salaries
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
5,000
5,000
5,000
5,000
5,000
5,000
5,250
5,250
5,250
5,250
5,250
5,250
0
0
0
0
0
0
0
0
0
0
0
TOTAL
0
61,500
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
IT Consulting Fees - 2002 Jan
Feb
Mar
Sample - Network Security Audit
Consulting Project #1 Consulting Project #2 Consulting Project #3 Consulting Project #4 Consulting Project #5 Consulting Project #6 Consulting Project #7 Consulting Project #8 Consulting Project #9 Consulting Project #10 Total Consulting Fees:
Apr
5,000
0
0
0
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
TOTAL
5,000
0
10,000
0
0
0
0
0
0
0
0
0 0 0 0 0 0 0 0 0 0 0
IT Operating Budget
MDE Enterprises Budget for Fiscal Year: 2002 Department: IT Prepared by: Rev. Date: 9/26/2010 Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
YTD Total
Dec
REVENUE: Product #1 Product #2 Product #3
0 0 0 0 0
Service #1 Service #2 Service #3 TOTAL REVENUE Bad Debt Provision NET REVENUE EXPENSES: Salaries & Benefits: Salary Bonus & Commissions Employee Incentive
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0 0 0 0
0
0
0
0
0
0
0
0
0
0
0
0
0 0 0 0 0
Employee Benefits (_____%) Automobile Expense Temporary Labor TOTAL Salary & Benefits
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0 0
Other Expenses:
Seminars & Training Consulting Fees Legal Fees Other Professional Fees
0 0 0 0
Contracted Services Recruiting
0 0
Relocation Advertising
0 0
Marketing Materials Travel & Entertainment
0 0
Office Expense Telephone
0 0
Telephone Cellular
0 Page 9 of 10
Computer Lease/WAN Lease Repairs & Maintenance
0 0 0 0 0 0 0
Utilities Office Supplies Dues & Subscriptions Office Rent Postage General Insurance Taxes & Licenses Computer SW/HW Maintenance Computer Software (PC) Licenses TOTAL OTHER EXPENSES EBITDA
0 0 0 0 0
0 0
0 0
0 0
0 0
Page 10 of 10
0 0
0 0
0 0
0 0
0 0
0 0
0 0
0 0 0