Department Budgetting
DEPARTMENT BUDGET ANALYSIS
Overview
As a departmental or organizational leader, you are likely to be required to prepare a proposed budget for each fiscal year. Budgets provide parameters for achieving goals. Spend too much and you’ll have a deficit. Spend too little and you could have a surplus, which may not be a good sign if organizational goals have not been met. Using technology can help you stay organized throughout your analysis, as well as help you visualize financial data. Displaying the data visually will allow you to observe general trends in your department finances and to communicate about them effectively to stakeholders.
For this assignment, you will use Excel to analyze and represent department data. The approach to learning and working with Excel software in this course is one you can apply to other technology tools in your life and career as well.
Your goal for this assignment is to continue building your confidence in using a technology tool while conducting a department financial analysis. You will use Excel to organize quarterly and annual budgets, visualize this financial data using charts and graphs, and discuss the results of your financial analysis, including progress toward your department financial goal.
Preparation
Read the following scenario to understand the context for this assignment. Review the Week 9 Assignment Template [XLSX]. This completed template will be one of your deliverables for this assignment.
Review the information under Audio and Video in this Course in Tools and Resources as needed for help with creating the video portion of the assignment. You may use Kaltura or other video recording software as long as the format is compatible with the courseroom.
Scenario
You are the associate administrator of a large department in the City of Acme with an operating budget of 1.8 million dollars. Your boss, the administrator, has asked you to create a presentation with graphs demonstrating proposed allocations for next fiscal year’s budget.
Instructions
Using what you have learned about budgeting, Excel, and creating data visualizations, complete the Week 9 Assignment Template. Then create a 5–10 minute video in which you present your proposed budget to the department and leadership.
For this assignment, complete the following:
1. Prepare a proposed budget based on a professional financial analysis of a year-to-date budget.
· Use the Week 9 Assignment Template to review the current annual budget. After your review the current budget, create a new proposed budget.
· Based on this data, how will you propose the budget be updated for the next fiscal year? Consider the following before filling in the cells for the next fiscal year:
· It is rumored there will be a 3 percent increase in cost of living. Consider budgeting this increase for personnel cost.
· Many outdated computers had to be replaced this budget year, which accounts for the increase at the nine-month mark. It is up to you to decide whether to anticipate more issues next year knowing that two-thirds of the computers were replaced this year.
· Fewer vehicles will be needed next year. If each vehicle is valued at about 36,475 dollars, consider a conservative amount to budget for next year. If you do not add more vehicles, consider a substantial increase in Vehicle Maintenance for repairs in the new year.
· Please read the comments in the cells, indicated by
red triangles in the upper right corner of the cells.
· You have the original budget, the nine-month budget (or year-to-date at nine months), and the empty budget spreadsheet for the next year.
2. Create appropriate graphs to illustrate the findings of a professional financial analysis.
· Make graphs for the current year budget, the year-to-date budget, and the proposed budget.
· Remember, you may wish to display these graphs during your video, so it is worth considering the audience for your video and how the graphs will appear during the presentation.
· Use Excel to create these graphs in the Week 9 Assignment Template.
3. Explain the results of a professional financial analysis, including overall progress and why the proposed budget is the best possible forecast for the new year.
· Make a video presentation using Kaltura (or another courseroom-compatible video recording method you are familiar with) in which you review your analysis and proposed budget. At the end, briefly explain your choice of graphs.
· Make your recommendation for the budget with your logic for any proposed increases or decreases.
· Discuss whether your final budget is conservative (meaning in the worst possible scenario it would still work) or aggressive (meaning that all budget metrics must be met to avoid a deficit next year).
4. Explain how selected graphs are the best choice to communicate financial information.
· Discuss the graphs that you chose to present the data from the three budgets in the Week 9 Assignment Template.
· How do they help the audience better understand the presented financial data?
· How are they better than other types of graphs you considered?
5. Address the appropriate audience, using familiar, discipline-specific language and terminology.
· Are you using appropriate economic and finance terminology?
· Is the language you are using appropriate for the scenario and your role as an assistant administrator?
· Are you speaking to the designated audience of department members and leadership in your presentation?
Submit the completed Week 9 Assignment Template as well as the 5–10 minute video presentation recording.
Additional Requirements
· Communication: Communicate in a manner that is scholarly, professional, respectful, and consistent with expectations for professional practice in education. Original work and critical thinking are required. Your writing must be free of errors that detract from the overall message.
· Video presentation: 5–10 minutes.
Week 9 Assignment
| ECO1100: Week 9 Assignment | Enter data | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Note: Remember to create graphs for each of the three budgets in this template. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Populates Automatically | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Current Year Budget | $ 1,800,000.00 | Year-to-Date Budget | Proposed Budget |
user: user: |
Proposed Budget Rationale Notes (Note how you built in the considerations from the assignment instructions here: Cost of Living increase; Planning for issues like outdated equipment; Vehicle considerations; Any other specific decisions you made.): | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 9 months of info (3 Qtrs) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Personnel | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Salaried Employees 8 FTE | $ 600,000.00 | $ 450,000.00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Non Salaried Employees 13 FTE | $ 298,500.00
user: user: |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Benefits | $ 315,000.00 | $ 224,550.00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Operating Funds | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Office Supplies | $ 30,000.00 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Utilities | $ 45,000.00 | $ 34,762.50
user: user: |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Vehicle Maintenance | $ 900.00 | $ 475.00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Liability Insurance | $ 65,000.00 | $ 48,749.99 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Contracts | $ 38,000.00 | $ 24,750.00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Capital | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Computers | $ 35,000.00 | $ 41,950.00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Vehicles | $ 145,900.00 | $ 124,973.00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Infrastructure | $ 73,000.00 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Miscellaneous | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Petty cash | $ 200.00 | $ 100.00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Other expenses | $ 10,000.00 | $ 3,756.00 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| $ 1,355,566.49 | Subtotal (Should Match H4) | $ – 0 |
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
