Spreadsheet (Budget) Assignment

OBJECTIVES & PURPOSE:

The purpose of this assignment is to become familiar with electronic spreadsheet features and techniques including:

Designing and laying out a spreadsheet;
Using label, numberic, and formula cells;
Formatting cells;
Printing and saving spreadsheets;
Demonstrating a spreadsheet's "what if" capabilities;
Incorporating a chart to a spreadsheet;
Obtaining a "formula" printout.

SPECIFICATIONS & INSTRUCTIONS:

PLEASE NOTE:

To receive full credit, all specifications must be meet.
Read the NOTES at the bottom of the assignment before continuing;
Learning how to do this assignment:

The following descriptions are the specifications for the assignment;
They are not the instructions on how to do the assignment;
Discussion and demonstration on how to do the assignment will be given in class on the demonstration day for this assignment (please attend class that day for the demonstration);

Use Microsoft's Excel spreadsheet. If any other spreadsheet other than Excel is used, it may not behave in the same mannor as demonstrated with Excel.
NOTE: These instructions were written for Excel 2003. Later versions of Excel may require different procedures than those listed herebelow.

Using Microsoft's Excel, prepare a budget for the Spring Semester;

You may use your actual budget amounts, or if you wish your finances to remain private, you may use fictitious amounts as long as they are realistic and not nonsense;
Use the examples for this assignment and set up your spreadsheet in a similar and realistic manner, but do not use the same amounts shown in the sample.

The specifications include the following:

The spreadsheet columns include:

Budget Item (description)
January
February
March
April
May
Total (of all five months).

The spreadsheet rows include:
- A row for each type of income;
- A row for total income;
- A row for each type of expense;
- A row for total expenses;
- Finally, a row for surplus-deficit (which is income less expenses);
Use formulas for the following:
- Total Income = sum of all income items for the month;
  (Example: In the sample provided below, the Income items for January are given in cells B8 through B11. The Total Income for January, in cell B12, should be a formula expressed as either:
  =B8+B9+B10+B11
  or
  =SUM(B8:B11)
  Note: the latter formula is using the SUM function.)
- Total Expenses = sum of all expense items for the month;
  (Example: In the sample provided below, the Expense items for January are given in cells B15 through B22. The Total Expenses for January, in cell B23, should be a formula expressed as either:
  =B15+B16+B17+B18+B19+B20+B21+B22
  or
  =SUM(B15:B22)
  Note: the latter formula is using the SUM function.)
- Surplus or Deficit = Total Income - Total Expenses for the month;
  (Example: In the sample provided below, the Surplus or Deficit amount for January is shown in cell B24, which should be a formula expressed as:
  =B12-B23
  because, the Total Income for January is in cell B12 and the Total Expenses for January is in cell B23).
- The "Total" column is the sum of all the amounts of the respective row for January through May;
Format your spreadsheet as follows:
- The "Item" Column is left aligned;
- All other columns are right aligned;
- All headings are bolded;
- All numeric cells are to be formatted as:
  - Numeric;
  - With commas;
  - With 2 places to the right of the decimal;
  - With no dollar signs.
Create a First Version of the spreadsheet in which all "Surplus/Deficit"'s are deficits (negative numbers because the total expenses are greater than their respective total incomes);
Print a copy of the First Version spreadsheet before continuing;
Make the Revised Budget of the spreadsheet as follows:

Do not create a second worksheet; modify your first worksheet and experience playing "what-if" on your first worksheet by following the next steps;

Revise income and/or expenses in the First Version until all Surplus/(Deficit) values are zero (no surplus, no deficit);

Include a chart below the budget which contains the following specifications (instructions are given below):
- Column Chart, "Stacked" or "Stacked 3-D". Do not use "Clustered" or "100% Stacked" Column charts - they will not graph the expenses correctly.
- Each column (in the chart) shows a month's expenses stacked on atop each other.
  Note: After selecting the "Stacked" or "Stacked 3-D" chart and clicking the next botton, make sure the "Series in:" selection is "Rows", and not "Columns"; otherwise, the chart will not display the expenses by month;
- Do not include any of the Total values in the chart - they will distort the chart;
- Do not include the Surplus/(Deficit) values in the chart - they will distort the chart;
- Enter a Title for your Chart (such as "Revised Budget Spring 2012");
- Enter a Legend labeling each monthly expense (series) with text description and color or shade;
- Label the Axes for Month & Dollars (the "X" axis is to be labled as "Month", and the "Z" axis is to be labled as "Dollars");
- Include Major Grids.
If using Excel 2000/2003:
- Mark the expenses to be charted;
- Click the Chart Wizard tool in the Tool Bar,
- Select Column Chart > Stacked Column or Stacked Column 3-D and click the Next button;
- Select the Series in "Rows" radio button,
- Click the "Series" tab;
  - Name each series with its respective Expense name;
  - Click the Next button;
- In the "Titles" tab:
  - Enter the Chart Title (such as "Budget Spring Semester");
  - Enter "Month" for the Category (X) axis;
  - Enter "Dollars" for the Value (Z) axis;
- Click the "Finish" button;
- Drag the chart to a position below the spreadsheet.
If using Excel 2007/2010:
- Mark the expenses to be charted;
- Begin the chart by clicking Insert tab > Column (Charts) > Stacked Column or Stacked Column in 3-D or Stacked Cone or Stacked Cylinder or Stacked Pyramid);
- Drag the chart to a position below the Budget;
- Rearrange columns so columns are stacked by month (not by series):
  - Right click on grids of chart;
  - Click on Select Data
  - Click on the Switch Row/Column button
  - Label each Legend Entry (Series) as an Expense (as labeled in the Budget Spreadsheet);
  - Label each Horizontal (Category) Axis as a Month:
    - Click the Edit Icon;
    - Enter the months in the Axis Labels text box as "Jan,Feb,Mar,Apr,May"
  - Add the Title to the chart:
    - Click Chart Tools > Layout
    - Click Chart Title > Above Chart
    - Enter the Title in the Chart Title text box
    - Click outside the Chart area (or the Print command will print only the chart).

ASSIGNMENT SUBMISSION:

Include the following hardcopies (printouts) on seperate pages stapled together in the order given below as your assignment:

The First Version printout of your budget made in the step above showing:
- All deficits (all negative numbers) on the row "Surplus/Deficit". Deficits may appear with a negative sign or inside parentheses (such as -123.45 or (123.45) );
- Hand write your Name and ID#, Section#, and Assignment# on the top right corner of this page of the assignment;
The Revised Version (which includes the chart) showing:
- The changes made in the First Version that result in a balanced budget, i.e., all amounts in the "Surplus/Deficit" row must be zero (no surplus and no deficit). Values may appear as 0.00 or as simply a dash (accounts' way of stating zero);
- The chart below the spreadsheet.
The "Formula" Printout - on Excel:
- Press Ctrl~ (ctrl-tilde) to switch to formula view;
- From this view, print the "Formula" printout;
- Press Ctrl~ (ctrl-tilde) again to return to regular spreadsheet view.
Please Note: The formula printout may print in parts on several pages. This is normal and OK! Do not be concerned if the formula printout does print on several pages.

NOTES:

All specifications must be met to receive full credit.
Use Microsoft's Excel spreadsheet. If any other spreadsheet other than Excel is used, it may not behave in the same mannor as demonstrated with Excel.
The "First Version" and "Revised Version" printouts are to be on seperate pages! Use the same spreadsheet for both versions by simply revising the the first version to produce the second version. This is to demonstrate using the "what if" capabilities of a spreadsheet program.
The formula printout may appear on several pages. This is normal, and do not be alarmed by it.

GRADING:

The instructor and/or Grader will be verifying that all specifications stated above in SPECIFICATIONS AND INSTRUCTIONS as well as ASSIGNMENT SUBMISSION were met to receive full credit. Specifications missing will be the basis of deducted points. Items the instructor or grader examine include (but are not limited to):

DOCUMENT:
- Versions:
  - Version Two is actually the same spreadsheet origionating as Version One (with changed amounts), and not a seperate document (this is to demonstrate the "what-if" scenario capability of a spreadsheet);
  - Version Two includes the stacked bar chart.
- Formats:
  - First column labels are:
    - bolded;
    - Left aligned.
  - Row with Column Titles (such as January, February, etc.):
    - bolded;
    - Right aligned.
  - Numeric cells:
    - Decimal point;
    - 2 digits to the right of the decimal point;
    - Commas seperating the thousands.
- Formulas:
  - Each cell that represents an arithmetic total of other cells must be a formula;
- Chart:
  - Chart appears below Version Two of the budget;
  - The chart is a stacked bar chart as specified above;
  - The chart includes:
    - Only expenses are charted;
    - No totals are charted;
    - Each stacked column represents the expenses for one month;
    - Title showing;
    - Horizontal axis labeled;
    - Vertical axis labeled;
    - The Legend must identfy an expense by name;
PRINTOUTS:
1. Version One spreadsheet showing deficits for all months;
2. Version Two spreadsheet showing:
3. Formula Printout of spreadsheet.

Click Here To See A Sample Of This Assignment

Click Here To See More About Spreadsheet Concepts

Click Here To Return To Class Page