Complete as required

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,043

1,256

159

485

1,254

546

658

Feb

10,453

753

175

387

845

897

799

Mar

11,745

1,089

236

446

2,879

547

589

Apr

14,178

758

187

478

299

541

May

12,505

589

145

515

2,898

684

879

Jun

11,245

968

198

625

1,985

486

632

Jul

11,986

1,546

250

695

1,656

525

823

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