Executive Summary

The purpose of my project was to create an easy and efficient way for a user to analyze the costs of
different projects within a program. Last summer I was a financial analyst for my internship. Each week
I had the repetitive task of entering cost information and updating graphs and creating very simple
projection scenarios. These user forms will enable a user in a similar situation to quickly create new
project templates, edit projects, enter data, view different possible cost projections for the year, and view
a summary of all the projects in a single program.

Create a Project
The first user form will quickly create a project spreadsheet. The spreadsheet will include the project
name and budget and an initial cost projection based on the budget. Each project spreadsheet will also
include a graph to quickly view total actual costs as well as current projected total costs.

Edit a Project
The second user form edits the name or budget of a project. This is useful if the project budget changes
during the year, or if the user entered the project name or budget incorrectly.

Enter Cost Data
Once the project spreadsheet is set up, the user can enter and update cost information as frequently as
needed. This user form is set up so the user can quickly enter cost data for all the projects and edit any
mistakes.

Create Cost Projections
Next the user can create cost projections. I created six different ways of creating projections, based on
the amount of information available to the user and to give the user a view of best case and worst case
scenarios. These methods include using the average monthly cost information, the highest monthly cost
information, the lowest monthly cost information, the previous month’s cost information, a straight line
projection through the budget, and a final option that let the user enter cost estimates for each future
month.

Summarize Projects
Finally the user can create a summary of all the projects in the workbook. This summary sheet will
include the names of the projects, actual and projected cost information, and a graph. I have found that
summary graphs like this are useful for management reviews.

• http://files.gove.net/shares/files/14w/kseppi/Morrison_Final_Project.xlsm
• http://files.gove.net/shares/files/14w/kseppi/Morrison_VBA_Final_Project.pdf