Overview You are the Budget Analyst for a small company One of you primary tasks is to carefully monitor expenses to ensure you do not spend more than allocated budgeted by the top management 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 columns for rent and insurance) Utilize cell arithmetic Provide totals for each expense category (e g , salary) and for each month Calculate the following Average of the actual expenses incurred for each expenses category 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 except for the insurance and rent payments) Estimate at Completion (EAC) total actual expenses plus the ETC from b) above Difference between the budget (attachment 1) and your EAC for each expense category and the total budget Generate a Column chart based on the differences for each category (e g , Salary, Supplies, etc ) Include the following in the chart Chart Title as Difference Comparison Data Labels under each expense category column Generate a report to your supervisor indicating your findings Include the following Introduction explaining why you have decided to perform this analysis Methodology utilized Findings of your analysis Develop a single table that shows all calculations Provide a narrative of the significant differences between the budget and EAC 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 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 organizational 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 15,520 968 280 11,879 1,546 280 13,447 695 280 15,125 962 485 14,555 789 280 13,465 1,256 280 13,647 753 280 14,425 994 280 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Utilities 684 495 534 493 515 652 732 829 Travel 1,524 985 3,128 1,256 2,578 2,186 1,893 2,458 Training Advertising 654 856 798 979 457 675 857 612 864 978 648 789 777 732 312 926 Monthly rent for the office is $3,125 Insurance is $195 (per month) for the first 4 months and goes up to $225 in May Annual Budget Information Salaries Supplies Phone Utilities Travel Training Rent Insurance Advertising Amount 160,000 12 000 3,200 6,000 15,000 8,000 38,000 2,200 12,000 62 4 4 7 1 2 2 3 5 9 3 1 14 8 0 9 4 7

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 01, 2024

Overview: You are the Budget Analyst for a small company. One of you primary tasks is to carefully monitor expenses to ensure you do not

Overview:

You are the Budget Analyst for a small company. One of you primary tasks is to carefully monitor expenses to ensure you do not spend more than allocated/budgeted by the top management.

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 columns for rent and insurance).
Utilize cell arithmetic.
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 except for the insurance and rent payments)
3. Estimate at Completion (EAC) total actual expenses plus the ETC from b) above.
4. Difference between the budget (attachment 1) and your EAC for each expense category and the total budget.
Generate a Column chart based on the differences for each category (e.g., Salary, Supplies, etc.). Include the following in the chart:
1. Chart Title as Difference Comparison.
2. Data Labels under each expense category/column.
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 utilized.
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. 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?

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 organizational 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.

image text in transcribed

Attachment 1 Salaries Supplies Phone 15,520 968 280 11,879 1,546 280 13,447 695 280 15,125 962 485 14,555 789 280 13,465 1,256 280 13,647 753 280 14,425 994 280 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Utilities 684 495 534 493 515 652 732 829 Travel 1,524 985 3,128 1,256 2,578 2,186 1,893 2,458 Training Advertising 654 856 798 979 457 675 857 612 864 978 648 789 777 732 312 926 Monthly rent for the office is $3,125 Insurance is $195 (per month) for the first 4 months and goes up to $225 in May Annual Budget Information: Salaries Supplies Phone Utilities Travel Training Rent Insurance Advertising Amount 160,000 12.000 3,200 6,000 15,000 8,000 38,000 2,200 12,000 % 62.4 4.7 1.2 2.3 5.9 3.1 14.8 0.9 4.7