Information and Decision Tool

PA 650 – Fall 2014
Spreadsheets

Overview:
You are the Budget Analyst for a small County Department. One of you primary tasks is to carefully monitor expenses to ensure you do not spend more than allocated/budgeted by your Fiscal Manager.

Requirements:
Your manager has asked you to generate a report using the following criteria:

1)   Develop a table using the information provided in Attachment 1 (do not forget column for computer services).
2)   Provide totals for each expense category (e.g., salary) and for each month.
3)   Calculate the following:
a)   Average of the actual expenses incurred for each expenses category.
b)   Estimate to Complete (ETC). This is the amount you are forecasting will be spent for each of the expense categories for the remainder of the year. (Use the monthly average times the number of remaining months)
c)   Estimate at Completion (EAC) – total actual expenses plus the ETC.
d)   Difference between the budget (attachment 1) and your EAC for each expense category.
4)   Generate a pie chart based on the total actual expenses for each category (e.g., Salary, Supplies, etc.). Include the following in the chart:
a)   Chart Title as “Total Actual Expenses”
b)   Data Labels as a percentage of the total expense and including the category name
5)   Generate a report to your supervisor indicating your findings. Include the following:
a)   Introduction explaining why you have decided to perform this analysis.
b)   Methodology utilized.
c)   Findings of your analysis. Develop a single table that shows all calculations. Provide a narrative of the significant differences between the budget and EAC.
d)   Recommendations. Suggest a course of action that should be taken in order to ensure that the established budget is not exceeded.

What to Turn In?
1) Your written report to your supervisor with charts embedded into the text to support your findings.
2) Your spreadsheet file.

The grade will be based on completion of the requirements stated above and your recommendation to meet the departmental goal of not exceeding your budget.

Both files must be title with your name in the filename (ex: Mosher Tool 1) and be submitted as indicated in the course schedule.

Attachment 1

Salaries   Supplies   Phone   Postage    Travel   Training   Copiers
Jan   10,543   1,256   159   485   1,254   546   658
Feb   8,953   1,753   175   387   865   897   799
Mar   11,265   2,589   236   446   2,879   857   589
Apr   10,678   1,758   187   478       299   541
May   12,005   589   145   515   2,898   684   879
Jun   10,745   968   198   625   1,895   486   632
Jul   9,886   1,546   230   695   1,656   525   823
Aug
Sep
Oct
Nov
Dec

Note: Monthly computer service allocation for the department is $2,560

Annual Budget Information:
Salaries   135,000
Supplies   12,000
Phone       2,400
Postage   5,750
Travel       12,000
Training   7,000
Comp. Srvc.   30,720
Copiers   9,000

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