1. technology and computing
2. programming languages
3. visual basic

Assignment Grading Rubric
Course: IT133 Unit: 6
Designing a personal budget spreadsheet (Parts 1 and Part 2)
Points = 65
Scenario Part 1
You have decided that it might be a good idea to create a personal household budget. You want to be able to
save money to purchase a house after you graduate. You decide to create a personal budget. You may either
gather your pay stubs and the bills you pay each month or create fictional expenses to complete this
assignment.
Assignment Details Part 1
1. Open Microsoft® Excel® 2010. You will begin with a new worksheet.
2. Create and format row 1 to show the months for the year (Jan–Dec).
a. Begin adding months in cell B1
3. Create and format row 3, to show monthly income for each month (Jan–Dec),
a. Label the row values as currency, 2 decimal places, and a dollar sign.
4. Create and format a row for each expense (as identified in step 5) and do not format to currency.
5. For the expenses create a year's history by month. Use different values for each month. For example,
for electric, you would pick a number between $125 and $142 for January (or use your actual data), a
different number between $125 and $142 for February, and so on. For income the amount can be the
same.
6. Select an appropriate style for your budget and apply it to your worksheet.
Copyright Kaplan University
Assignment Grading Rubric
Course: IT133 Unit: 6
Assignment Details Part 2
Now that you have all the appropriate budget data added to your workbook, you want to analyze it to determine
how much your expenses are each month and year as well as how much you make per year to determine if
you are spending too much and where you are spending it.
Important: You will continue to work on this workbook in Unit 7. So please wait until you receive your Unit 6
graded assignment back from your professor before beginning on Unit 7.
NOTE: For this part you will add formulas to your workbook. In the following unit you will create a chart to show
your expenses.
7. Create a formula or use the AutoSum function to have Excel calculate the total the expense for each
month.
8. Create a formula or use the AutoSum function to have Excel compute the Total Annual Income.
9. Create a formula or use the AutoSum function to have Excel calculate the Total Annual Expenses.
10. Create a formula to determine the percentage of income used for each annual expense total.
a. Be sure to format it as a percentage. Place that formula in the column next to the total of each
monthly expense. (GEL 2.3)
11. At the bottom of your budget add the following three functions to calculate the following based on the
monthly expenses:
a. Min
b. Max
c. Average
12. In cell A1 add a function to show the current date
13. Rename your worksheet tab to Budget Detail.
Save your workbook and submit it to the appropriate dropbox.
Review the grading rubric below before beginning this Assignment.
Copyright Kaplan University
Assignment Grading Rubric
Course: IT133 Unit: 6
Challenge Levels
Anyone up for a challenge? This course offers you the opportunity to go beyond the required course
material. Two Challenge Levels are offered to supercharge your coursework: Whiz and Guru.
Challenge Levels are a great way for you to learn more about Microsoft Office, better improve your
existing skills and abilities, and/or highlight how knowledgeable you are in the unit material. These levels
do not count for additional points or guarantee a higher grade.
Challenge Levels offer you an opportunity to stand out, learn more, and gain confidence.
To start, follow the rubric and Assignment instructions as normal. If you choose to try it, supplement your
Assignment with the challenge levels. Reach out to your instructor and do some research for resources.
The "Whiz" level adds to the basic level, and the "Guru" level adds more challenges to the "Whiz" level.
If you start a challenge level, but it becomes too difficult to handle, please stick with the basic Assignment
instructions, as found in the rubric to calculate your grade. Most importantly—have fun with it!
Your Challenge Levels for this unit:
Excel Whiz
Use a function to place the day of the week (i.e., Monday or Tuesday, etc.) based off your TODAY function
in cell A1. Place this function in cell A21.
Excel Guru
In addition to the Excel Whiz challenge. Apply conditional formatting to all of your monthly expenses to
meet the following requirements: Greater than 100 with light red fill and dark red text. Finally, add data
bars to your MIN, MAX, and Average calculations. Use any color you like.
Assignment 6 grading rubric = 65 points
Assignment Requirements
1. Create and format row 1 to show the
months for the year (Jan–Dec).
2. Create and format row 3, to show monthly
income for each month (Jan–Dec),
a. Label the row values as currency, 2
decimal places, and a dollar sign.
3. Add each expense in a separate row (as
identified in step 5) and properly label each
Copyright Kaplan University
Maximum
points possible
for satisfactory
completion of
task
0–6
0–6
0–3
Points earned
by student
Assignment Grading Rubric
Course: IT133 Unit: 6
row and value.
4. For the expenses and income, create a
year's history by month. Use different
values for each month. For example, for
electric, you would pick a number between
$125 and $142 for January, a different
number between $125 and $142 for
February, and so on. Feel free to make
up the amounts you use for each expense
5. Select an appropriate style for your budget
and apply it to your worksheet.
6. Create a formula or use the AutoSum
function to have Excel calculate the total
the expense for each month. Use functions
to determine MIN, MAX, and AVERAGE of
monthly expenses.
7. Create a formula or use the AutoSum
function to have Excel compute the Total
Annual Income.
8. Create a formula or use the AutoSum
function to have Excel calculate the Total
Annual Expenses. Add a function to show
current date in cell A1.
9. GEL-2.3 -Create a formula to determine
the percentage of income used for each
expense. Be sure to format it as a
percentage. Place that formula in the row
next to the total of each monthly expense
10. Rename your worksheet tab to Budget
Detail.
Total (Sum of all points)
Copyright Kaplan University
0–6
0–6
0–10
0–10
0–10
0–6
0–2