Department Budget Analysis

Overview

Department Budget Analysis. As a departmental or organizational leader, you must 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 are unmet. 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’s finances and communicate them effectively to stakeholders.

For this Department Budget Analysis 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’s financial goal.

Preparation

Read the following scenario to understand the context of the Department Budget Analysis 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 to help create the assignment’s video portion. You may use Kaltura or other video recording software if the format is compatible with the course room.

Department Budget Analysis 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 Department Budget Analysis 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 you 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 the cost of living. Consider budgeting this increase for personnel costs.

· Many outdated computers had to be replaced this budget year, accounting for the nine-month increase. 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 professional financial analysis.

· Make graphs for the current year’s 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 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 course room-compatible video recording method you are familiar with) in which you review your analysis and proposed budget. In 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 it would still work in the worst possible scenario) 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 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 your Department Budget Analysis presentation’s designated department members and leadership audience?

Submit the completed Week 9 Assignment Template and 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.