Dropbox Assignment 7:
Creating a Budget with Microsoft Excel
Instructions
For this assignment, you will be creating a simple budget in MS Excel, which you can use to keep track of your monthly income and expenses. Then, you will reflect on what you have learned and write a brief paragraph in MS Word. You will also gain experience with spreadsheet concepts you may apply in future classes or projects.
To create your spreadsheet, follow along with these instructions and these screen-capture images.
Please note: You aren’t required to use your real expenses and income. Feel free to substitute your actual expenses and income with made-up figures.
Create a new workbook.
Start MS Excel and create a new workbook. You should use the Blank workbook for this assignment (see Figure 1).
Figure 1: New workbook
Start by creating row headings (2pts).
Begin by listing your expenses (see Figure 2). Your expenses may differ from the example; however, you may find that some of the categories listed apply to you.
Type the following labels into your spreadsheet, beginning in cell A2.
A2: EXPENSES
A3: Payments
A8: Car expenses
A11: Entertainment
A15: Miscellaneous
A16: TOTAL EXPENSES
A18: INCOME
A20: NET
Figure 2: Creating Expense and Income List
Create categories for each heading (2pts).
Next, navigate to B3. In the B column of your spreadsheet, add in categories that apply to you (see Figure 3). The following are example categories:
B3: Rent
B4: Gas
B5: Electricity
B6: Water
B7: Gas
B8: Renters Insurance
B9: Internet
Once you have created a simple list of your expenses and income, organizing them into categories, you may find additional categories or a need to modify some of the categories you originally listed.
Figure 3: Adding Categories
Create column headings for each month (3pts).
Enter January in cell C2 and February into D2, point your mouse to the corner of cell D2, when it changes to a cross hair, copy the rest of the months of the year across that row (see Figure 4). For assistance, you can refer to this resource: Create a list of sequential dates.
Enter your figures for one month (3pts).
Enter the amount of money for monthly expenses and income, starting with January (see Figure 4). Please use your own expense amounts, and if they vary each month, use those to make an accurate account of your expenses.
Figure 4: Entering Monthly Expenses
Add the total expenses using a formula (2pts).
Select the cell where you want to calculate your total Expenses (see Figure 5). In that cell type =SUM and then drag through all the cells that represent your expenses for January. You have created your first formula! The = sign tells Excel that it needs to perform a calculation using what follows. SUM is the actual formula; it adds together the range of cells in brackets, C3 to C15, inclusive, in Figure 5.
Alternatively, you can also type in the formula. It would look like this:
=SUM(C3:C15)
The colon between C3 and C15 means the formula will add up all cells between those, in what’s called the Range.
Figure 5: Calculating the Sum of Expenses for January
Calculate your Income minus your expenses using a Formula (2pts).
Enter your income in cell C18. Next, you can work out how much money is left at the end of the month. This is a much simpler formula that just subtracts the total outgoings from the income. Use the following formula in cell C20 to calculate the Net amount for the month.
=SUM(C18-C16)
Figure 6: Calculating NET
Copy and paste one month’s figures.
Rather than type more figures into the Feb (D) to Dec (N) columns, it’s simpler to copy and paste the Jan (C) column. Click cell C3 and drag the mouse down to cell C20 to select all of the figures (see Figure 7). Then click the Copy button under Home (See Figure 8) to copy the cells you highlighted. The selected range of cells will now be highlighted with a blinking dotted outline.
Figure 7: Copying/Pasting Expenses and Income
Figure 8: Paste, Cut, and Copy Buttons under Home
Paste the figures to the rest of the sheet (2pts).
Finally, click cell D3 and drag your cursor to N20 (see Figure 9). Then click the Paste button under Home to paste the cells into those you highlighted. The numbers from January should copy over to each of the columns representing the remaining months. You can then change any amounts that may vary for specific months (i.e., specific bills due at specific times of year, etc.).
Figure 9: Pasting Cells
Submit your budget and paragraph to Dropbox 7 (4pts).
After completing your budget spreadsheet, write a brief paragraph (of approximately 150 words) using MS Word explaining what you have learned about your saving and spending habits, along with any financial goals you are working toward. Upload both the MS Word document and the MS Excel Budget to Dropbox 7.
1