Question: 1. Background: IT managers work with senior level IT leadership to submit an annual budget based on the business and IT strategies, tactical plans, operational plans and approved initiatives IT project portfolio. Hence, in this scenario the CIO has provided you with the IT budgeting workbook and asked you frame up next year's IT budget for final review and approval.
2. Requirements & Deliverable: This is not a group project assignment. It is to be completed and submitted by each student individually. Carefully, read the Project #3 Guideline Document. Use the IT Budget Workbook provided by the professor. Complete the Project #3 assignment as instructed and submit before or on the scheduled due date. Your deliverable will be the completed IT Budget Workbook; must be a M.S. Excel File(working spreadsheet) that is uploaded into Moodle using the designated link.
Note: Any manual entries or overwriting of pre-calculated fields on the work sheets or IT Department Budget dashboard to force the expected results outlined in section 5 of this document; will result in a failing grade for this assignment.
3. The IT Budgeting Workbook contains the following worksheets (Tabs):
a. IT Department Budget
b. IT Department Budget Worksheet*
c. G&A Worksheet
d. Software-Equipment-Service Worksheet*
e. Personnel Worksheet
f. References
Note: * Denotes that these are the only worksheets that will be modified to create the budget. All worksheet will need to be referenced to make the appropriate entries in the IT Department Budget Worksheet and successfully create the IT Department budget.
4. IT Budget Workbook Tabs and Instructions:
a. IT Department Budget Tab: This is the IT Budget Dashboard which will self-populate when the other worksheets have been completed. You will use the dashboard to provide your overview of the budget for the CIO.
b. IT Department Budget Worksheet Tab: This is the primary worksheet used to formulate the IT budget. You will complete all of the IT budget sections (e.g., Personnel, Computing Equipment, Software, Enterprise Data Center, and General & Administrative)by entering the appropriate dollar values for each month. You will need complete and/or use the other worksheet tabs in the IT Budget workbook (e.g., G& A, Personnel, and Software-Equipment-Services) to properly complete the IT Department Budget Worksheet. Please note the following:
i. You will enter budget line items in Column "A" Rows (specifically 13-17; 21-27; 31-36; 40-46; 50-54; and 58-63) under each budget category where there are blanks referencing information outlined in the other corresponding worksheet tabs. Note: you may have one blank row in some budget sections; this is okay and part of the challenge for you to determine.
ii. To determine which equipment, software, and EDC facilities budget line items fall under capital or operational expense sections; you will need to determine the correct "Expenditure Type" by first completing the Software-Equipment-Services worksheet (Specifically: Column D in each of the tables, selecting either CAPEX or OPEX and then make the corresponding budget line item entry and amount into the IT Department Budget Worksheet. Also you do not need to add or change any of the remaining fields in these tables on this worksheet, they have already been prepopulated with the essential information for you to fill out the IT Department Budget Worksheet.
iii. You will enter the correct dollar amounts (fields already formatted) for each budget line items in the months (Jan - Dec / Columns B-M) when the expense will occur during the budget year. Specifically cells located in Columns B-M items in Rows 4-9; 13-17; 21-27; 31-36; 40-46; 50-54; 58-63; and 68-76 under each budget category where there are $0 values referencing information outlined in the other corresponding worksheet tabs. Note: you may have one or more $0 values contained in a row in some budget sections. Hint: not all expenses reoccur on a monthly basis; use the information already outlined in the corresponding worksheet to make the correct determination (e.g., amounts, billing frequency, and billing months).
iv. All the cells "Total" fields in Column N for each row are calculated fields and already formatted. Do nothing in this column.
v. The IT Budget Worksheet Totals below the green highlighted row; are calculated fields and already formatted. Do nothing in the cells contained in Columns A-N: Rows 78, 80, 81, 82, 83, and 84 in this worksheet.
vi. The all of the G&A expense budget line items have already been entered into the IT Department Budget Work Sheet (Column A: Rows: 68 - 76). The cells contained in Columns B - N: Row 77; (Total G&A Expenses) under G&A section are calculated fields and already formatted. Do nothing in this row.
vii. The all of the Personnel expense budget line items have already been entered into the IT Department Budget Work Sheet (Column A: Rows: 31 - 36). The cells contained in Columns B-N: Row 10 (Total G&A Expenses) under G&A sectionare calculated fields and already formatted. Do nothing in this row.
viii. Columns B-M: Row 4 (Full-Time Employee Salaries) has already been prepopulated automatically from the Personnel Worksheet tab. The values in these cells are calculated fields and already formatted. Do nothing in this row.
c. G&A Worksheet: This worksheet does not need to be modified but analyzed and referenced to complete the corresponding G&A budget line items in the IT Department Budget Worksheet (e.g., Item names, estimated costs, billing frequency, occurrence, and billing month). This worksheet contains some typical expenses found in the G&A section of an IT budget. Please note the Equipment Depreciation was outlined on the G&A Worksheet but is not included in the IT Department Budget Worksheet (this particular item often requires a good understanding and special treatment with coordination with Finance & Account to determine the asset fair market value and allowable depreciation rates for these assets. In addition, this item is often excluded from the IT budget but included in the overall corporate budget.Lastly, top executives and Board of Directors salaries are often included in G&A and spread across all department budgets. This G&A line item was not included for this exercise.
d. Software-Equipment-Service Worksheet: This worksheet does need to be analyzed, modified, and referenced to complete the corresponding G&A budget line items in the IT Department Budget Worksheet. You will need to determine the correct "Expenditure Type" by first completing the Software-Equipment-Services worksheet (Specifically: Column D in each of the tables, selecting either CAPEX or OPEX and then make the corresponding budget line item entry and amount into the IT Department Budget Worksheet. Also you do not need to add or change any of the remaining fields in these tables on this worksheet, they have already been prepopulated with the essential information for you to fill out the IT Department Budget Worksheet (e.g., Item names, expenditure type, total price, billing frequency, occurrence, and billing month).
e. Personnel Worksheet: Fortunately for you, this worksheet does not need to be modified but analyzed and referenced to complete the corresponding Personnel budget line items in the IT Department Budget Worksheet (e.g., Item names, estimated costs, salaries, headcounts per month, billing frequency, occurrence, and billing month). This worksheet contains some typical expenses found in the personnel section of an IT budget. As noted earlier, all of the Personnel expense budget line items have already been entered into the IT Department Budget Work Sheet (Column A: Rows: 31 - 36). You will need to populate the remaining cells for this section of the budget using the prepopulated tables on the Personnel Worksheet. You will notice that the Full-Time Employee salaries have already been populated in the IT Department Budget Worksheet (Columns: B-M; Row 4) you do not need to do anything else with this line item. This information came from and is directly linked to the IT Department Personnel Monthly Worksheet table. You will how ever need to calculate the budget amounts for the consulting staff using the Contracted Support Staff Table based on the total number per month and predefined salaries. Use the Other Personnel Expenditure Table to complete the remaining budget line items and associated expenditures per month to their corresponding line item on the IT Department budget Worksheet.
f. References: Do nothing with this worksheet. It was used to build reference lists for drop downs items in the table on the worksheets.
5. This project assignment is meant to give you some practical hands-on experience and a better understanding of the key components, data, correlations to IT investments and operationsrequired in formulating the IT Department budget. Reference Module 6 lecture notes and course text, and perhaps independent research to complete this project assignment. As a guide post your IT Department Dashboard final total results for this Year's Budget column should be comparable to the following:
|
Budget Section
|
Totals
|
|
Personnel Expenses
|
$210,302,974
|
|
Computing Equipment-Capitol
|
$1,254,150
|
|
Computing Equipment-Expenses
|
$729,132
|
|
Software Cost-Capitol
|
$1,194,160
|
|
Software Cost-Expenses
|
$676,720
|
|
EDC Facility Costs-Capital
|
$757,410
|
|
EDC Facility Costs-Expenses
|
$884,166
|
|
General & Administrative
|
$458,636
|
Use this table to compare with the IT Department Budget dashboard tab in the workbook.
If you are off buy a small amount don't get concerned. However, if you are way off the mark, then you most likely have misclassified the expenditure type, have errors in calculations, or entered in a wrong amount in a cell. You must pay attention to detail when transferring information from the corresponding worksheets. Recheck your determinations and values. Again do not make entries to force the expected outcomes outlined in this table. You will be much better off submitted the assignment giving it your best try with minor errors than manipulating the spreadsheet to align with the expected result outlined in the table above. Lastly, you will have a working example to reference if and when you are asked to submit an IT Budget.
Information related to above question is enclosed below:
Attachment:- ITBudgetWorkbook_Student.rar