Complete a budget in excel using the steps in the attached worksheet
infs_hw_excel_budget.docx

Unformatted Attachment Preview

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)
Utilities
Car Payment
Insurance
Student Loans
Food
Miscellaneous
Entertainment
Gas
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
Open Excel and save a blank worksheet with the
following name:
1
“Student’s First InitialLast Name Excel”
Example: JSmith Excel
0.1
Comments
Use Print Preview to
review how
spreadsheet would
print.
Set Page Layout to Landscape
2
3
In the worksheet, insert a Custom Header titled,
“My Personal Budget.”
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.25
This Custom Header
text must be Arial 14
point, Bold, and be
centered on the page.
Text format is Arial 10
point
0.3
Normal text
Requirement
Points
Allocated
Enter column headings :
All column headings
must use the following
text formatting:
BUDGET ITEM, PROJECTED COSTS and the 12
months for the year: JANUARY THROUGH
DECEMBER
4
You may abbreviate the months as follows: JAN,
FEB, MAR, APR, MAY, JUN, JUL, AUG,
SEP, OCT, NOV, DEC
Arial 10 point
0.3
5
All capital letters
Text format is Arial 10
point, Normal.
0.25
Align text Left in the
cell.
A minimum of 9 categories is required.
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.
6
7
0.25
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.
Bold
Text centered in
column
Use all capital letters
Enter your personal budget categories/labels (either
your own or the list provided) in the rows under the
column heading BUDGET ITEM.
Comments
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.
Arial 10 point
0.25
Bold
Black
Align values Right in
column
Enter the label “PROJECTED BUDGET TOTAL:” in
the next row in the BUDGET ITEM column.
Use the following
formats:
Arial 10 point
8
0.1
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
Arial 10 point
0.3
Normal
Requirement
10
Points
Allocated
$3,500.00 or your selected budget goal.)
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).
Black
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.
Use the following text
format:
Align values Right in
the cell
Arial 10 point
0.3
Normal
Align values Right in
the cell
In the next row in the BUDGET ITEM column (under
the PROJECTED BUDGET TOTAL label) enter the
label “Total Monthly Expenses.”
11
12
Comments
0.1
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.
Use the following text
format:
Arial 10 point
Bold
Green
Align text Right in the
cell
0.5
NOTE: do not include empty cells in your
formula.
In the next row under the “Total Monthly Expenses”
label put the label “Projected versus Actuals.”`
13
14
0.1
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
Use the following text
format:
Arial 10 point
Bold
Black
Align text Right in the
cell
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
Enter a column label titled “Total” to the right of your
last month.
15
Format :
0.1
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.
16
NOTE: Be certain to total just the months; do not
include the PROJECTED COSTS column.
Use the
following formats:
Arial 10 point
Bold
Blue
0.5
Align values Right in
the cell –
NOTE: do not enter each cell in the row
individually when using the SUM function
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.
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
0.5
Use the following text
format:
Arial 10 point
17
18
Arial 10 point
Bold
Blue
Align center in cell
Enter a formula using the AutoSum drop-down
option on your tool bar and insert the Average
function of your expenses from January through
Requirement
Points
Allocated
December in the first budget item row, under the
label “Item Average Expense.” Then copy this
formula down for all the other categories.
Bold
Black
Align text right in the
cell
Note: do not enter each cell in the row
individually when using the AVERAGE function
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.
Comments
0.5
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.
Overall, format the
spreadsheet for
readability and clarity.
Be sure font size and
type are used
consistently. Use color
appropriately to
improve the
appearance.
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.
20
0.5
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.
21
Title the pie chart: “My Personal Budget”
0.2
22
Show dollar amounts on each segment of the chart.
0.25
Chart Legend
23
24
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.
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.5
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.
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?
25
25
Use the following text
format:
Arial 10 point
1.0
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.
Normal/Black
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.
Use the following text
format:
Align text left in the
cell
Arial 10 point
1.0
Normal/Black
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.
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?
Align text left in the
cell
Use the following text
format:
Arial 10 point
1.0
Normal/Black
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.
TOTAL
Align text left in the
cell
10

Purchase answer to see full
attachment