Budget for Spreadsheet Project

Purpose: Create a spreadsheet showing the revenues and expenditures for each month along with totals for the year.

1. Open Excel or MS Works (spreadsheet)

2. Label Row and Column headings

First column (down) Beginning balance, Check Deposits, Credit Card Deposits, Total Deposits: Ending Balance

First Row (start from B, across) January through December, Annual Totals

3. Input data

Beginning balance (from previous year: $4,215.07

Check deposits (January - December) Jan. $6,231.00 Feb. $2,761.00 Mar. $4,356.00 Apr. $2,796.00 May $2,613.00 Jun.$2,045.00 July $3,077.00 Aug. $6,123.00 Sept. $1,578.00 Oct. $2,644.00 Nov. $765.94 Dec. $978.00

Credit Card deposits (Jan - Dec.) Jan.$2,013.00 Feb.$1,987.00 Mar.$2,098.00 Apr.$857.00 May$120.00 June.0 July.$1,980.00 Aug.$176.00 Sept.0 Oct.$678.00 Nov.$987.00 Dec.$231.00

Expenditures:

Checks: Jan.$2,554.64 Feb.$7,765.84 Mar.$7,786.38 Arp.$4,376.55 May.$5,743.80 Jun.$1,435.23 Jul.$5,986.33 Aug.$7,658.53 Sep.$987.75 Oct.$2,542.55 Nov.$2,254.76 Dec.$1,453.14

To show the benefits of a spreadsheet, in cell B8, type in the following: =sum(

Then drag your cursor over B6 and B7. Then close the formula with a ).

Cell B8 should look like this =sum(B6:B7)

To replicate that formula from January to December, highlight B8 with your cursor and drag across row 8 to December (M8). Then under "Edit" in the menu bar, scoll down to "Fill" and then move your cursor to "right." This can be done "down" or "right" to replicate your formulas or actions.

Do the same replication with the "ending balance. Figure out the ending balance for January, then replicate the process for the rest of the months.

4. Create a column chart showing the ending balance for each month of the year.

a. Highlight your entire spreadsheet, e.g., A-5 to M10

b. Click on the "chart" icon in the menu bar under "window"

c. The chart maker will default to the column chart, and click "next"

d. In step 2 of 4, in the box on the left, remove all the rows except "ending balance" and click "next" to go to step 3 of 4

e. Category "X" is the horizontal axis, e.g., bottom of the chart. Type in "Months"

f. Category "Y" is the vertial axix, e.g., the left side of the chart. Type in "Ending Balance in Dollars."

g. Click on the "Legends" tap and remove the check in the "Show Legends" box.

h. Click on "next" and move to step 4. Make sure the chart in included in "Sheet 1."

i. Click on "Finish" (Congratulations!)

5. Send, via email, the spreadsheet and chart to jmckay@mail.unomaha.edu