Excel Project

Page 1 of 6

Microsoft Excel Project

Purpose

The purpose of th s ass gnment s for students to demonstrate prof c ency n M crosoft Exce by creat ng

a spreadsheet that will be used to manage their own personal budget. Please note that you do not have

to include actual values for your income and expenses; you can make up values, but they should be

realistic.

Before attempting to design the spreadsheet in Microsoft Excel, students should search the Web for

sample personal budgets to learn how they might be organized in a spreadsheet. We will not provide

samp es of what the f n shed product w ook ke. A ma n object ve of th s ass gnment s to

demonstrate how to proper y organ ze data n an Exce spreadsheet. M crosoft Off ce He p on ne

resources, and your instructors can help to provide proper guidance.

Content Requirements

The spreadsheet should contain, in a logical format, the following information.

1. The first part of the spreadsheet should show your income each month, for a 12-month period,

that comes from all income sources. An example is below:

Income Jan. Feb. Mar. Apr. May June July Aug. Sep. Oct. Nov. Dec.

Employer $440 $400 $500 $560 $440 $550 $250 $390 $500 $440 $550 $300

Interest $2 $2 $2 $2 $2 $2 $2 $2 $2 $2 $2 $2

Assistance Parental $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100

2. In a new row at the bottom of your ncome nformat on nc ude a row that w d sp ay the tota

ncome per month

3. In a new column on the right side of your income information, include a column that will display

the tota ncome per category

4. The second part of the spreadsheet shou d show your est mated mandatory expenses each

month, for a 12-month period. There should be some varying values, so you do not end up with

a of the same va ues for every month n every category. Mandatory expenses m ght nc ude

rent or house payments grocery b s ut t es and car payments but not necessar y anyth ng

related to entertainment. An example is below:

Expenses Jan. Feb. Mar. Apr. May June July Aug. Sep. Oct. Nov. Dec.

Rent $500 $500 $500 $500 $500 $500 $500 $500 $500 $500 $500 $500

Car Pymt. $170 $170 $170 $170 $170 $170 $170 $170 $170 $170 $170 $170

Utilities $60 $60 $60 $60 $60 $90 $90 $90 $90 $60 $60 $60

Cell Phone $50 $50 $50 $50 $50 $50 $50 $50 $50 $50 $50 $50

Groceries $50 $60 $45 $50 $65 $50 $45 $50 $50 $50 $80 $80

5. In a new row at the bottom of your expense nformat on nc ude a row that d sp ays the tota

expenses per month. To receive credit for this step, you must use an Excel formula or function

to ca cu ate the tota wh ch shou d automat ca y reca cu ate f the va ues n the ce s are

mod f ed.

Page 2 of 6

6. In a new column on the right side of you expense information, include a column that will display

the tota expense per category.

7. The th rd area on your spreadsheet shou d cons st of two rows the f rst row w show your 12-

month period, the second row will later use a formula to calculate, for each month, how much

extra money you w have or how much money you are short. Th s s your net ncome after

your mandatory expenses

8. The fourth area to your Excel spreadsheet, which will look similar to your area showing your

mandatory expenses that shows opt ona expenses. Opt ona expenses m ght nc ude categor es

such as enterta nment d n ng out and contr but ons to your sav ngs and/or other nvestments.

There should be some varying values, so you do not end up with all of the same values for every

month, in every category. Include a total row and total column, similar to what you did for your

ncome and expenses. In th s area of your spreadsheet you on y shou d budget what you have

ava ab e to spend. For examp e n the prev ous step f you found you have $200 extra one

month, you only should spend up to $200 in optional expenses

9. The ast area w need to have two rows the f rst row w show your 12-month per od the

second row w ater use a formu a to ca cu ate for each month your month eft over after a

mandatory and optional expenses so that you can see how much extra money you might have

(or the money you are short) at the end of the year.

Techn ca Requ rements

In add t on to meet ng the content requ rements for th s ass gnment you a so w need to demonstrate

your proficiency of Microsoft Excel by applying the following formatting. Please note that you should

not perform any calculations manually; if a cell should contain a calculated value, use a function or

formula to calculate that value. The technical instructions are intended to be completed in order.

 Change the worksheet tab so that it displays “Monthly Budget” instead of “Sheet1.” Also,

change the tab color to one of your choosing

 Insert a row to create a t t e on your worksheet. Change the font font s ze font co or and f

co or from the defau t va ues and then Merge & Center the ce across the top of the worksheet

 Add appropr ate abe s above each of the f ve areas of the worksheet to dent fy the

nformat on. For examp e for the tab e d sp ay ng the ncome you m ght nsert a row above the

table containing text that reads, “Monthly Income” to identify the information. This text also

shou d be formatted us ng a d fferent font font co or and font s ze from the defau t so that t

stands out

 For the Income, Mandatory Expense and Optional Expense areas, use an Excel formula or

funct on to ca cu ate a of the tota rows (ca cu at ng the tota for each month) and tota

co umns (ca cu at ng the tota for each category).

o A tota ce s must nc ude ce references n the formu a. The tota shou d automat ca y

update f you change any of the va ues nc uded n the formu a

 For the net ncome area use an Exce formu a or funct on to ca cu ate how much extra money

you will have, or how much money you will be short (Income – Mandatory Expenses)

Page 3 of 6

 For the final area in your spreadsheet, use an Excel formula or function to calculate how much

money you w have (or f you are short) at the end of the year (Income – Mandatory Expenses –

Opt ona Expenses)

 A Co umn W dths shou d be set to proper y d sp ay a contents n the co umn (noth ng shou d

be cut off or d sp ay ng unnecessary symbo s and co umns shou dn t be so w de that there s a

lot of blank space in each cell)

 For a ce s that conta n a do ar va ue app y the Account ng Number Format

 For the ast area of your spreadsheet app y cond t ona formatt ng to each ce n th s area. You

should use the Conditional Formatting feature, and not manually format each cell based on its

va ue.

o The font co or shou d be green f the va ue n the ce s greater than zero

o The font co or shou d be green f the va ue s equa to zero

o The font co or shou d be red f the va ue n the ce s ess than zero.

o A three formats shou d be app ed to a ce s as they shou d automat ca y change font

co or f the va ues are mod f ed.

 Create ce s n your worksheet (near the tab e show ng your mandatory month y expenses) that

use Exce funct ons to ca cu ate the fo ow ng us ng your mandatory month y expense tota s

o The tota from the month where the mandatory month y expenses are the owest

o The tota from the month where the mandatory month y expenses are the h ghest

o The average amount of money you spend on mandatory expenses n a 12-month per od

 In a new worksheet (not a new workbook) create two charts (both charts shou d d sp ay s de-

by-s de on the same new worksheet)

o The f rst chart shou d be a 3-D Co umn Chart that shows the ncome you rece ve each

month. The hor zonta ax s shou d d sp ay the Months and the vert ca ax s shou d

d sp ay the do ar va ues. Inc ude an appropr ate chart t t e and data abe s. Each co umn

n the chart shou d be formatted as a d fferent co or. An examp e s be ow (your chart

does not need to look exactly like this; the purpose of this sample chart is to help clarify

the instruction):

Page 4 of 6

o The second chart shou d be a p e chart dep ct ng your mandatory expenses. The who e

p e shou d represent the tota amount you spend n mandatory expenses dur ng the 12-

month period, and each slice will represent the total monthly expense for each

category. If you have f ve categor es of mandatory expenses then your p e chart w

have f ve s ces. Inc ude a descr pt ve chart t t e and egend. The egend shou d d sp ay

below the pie chart. Display data labels for each slice, and position them for best fit. An

examp e s be ow (your chart does not need to ook exact y ke th s the purpose of th s

samp e chart s to he p c ar fy the nstruct on)

$600

$800

$1,000

I $1,200

n

o

m

e $400

A $200

m $0

o

u

n

t Month

Monthly Income

$6,000 00

$2,040 00

$840 00 $600 00

Mandatory Expenses

Rent Car Pymt Utilities Groceries

Page 5 of 6

 Rename the worksheet tab for the worksheet containing the chart to “Charts”, and set the tab

co or to someth ng other than the defau t (make sure the tab co or s a so d fferent than the

“Month y Budget” tab)

 For the tab es n the Month y Budget worksheet d sp ay ng your ncome mandatory expenses

and opt ona expenses (these shou d be three separate tab es) use Exce to app y a Tab e Sty e.

Then for each table, remove the data filters

 If the Tab e Sty e you chose d d not bo d the va ues n the tota rows and co umns then manua y

bo d the va ues n a tota rows and co umns

 Delete any worksheets from the workbook that do not contain any data or information.

 Run a Spe ng & Grammar check to make sure your workbook s free of spe ng and

grammat ca errors

 In the Properties for this spreadsheet, make sure your full name appears in the Author property

(if it does not, change it), and that the title of this spreadsheet appears in the Title property

Subm ss on Gu de nes

It s mportant for students to pay c ose attent on to the subm ss on gu de nes n order to rece ve fu

credit for this assignment.

 Save the f e as MSExce ProjectLastNameF rstName.x sx (where LastNameF rstName shou d be

rep aced w th your ast and f rst name)

 Close the file after saving it. Otherwise the file will not attach properly to the email message.

 Submit the file to Canvas.

Important Notes

 Start the ass gnment we n advance of the due date. Last m nute prob ems on your end w not

be an excuse for m ss ng a dead ne

 Do not use anyone e se s work. After we rece ve a ass gnments we w run them through an

automated process to check for p ag ar sm. Any v o at ons or any p ag ar sm w resu t n a zero

on this assignment and possible further disciplinary action by the College. It is better to miss

turn ng n an ass gnment (or to turn n an ncomp ete ass gnment) and rece ve a ower grade

than to r sk go ng through a Student Conduct rev ew process

 Using a Mac version of Microsoft Office is entirely at your own risk. If the Mac version does not

a ow you to perform certa n steps out ned n th s document you w ose po nts for those steps

 P ease e-ma your nstructors w th any quest ons

Rubric

Cr ter a Ava ab e Po nts

Create a t t e for your worksheet , merge and center t 3

Page 6 of 6

Us ng an exce formu a ca cu ate the amount of money you spend dur ng the month where the mandatory expenses are the owest

4

Us ng an exce formu a ca cu ate the average amount of money you spend dur ng the 12 month per od

4

Create a P e Chart show ng the Mandatory expenses by category w th t t e and the egend be ocated at the bottom of the chart

8

Labe the f ve areas of your worksheet, mod fy font s ze, co or, f co or 3

Create three tab es of your ncome, mandatory expenses and opt ona expenses 9

App y a Tab e Sty e to each tab e and remove data f ters 3

Bo d the tota rows and co umns 3

Use the Account ng Number Format for a va ues us ng the $ s gn 3

nc udes rows show ng your ncome for 12 months 6

Use an exce formu a or funct on to ca cu ate the tota ncome 3

nc udes rows show ng your vary ng expenses for 12 months 6

Use an exce formu a or funct on to ca cu ate the tota expenses 3

Use a formu a or funct on to ca cu ate the tota of each expense category 3

Ca cu ate your month y net ncome oss us ng an exce formu a 3

Create an area show ng opt ona expenses vary ng on y up to the amount of your net ncome

6

Use an exce formu a to ca cu ate your money eft over after opt ona expenses 3

Format your f na net ncome oss w th a 3 cond t ona formatt ng. Green text >= 0 and Red < 0

6

Us ng an exce formu a ca cu ate the amount of money you spend dur ng the month where the mandatory expenses are the h ghest

4

Create a 3D Co umn Chart show ng your month y ncome w th t t e and data abe s. Each co umn shou d have a d fferent co or

8

Change the worksheet tab to Month y Budget and add a tab co or 3

Rename the second worksheet to Charts and add a tab co or 3

De eted any add t ona worksheets 3

Ass gnment not cover ng the nstructed top c -100

Tota Po nts 100