Information and Decision Tool 1

 

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:

 

  • Develop a table using the information provided in Attachment 1 (do not forget column for computer services).
  • Provide totals for each expense category (e.g., salary) and for each month.
  • Calculate the following:
    1. Average of the actual expenses incurred for each expenses category.
    2. 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)
    3. Estimate at Completion (EAC) – total actual expenses plus the ETC.
    4. Difference between the budget (attachment 1) and your EAC for each expense category.
  • Generate a pie chart based on the total actual expenses for each category (e.g., Salary, Supplies, etc.). Include the following in the chart:
    1. Chart Title as “Total Actual Expenses”
    2. Data Labels as a percentage of the total expense and including the category name. (please the parts from 1 to 4 do it in excel).
  • Generate a report to your supervisor indicating your findings. Include the following:
    1. Introduction explaining why you have decided to perform this analysis.
    2. Methodology
    3. Findings of your analysis. Develop a single table that shows all calculations. Provide a narrative of the significant differences between the budget and EAC.
    4. Suggest a course of action that should be taken in order to ensure that the established budget is not exceeded.(please do this part in word file)

 

What to Turn In?

  • Your written report to your supervisor with charts embedded into the text to support your findings.
  • 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

 

 

Is this the question you were looking for? If so, place your order here to get started!

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.