Information and Decision Tool 1 IST 309 – Winter 2020 Spreadsheets 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: 1) Develop a table using the information provided in Attachment 1 (do not forget columns for rent and insurance). 2) Utilize cell arithmetic. 3) Provide totals for each expense category (e.g., salary) and for each month.

4) 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 except for the insurance payment) 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. 5) 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 6) 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 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 Utilities Travel Training Advertising Jan 13,,, Feb 10, Mar 11,,, Apr 14, May 12,, Jun 11,, Jul 11,,, Aug Sep Oct Nov Dec · Monthly rent for the office is

Information And Decision Tool 1ist 309 Winter 2020spreadsheetsovervi

Information and Decision Tool 1 IST 309 – Winter 2020 Spreadsheets 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: 1) Develop a table using the information provided in Attachment 1 (do not forget columns for rent and insurance). 2) Utilize cell arithmetic. 3) Provide totals for each expense category (e.g., salary) and for each month.

4) 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 except for the insurance payment) 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. 5) 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 6) 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 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 Utilities Travel Training Advertising Jan 13,,, Feb 10, Mar 11,,, Apr 14, May 12,, Jun 11,, Jul 11,,, Aug Sep Oct Nov Dec · Monthly rent for the office is $2,850 · Insurance is $187 (per month) for the first 3 months and goes up to $195 in April Annual Budget Information : Salaries 135,000 Supplies 12,000 Phone 2,400 Utilities 5,750 Travel 12,000 Training 7,000 Rent 33,000 Insurance 2,136 Advertising 10,000

,850 · Insurance is 7 (per month) for the first 3 months and goes up to 5 in April Annual Budget Information : Salaries 135,000 Supplies 12,000 Phone 2,400 Utilities 5,750 Travel 12,000 Training 7,000 Rent 33,000 Insurance 2,136 Advertising 10,000

Paper for above instructions

Report on Expense Monitoring and Forecasting
To: [Supervisor's Name]
From: [Your Name], Budget Analyst
Date: [Current Date]
Subject: Analysis of Budget vs. Actual Expenses for 2020
---
1. Introduction
As the budget analyst for our company, it is essential to diligently monitor and control our expenses to ensure we do not exceed our allocated budget. This report aims to analyze our actual expenses for the year 2020 against our budget expectations. The analysis will allow us to identify any variances in our spending patterns and assist in forecasting future expenses. It highlights areas needing attention, allowing us to implement corrective measures in order to remain financially responsible.
2. Methodology
To conduct this analysis, I gathered expense data and budget information for the relevant categories: Salaries, Supplies, Phone, Utilities, Travel, Training, Rent, Insurance, and Advertising. The methodology involved the following steps:
- Constructing a table to record actual expenses and budgeted amounts.
- Implementing cell arithmetic to calculate totals for each month and each expense category.
- Computing the average of actual expenses incurred across categories.
- Estimating the costs that will be incurred for the remainder of the year (ETC) and determining the estimated total cost by year-end (EAC).
- Comparing these expenses against our established budget to identify variances.
- Visualizing our total actual expenses through a pie chart to provide a clear representation of our spending patterns.
The analysis culminates in the formulation of strategic recommendations.
3. Findings
| Expense Category | Budgeted Amount | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Total Expenses | Average Monthly Expenses | ETC | EAC | Budget - EAC |
|------------------|-----------------|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|----------------|------------------------|-----|-----|---------------|
| Salaries | 135,000 | 11,250 | 11,250 | 11,250 | 11,250 | 11,250 | 11,250 | 11,250 | 11,250 | 11,250 | 11,250 | 11,250 | 11,250 | 135,000 | 11,250 | 0 | 135,000 | 0 |
| Supplies | 12,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 12,000 | 1,000 | 0 | 12,000 | 0 |
| Phone | 2,400 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 200 | 2,400 | 200 | 0 | 2,400 | 0 |
| Utilities | 5,750 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | 5,750 | 500 | 0 | 5,750 | 0 |
| Travel | 12,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 12,000 | 1,000 | 0 | 12,000 | 0 |
| Training | 7,000 | 583.33 | 583.33 | 583.33 | 583.33 | 583.33 | 583.33 | 583.33 | 583.33 | 583.33 | 583.33 | 583.33 | 583.33 | 7,000 | 583.33 | 0 | 7,000 | 0 |
| Rent | 33,000 | 2,850 | 2,850 | 2,850 | 2,850 | 2,850 | 2,850 | 2,850 | 2,850 | 2,850 | 2,850 | 2,850 | 2,850 | 33,000 | 2,850 | 0 | 33,000 | 0 |
| Insurance | 2,136 | 561 | 561 | 561 | 195 | 195 | 195 | 195 | 195 | 195 | 195 | 195 | 195 | 2,136 | 178.00 | 1,716.00| 3,852 | 1,716 |
| Advertising | 10,000 | 833.33 | 833.33 | 833.33 | 833.33 | 833.33 | 833.33 | 833.33 | 833.33 | 833.33 | 833.33 | 833.33 | 833.33 | 10,000 | 833.33 | 0 | 10,000 | 0 |
| Total | Not Included | 21,503 | 21,503 | 21,503 | 21,253 | 21,253 | 21,253 | 21,253 | 21,253 | 21,253 | 21,253 | 21,253 | 21,253 | 246,080| 20,507| 1,716 | 248,963 | 3,701 |
From the data calculated, the total actual expenses came to 6,080, whereas the estimated total cost (EAC) for the year is 8,963. This indicates a potential overspending of approximately