Microsoft Excel Project

Purpose
The purpose of this assignment is for students to demonstrate proficiency in Microsoft Excel by creating 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 samples of what the finished product will look like. A main objective of this assignment is to demonstrate how to properly organize data in an Excel spreadsheet. Microsoft Office Help, online resources, and your instructors can help to provide proper guidance.

Content Requirements

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

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
Parental $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100
Assistance

  1. In a new row at the bottom of your income information, include a row that will display the total
    income per month
  2. In a new column on the right side of your income information, include a column that will display
    the total income per category
  3. The second part of the spreadsheet should show your estimated mandatory expenses each
    month, for a 12-month period. There should be some varying values, so you do not end up with
    all of the same values for every month, in every category. Mandatory expenses might include
    rent or house payments, grocery bills, utilities, and car payments, but not necessarily anything
    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
  4. In a new row at the bottom of your expense information, include a row that displays the total
    expenses per month. To receive credit for this step, you must use an Excel formula or function

to calculate the total, which should automatically recalculate if the values in the cells are modified.

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

The third area on your spreadsheet should consist of two rows: the first row will show your 12-month period, the second row will later use a formula to calculate, for each month, how much extra money you will have, or how much money you are short. This is your net income after your mandatory expenses

The fourth area to your Excel spreadsheet, which will look similar to your area showing your mandatory expenses, that shows optional expenses. Optional expenses might include categories such as entertainment, dining out, and contributions to your savings and/or other investments. 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 income and expenses. In this area of your spreadsheet, you only should budget what you have available to spend. For example in the previous step if you found you have $200 extra one month, you only should spend up to $200 in optional expenses

The last area will need to have two rows: the first row will show your 12-month period, the second row will later use a formula to calculate, for each month, your month left over after all 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.

Technical Requirements

In addition to meeting the content requirements for this assignment, you also will 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 title on your worksheet. Change the font, font size, font color, and fill color from the default values, and then Merge & Center the cell across the top of the worksheet

Add appropriate labels above each of the five areas of the worksheet to identify the information. For example for the table displaying the income, you might insert a row above the table containing text that reads, “Monthly Income” to identify the information. This text also should be formatted using a different font, font color, and font size from the default so that it stands out

For the Income, Mandatory Expense and Optional Expense areas, use an Excel formula or function to calculate all of the total rows (calculating the total for each month) and total columns (calculating the total for each category).

o All total cells must include cell references in the formula. The total should automatically update if you change any of the values included in the formula

For the net income area, use an Excel formula or function to calculate how much extra money you will have, or how much money you will be short (Income – Mandatory Expenses)

For the final area in your spreadsheet, use an Excel formula or function to calculate how much money you will have (or if you are short) at the end of the year (Income – Mandatory Expenses – Optional Expenses)

All Column Widths should be set to properly display all contents in the column (nothing should be cut off or displaying unnecessary symbols, and columns shouldn’t be so wide that there is a lot of blank space in each cell)

For all cells that contain a dollar value, apply the Accounting Number Format

For the last area of your spreadsheet, apply conditional formatting to each cell in this area. You should use the Conditional Formatting feature, and not manually format each cell based on its value.

o The font color should be green if the value in the cell is greater than zero o The font color should be green if the value is equal to zero

o The font color should be red if the value in the cell is less than zero.

o All three formats should be applied to all cells, as they should automatically change font color if the values are modified.

Create cells in your worksheet (near the table showing your mandatory monthly expenses) that use Excel functions to calculate the following using your mandatory monthly expense totals:

o The total from the month where the mandatory monthly expenses are the lowest o The total from the month where the mandatory monthly expenses are the highest

o The average amount of money you spend on mandatory expenses in a 12-month period

In a new worksheet (not a new workbook), create two charts (both charts should display side-by-side on the same, new worksheet):

o The first chart should be a 3-D Column Chart that shows the income you receive each month. The horizontal axis should display the Months, and the vertical axis should display the dollar values. Include an appropriate chart title and data labels. Each column in the chart should be formatted as a different color. An example is below (your chart does not need to look exactly like this; the purpose of this sample chart is to help clarify the instruction):

Monthly Income

$1,200
$1,000
c

$800

o

$600
$400

$200

A

m $0

o
u
n
t Month

