Answered step by step
Verified Expert Solution
Question
1 Approved Answer
County Library IT Budget Instructions: Formatting and Formulas 1 In cell B5, use a sum formula to calculate the total patrons in year 1 (this
County Library IT Budget | |
Instructions: Formatting and Formulas | |
1 | In cell B5, use a sum formula to calculate the total patrons in year 1 (this year) |
2 | In cell B10, calculate the growth rate for the county budget from last year to year 1 (this year) |
3 | Format cell B10 as a percent with 2 decimal places |
4 | In cell F5, calculate the amount of student fees to be used for IT revenue using the total patrons for year 1 (this year) calculation and the dollar amount that IT receives per person. Example: If IT receives $2 per person and there are 200 people, then IT receives $400.00 |
5 | In cell F6, calculate the revenue that IT receives from the large county budget. IT receives a certain percent of the county budget. Example: if IT receives 5% of the county budget and the county budget is $2000, then IT receives $100 |
6 | In cell F7, write a sum formula to calcualte the total anticipated IT Revenue |
7 | In cell F8, calculate the IT salary expense. IT Salaries are a percent of the total anticipated IT revenuethat comes from the county budget |
8 | In cell F12, calculate the total of the IT expenses |
9 | In cell F13, calculate the net income for the IT department |
10 | In cell F2, write a formula to grab the total number of patrons from cell B5 |
11 | In cell G2, forcast the DECREASE in patrons using the growth rate given and copy/drag the formula to H2 |
12 | Use the county budget growth rate you calculated in B10 to forcast the county budget for Year 2 and Year 3 |
13 | Calculate the revenue (for the IT budget) from student fees for Year 2 and Year 3 |
14 | Calculate the revenue (for the IT budget) that comes from the county budget for Year 2 and Year 3 |
15 | Calculate the total anticipated IT revenue for Year 2 and Year 3 |
16 | Calculate the IT salary expense for Year 2 and Year 3 |
17 | Forcast the other IT expenses using the IT expense growth rate |
18 | Calculate the total anticipated IT expenses for Year 2 and Year 3 |
19 | Calculate the total net income for Year 2 and Year 3 |
20 | Format percents as percents with two decimals and dollar amounts as currency |
G H Year 2 Year 3 B C D E F. 1 Patrons-Year 1 Information Technology-Year 1-Year 3 Projected Budget - County Libraries 2 Smith Law Library 360000 Number Library Patrons for Smith, Valley & King 3 Valley Center Library 250000 Year 1 (This Year) 4. King Library 125000 County Budget $ 214,000,000 5 Total patrons for Year 1 (This Year) 735000 IT Revenue from Student Fees 6 County Budget for Last Year $ 208,650,000 IT Revenue From County Budget 7 IT Revenue (% of County Budget) 0.025 Total Anticipated IT Revenue 8 Salaries (% of Total IT Revenue) 0.27 IT Salaries 9 Student Fees Per Person 2.25 Training and Maintenance Contracts $575,500 10 County Budget Growth Rate (Last Yr-This Yr) 2.56% Software Licenses and Hardware $587,500 11 Expenses - growth rate 0.04 Miscellaneous IT Anticipated Expenses $3,548,600 12 Patrons growth rate -0.01 TotalAnticipated IT Expenditures 13 Total Net Income 14 15 Your Name Here
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started