Personal Budget Exercise - Excel Spreadsheet Analysis, Finance 101

This assignment requires the creation of a personal budget spreadsheet using Microsoft Excel. Students are tasked with designing a spreadsheet to track both projected and actual expenses across various budget categories, including housing, food, utilities, and more. The assignment mandates specific formatting requirements, such as custom headers and footers, column headings, and the use of currency formatting. Students must input projected costs, calculate totals using the SUM function, and then enter actual monthly expenses. Formulas are used to calculate total monthly expenses and the difference between projected and actual spending. The assignment also involves creating a pie chart to visualize projected costs and answering questions about budget allocation and financial planning scenarios. The final spreadsheet includes calculations for yearly totals and average expenses, formatted for readability and clarity, and includes a renamed sheet tab. Students must also answer questions regarding budget allocation and unexpected expenses, based on the data from the spreadsheet.

Personal Budget Exercise – MS Excel
Use the project description HERE to complete this activity. For a review of the complete
rubric used in grading this exercise, click on the Assignments tab, then on the title
Personal Budget (Excel). Click on Show Rubrics if the rubric is not already displayed.
Creating a spreadsheet to track personal expenses is an excellent use of Microsoft Excel.
For this exercise, you will create a spreadsheet to enter a personal budget and track
actual expenses for the year. You may choose to use real data with a projected monthly
income amount that reflects your real data OR create a fictitious budget using a monthly
income amount of $3,500.00
Here are suggested budget categories if you are not using a real budget. At a minimum,
you must have 9 budget categories:
In your projected budget amounts you would enter here what you HOPE to spend (or save) every
month. In the actual expenses, the amounts would most likely vary each month.
Housing (Mortgage or Rent) Food
Utilities Miscellaneous
Car Payment Entertainment
Insurance Gas
Student Loans Savings
Note: there are several tutorials on Excel functions that can be found in the topic labeled "Optional
Tutorials – Excel project" in the Content (Readings) list for Week 1.
Requirement Points
Allocated
Comments
1
Open Excel and save a blank worksheet with the
following name:
“Student’s First InitialLast Name Excel”
Example: JSmith Excel
Set Page Layout to Landscape
0.1
Use Print Preview to
review how
spreadsheet would
print.
2
In the worksheet, insert a Custom Header titled,
"My Personal Budget." 0.25
This Custom Header
text must be Arial 14
point, Bold, and be
centered on the page.
3
Add a custom Footer with your name in the Left
Section and automatic pagination in the Right
Section. Put a fixed date (use the date this
assignment is due) in the center portion of the
footer.
0.3
Text format is Arial 10
point
Normal text
4 Enter column headings :
BUDGET ITEM, PROJECTED COSTS and the 12
0.3 All column headings
must use the following

Requirement Points
Allocated
Comments
months for the year: JANUARY THROUGH
DECEMBER
You may abbreviate the months as follows: JAN,
FEB, MAR, APR, MAY, JUN, JUL, AUG,
SEP, OCT, NOV, DEC
Use all capital letters
text formatting:
Arial 10 point
Bold
Text centered in
column
All capital letters
5
Enter your personal budget categories/labels (either
your own or the list provided) in the rows under the
column heading BUDGET ITEM.
A minimum of 9 categories is required.
0.25
Text format is Arial 10
point, Normal.
Align text Left in the
cell.
6
Format all cells containing numeric data to
Currency, using two decimal places. If you have
this set up correctly MS Excel will automatically
insert a "$" in front of the amounts that you enter.
0.25
I will be able to tell if
you simply typed in
the "$." This will result
in zero (0) points for
this item. If any other
format is used besides
Currency, the score
will be zero (0) for this
component.
7
In the column under your PROJECTED COSTS
label, enter a monthly budget goal amount for each
BUDGET ITEM category. This would be the limit
of what you want to spend in each category (or
put into savings) in one month's time.
0.25
Arial 10 point
Bold
Black
Align values Right in
column
8
Enter the label "PROJECTED BUDGET TOTAL:" in
the next row in the BUDGET ITEM column.
0.1
Use the following
formats:
Arial 10 point
Bold
Blue
Align text Right in the
cell
9 In the cell to the right of the PROJECTED BUDGET
TOTAL label, use the SUM function to calculate the
total amount of the PROJECTED COSTS column.
(This amount should equal your income amount of
$3,500.00 or your selected budget goal.)
0.3 Arial 10 point
Normal
Black

Requirement Points
Allocated
Comments
Note: do not enter each cell in the column
individually when using the SUM function.
Note: this amount should appear in ONLY ONE
cell (not copied to remaining cells in the same
row).
Align values Right in
the cell
10
Under the heading for each Month, enter an actual
expense amount for that item for that month. (For
example, in the winter months, your utility bills might
be higher). While some items might be the same
from month to month, DO NOT enter the same
amount for all items across the months. In each
month you want to be close to you monthly income
number but do not always have to match it exactly.
0.3
Use the following text
format:
Arial 10 point
Normal
Align values Right in
the cell
11
In the next row in the BUDGET ITEM column (under
the PROJECTED BUDGET TOTAL label) enter the
label "Total Monthly Expenses."
0.1
Use the following text
format:
Arial 10 point
Bold
Green
Align text Right in the
cell
12
For the cells in this Total Monthly Expenses row,
insert a formula that will calculate the total actual
expenses for each month. Use the SUM function to
add the amounts in each column and show the
result. The sums for each month should not always
equal your projected budget total. It would be rare
to actually spend exactly what you budgeted for the
month.
Note: do not enter each cell in the column
individually when using the SUM function.
NOTE: do not include empty cells in your
formula.
0.5
13
In the next row under the “Total Monthly Expenses”
label put the label “Projected versus Actuals.”`
0.1
Use the following text
format:
Arial 10 point
Bold
Black
Align text Right in the
cell
14 Then in the cell under the Total Monthly Expenses
for each month, use a formula that will subtract the
actual total expenses for the month from the
projected budget total (the target amount in the
0.5

Requirement Points
Allocated
Comments
PROJECTED BUDGET TOTAL column).
You must use Absolute Reference in your
formula
If the result of your calculation is a positive number,
then you are under budget for the month. (You have
money left over). If the number is negative, then you
are over budget for the month. (You didn’t have
enough money to pay all of the expenses that
month).
*You will use the result of this calculation to
answer Question 3 below
15
Enter a column label titled “Total” to the right of your
last month.
0.1
Format :
Arial 10 point
Bold
Blue
Align center in cell
16
Enter a formula using the AutoSum drop-down
option on your tool bar and insert the Sum function
in the first budget item row, under Total. Then copy
this formula down for all the other categories. This
will calculate the total expenditures for each
BUDGET ITEM in your budget list over the span of
the year.
NOTE: Be certain to total just the months; do not
include the PROJECTED COSTS column.
NOTE: do not enter each cell in the row
individually when using the SUM function
0.5
Use the following
formats:
Arial 10 point
Bold
Blue
Align values Right in
the cell –
You must use Excel to
build a formula for
adding the item
amounts. If you simply
type in a total, I will be
able to tell and will
award zero (0) points
for this component.
17
Enter a column label titled "Item Average Expense"
to the right of the Item “Total” column.
0.1
Format this label:
Arial 10 point
Bold
Black
Align center in cell
18 Enter a formula using the AutoSum drop-down
option on your tool bar and insert the Average
function of your expenses from January through
0.5 Use the following text
format:
Arial 10 point

Requirement Points
Allocated
Comments
December in the first budget item row, under the
label "Item Average Expense." Then copy this
formula down for all the other categories.
Note: do not enter each cell in the row
individually when using the AVERAGE function
Bold
Black
Align text right in the
cell
19
Apply All Borders to the cells with data. This means
that there will be lines around all the individual cells
that are used in your spreadsheet. Format your first
row (column headings) by shading it to distinguish
the headings from the number entries. These
headings should already Bold.
Ensure that all column headings and row labels are
fully visible. Either use Wrap Text OR expand the
column width so that no labels are truncated.
0.5
Overall, format the
spreadsheet for
readability and clarity.
Be sure font size and
type are used
consistently. Use color
appropriately to
improve the
appearance.
20
Create a pie chart that shows the items listed in
your total PROJECTED COSTS column as slices of
the pie chart. (Note: This is budget not actual
expense items.). You will use two columns for your
chart – the BUDGET ITEM column and the
PROJECTED COSTS column.
Center the chart in the space below your budget
numbers & expenses on the first page (the
spreadsheet might take up multiple pages in Print
View. You may need to resize the chart to do this.
Check the Print view to ensure that the chart is
centered below the spreadsheet cells on page 1.
0.5
21 Title the pie chart: “My Personal Budget” 0.2
22 Show dollar amounts on each segment of the chart. 0.25
23
Chart Legend
Ensure that all segments are clearly identifiable
from your legend (on the right-hand side). The
legend should contain your BUDGET ITEM list and
be color-coded to match the chart.
0.5
24
Rename your sheet tab from “Sheet 1” to "Budget
2016" in the Sheet Tab area at the bottom left side
of the spreadsheet. Delete unused sheets.
0.25
For the questions below, present your answers in a very readable format under the chart.
These answers may be placed on the page below your chart if there is not sufficient room
on page 1. Do not let the answers be "split" over more than one page. You can type your
answer in one cell (in Column A), then highlighting and selecting several rows and

Requirement Points
Allocated
Comments
columns, selecting merge cells and selecting Wrap Text. You will want to change the text
from Center to Left justification. Play with this a bit. If you simply type your answer on a
single line in Column A, that will also be ok. But be sure the entire answer can be read
without the reader having to change any formatting.
25
Question1: If you received a $1000.00 bonus one
month, how would you divide it to spend among the
nine budget categories (in your PROJECTED
COSTS) and why?
Label your response Question 1. Answer this
question in 2 to 3 sentences after the last row of
your spreadsheet. DO NOT change your
spreadsheet. Just respond to the question.
1.0
Use the following text
format:
Arial 10 point
Normal/Black
Align text left in the
cell
25
Question 2: If your car unexpectedly needed a
$500.00 repair, explain how you would reduce your
MONTHLY BUDGET to pay for your car repair. Be
sure to include the categories from which you will
take the $500 in your explanation.
Label your response Question 2. Answer this
question in 2 to 3 sentences in a new row under
your response to Question 1. DO NOT change your
spreadsheet. Just respond to the question.
1.0
Use the following text
format:
Arial 10 point
Normal/Black
Align text left in the
cell
25
Question 3: State the amount that you were over or
under budget for the month of August. (See the
highlighted text above for how you determined if
you were over or under budget for August.) What
caused it?
Label your response Question 3. Answer this
question in 2 to 3 sentences in a new row under
your response to Question 2. DO NOT change your
spreadsheet. Just respond to the question.
1.0
Use the following text
format:
Arial 10 point
Normal/Black
Align text left in the
cell
TOTAL 10