The second chart should be a pie chart depicting your mandatory expenses. The whole pie should represent the total amount you spend in mandatory expenses during the 12-month period, and each slice will represent the total monthly expense for each category. If you have five categories of mandatory expenses, then your pie chart will have five slices. Include a descriptive chart title and legend. The legend should display below the pie chart. Display data labels for each slice, and position them for best fit. An example is below (your chart does not need to look exactly like this; the purpose of this sample chart is to help clarify the instruction):

Mandatory Expenses

$600.00

$840.00

$2,040.00

$6,000.00

Rent Car Pymt. Utilities Groceries

Rename the worksheet tab for the worksheet containing the chart to “Charts”, and set the tab color to something other than the default (make sure the tab color is also different than the “Monthly Budget” tab)

For the tables in the Monthly Budget worksheet displaying your income, mandatory expenses, and optional expenses (these should be three separate tables), use Excel to apply a Table Style. Then for each table, remove the data filters

If the Table Style you chose did not bold the values in the total rows and columns, then manually bold the values in all total rows and columns

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

Run a Spelling & Grammar check to make sure your workbook is free of spelling and grammatical 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

Submission Guidelines

It is important for students to pay close attention to the submission guidelines in order to receive full credit for this assignment.

Save the file as MSExcelProjectLastNameFirstName.xlsx (where LastNameFirstName should be replaced with your last and first name)

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

Submit the file to Blackboard.

Important Notes

Start the assignment well in advance of the due date. Last minute problems on your end will not be an excuse for missing a deadline

Do not use anyone else’s work. After we receive all assignments, we will run them through an automated process to check for plagiarism. Any violations or any plagiarism will result in a zero on this assignment and possible further disciplinary action by the College. It is better to miss turning in an assignment (or to turn in an incomplete assignment) and receive a lower grade than to risk going through a Student Conduct review process

Using a Mac version of Microsoft Office is entirely at your own risk. If the Mac version does not allow you to perform certain steps outlined in this document, you will lose points for those steps

Please e-mail your instructors with any questions

Rubric

 Criteria            Available Points     

 Create a title for your worksheet , merge and center it         3    

Label the five areas of your worksheet, modify font size, color, fill color 3

 Create three tables of your income, mandatory expenses and optional expenses            9    

Apply a Table Style to each table and remove data filters 3

 Bold the total rows and columns         3    

Use the Accounting Number Format for all values using the $ sign 3

 Includes rows showing your income for 12 months         6    

Use an excel formula or function to calculate the total income 3

 Includes rows showing your varying expenses for 12 months           6    

Use an excel formula or function to calculate the total expenses 3

 Use a formula or function to calculate the total of each expense category           3    

Calculate your monthly net income (loss) using an excel formula 3

 Create an area showing optional expenses varying only up to the amount of your          6    
 net income           

Use an excel formula to calculate your money left over after optional expenses 3

 Format your final net income (loss) with all 3 conditional formatting. Green text           6    
 (>= 0) and Red (< 0)             

Using an excel formula calculate the amount of money you spend during the 4
month where the mandatory expenses are the lowest

 Using an excel formula calculate the amount of money you spend during the           4    
 month where the mandatory expenses are the highest           

Using an excel formula calculate the average amount of money you spend during 4
the 12 month period

 Create a 3D Column Chart showing your monthly income with title and data            8    
 labels. Each column should have a different color            

Create a Pie Chart showing the Mandatory expenses by category with title and 8
the legend be located at the bottom of the chart

 Change the worksheet tab to Monthly Budget and add a tab color          3    

Rename the second worksheet to Charts and add a tab color 3

 Deleted any additional worksheets           3    

Assignment not covering the instructed topic -100

 Total Points            100  
Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Benefits of our college essay writing service

  • 80+ disciplines

    Buy an essay in any subject you find difficult—we’ll have a specialist in it ready

  • 4-hour deadlines

    Ask for help with your most urgent short tasks—we can complete them in 4 hours!

  • Free revision

    Get your paper revised for free if it doesn’t meet your instructions.

  • 24/7 support

    Contact us anytime if you need help with your essay

  • Custom formatting

    APA, MLA, Chicago—we can use any formatting style you need.

  • Plagiarism check

    Get a paper that’s fully original and checked for plagiarism

What the numbers say?

  • 527
    writers active
  • 9.5 out of 10
    current average quality score
  • 98.40%
    of orders delivered on time
error: