Chapter 10 ~ How to Finance
Your Greatest Dream
In this century, it is conceivable that we can do whatever we want with our lives: make a purchase, change a lifestyle, travel, or develop a new skill. Anything is possible if one has support, time, and money.
So begin to dream big! What do you want out of life? What fun do you want to engage in?
Dr. Lissa Rankin wrote in Psychology Today a list of 15 tips for dreaming big. The most important, in my opinion, is to ask yourself “What would you do if you took fear out of the equation?” She also reminds you to squelch your inner critic and try to visualize the dream. (Rankin 2010) Some dreamers create vision boards or surf the net looking for ideas. In this assignment, we'll visualize the dream in a spreadsheet.
No, you will not have to act on this dream for this class! Let's just pretend. In the process, you'll learn about yourself, what other people have done to achieve the same dream, and how you can meet financial goals.
You'll start by assessing your current monthly income and expenses, then move onto documenting a single dream.
⬆ Shiftrefresh Shift-Refresh this page to see the most up-to-date instructions.
Personal Finances
Print these instructions.
The following procedure has many steps, which are perhaps easier to follow when you print and check them off as you go. Hide/close + the previous section to avoid printing too many pages.
Need the Instructor's help?
If you have questions about completing the technical skills or the writing, you can share your file with the instructor.
- Share a Google file:
- With your file open, click the Sharing button at the top right of the screen. Or, if the file is not open but listed in the Drive, right-click it to choose the Share menu.
- Click the Get Link box to update the OSU Settings and copy the link:
- Ignore the Share with People and Groups box for this course.
- Paste the URL into the Canvas Inbox message or Assignment Comment box, along with your questions. Note which step number you're stuck on.
- Share a Microsoft file:
- Login to your OSU OneDrive account from the browser.
- Drag the file from your hard drive to the OSU OneDrive file list in the browser to transfer it there.
- Beside the file name in the list, click the icon:
- Choose the settings provided in the screenshot below:
- Copy the link so you can provide it to the instructor or in the Assignment Comment box.
- Paste the URL into the Canvas Inbox message or Assignment Comment box, along with your questions. Note which step number you're stuck on.
10.1 Design an income and expenses worksheet
In this lesson, you'll design a simple spreadsheet to document average income and expenses for a typical month. This becomes your starting point. This is not a budget; it is a description of your current state of financial affairs. Use real amounts whenever possible but don't add account names or numbers. No one else will see your data but the Instructor and TAs. Do count expenses paid by family members as income...you'll see how below.
- Launch a spreadsheet application:
- Microsoft Excel. Recommended for Business and Science-related majors. Brittani Palumbo demonstrates getting started with Excel. (2019)
- Google Sheets. Recommended for inexperienced students who are not Business majors.
- Apple Numbers. Not all of the required tasks are possible using this application. No technical support will be provided.
- Open a Blank worksheet file and save the filename as Personal Finances.
- Name the worksheet Income Expenses by double-clicking the untitled tab at the bottom.
- Type the following in Column A:
Income Job Family Financial Aid Savings Total Income Expenses Tuition Books Rent Water/Sewer Electric Power Phone service TV and Internet service Groceries, dining, drinks Car loan Gas and oil changes Car insurance Entertainment Hair Cuts Pets Medical Savings account Other Loan Credit cards Total Expenses Total Discretionary
Don't leave any of these row headings out. If you don't have those expenses, you'll give them a 0 zero amount later. - Stretch column A to the right so that all of the longer row headings don't spill into the next column. Stretch row 7 Expenses down a bit so it creates space between it and the Total Income heading. Do the same for row 26 or 27 Total Discretionary.
- Click on the Row 1 selector on the left. Apply Bold using Cntlb or ⌘b.
Do the same for these rows: Total Income, Expenses, Total Expenses
and Total Discretionary.
Bold the entire row, not just the heading.
- Label Column B with Monthly.
- Click on the Column A and B column selectors to select all rows in both columns.
Then, click the align right button.
- Click on the Column B column selector and choose the $ financial dollar sign button to apply dollar signs to the numbers.
- While Column B is still selected, choose the .0 ← or .0 → icon to remove the decimal places.
Even if you typed dollars and cents using a decimal, this button will remove the cents so you display only whole numbers.
10.2 Add and calculate income data.
For each item in Column A's Income section, add an actual amount in Column B.
- For the Job B2 cell, type the typical monthly income from jobs you hold.
If you get paid weekly or bi-weekly instead of monthly, then click into the cell and use math to add two or more paycheck amounts together, like this:
=400+200. The final number in the B2 cell will be 600.
If you have, for example, a job where you earn minimum wage, you could calculate
$10.25 per hour for 20 hours per week for 4 weeks using this math:
=(10.25*20)*4.
- For the Family B3 cell, add a 0 zero if family doesn't provide help with any expenses. If they do help, then wait until you add those expense amounts later, before using a function to get the total.
- For the Financial Aid B4 cell, click in the cell to enter the monthly amount received for any given term. If you get a lump sum per term, then use math in that cell to divide the amount by 3 months, like this: =6540/3.
- For the Savings B5 cell, add the amount of monthly income from savings accounts.
- Select the number cells in column B, cells 2 through 5, then choose the ∑SUM
option to auto-fill the sum of numbers in the B6 Total Income cell.
A typical formula looks like this: =sum(B2...B5), which means you want the total of numbers in column B rows 2 through 5.
10.3 Add and calculate expense data.
For each item in Column A's Expenses section, add an actual amount in Column B.
- For the Tuition and Fees B9 cell, type the amount of tuition for a single month.
- For the Books B10 cell, use math to calculate the term's book expenses divided by 3 months.
For example, if you bought 4 books with these prices, add them up and divide by 3 to get a monthly average, like this:
=(120+35+89+66)/3.
- For the Rent B11 cell, add your portion of monthly rent paid for your dorm, apartment, or house.
If you live with family who pays your portion of the rent or mortgage, then ask them for the total mortgage expense
and divided it by the number of people who live in the house. Use math in that cell to calculate it.
For example, if two parents and a sibling live in the family home, then a mortgage of $1720 per month divided by 4 people is:
=1720/4. Your portion of the rent is $430.
- For the Water and Sewer B12 cell, find the amount on your water bill if you live alone. Otherwise, use math to calculate your portion of the monthly amount.
- For the Electric power B13 cell, find the amount on your electric bill if you live alone. Otherwise, use math to calculate your portion of the monthly amount.
- For the Phone service B14 cell, find the amount on your landline and/or cellphone bill if you live alone. Otherwise, use math to calculate your portion of the monthly amount. Or, if you have a monthly rental and data amount, itemized on a family bill, use math to add those amounts.
- For the TV and Internet service B15 cell, find the amount on your landline and/or cellphone bill if you live alone. Otherwise, use math to calculate your portion of the monthly amount.
- For the Groceries, Dining, and Drinks B16 cell, use math to add up the total of receipts for a month's expenses for food, restaurants, and coffee, tea, and alcohol. If you don't save paper receipts, then look at the checking account or credit card register in your online bank account.
- For the Car payment B17 cell, enter the amount on your car payment bill (even if someone else pays it. This expense can get added to the Family income cell later. If you don't own a car, enter 0 zero.
- For the Gas and oil changes B18 cell, use math to enter the amount of receipts for a month's worth of gas. If you change the oil in your car every 3 months, then use math to divide that amount into 3 months. For example, if a full tank costs $32 and you fill it every week, and oil change costs $35 every 3 months, the math is =(32*4)+(35/3). If someone else pays these expenses, then add them anyway. This expense can get added to the Family income cell later. If you don't own a car, enter 0 zero.
- For the Car insurance B19 cell, enter the monthly amount or use math to calculate a monthly average if you pay it every 3, 6, or 12 months. If someone else pays these expenses, then add them anyway. This expense can get added to the Family income cell later. If you don't own a car, enter 0 zero.
- For the Entertainment B20 cell, use math to add up expenses for activities like cinema/movie/theatre, books, concerts, museums, club dues, cover charges, etc. Add only typical expenses for a single month.
- For the Haircuts B21 cell, enter the typical cost of a month's haircut. If you don't cut your hair, enter a 0 zero.
- For the Pets B22 cell, enter the typical cost of a month's pet expenses, such as food, vet bills, and accessories. If you don't have a pet, enter a 0 zero.
- For the Medical B23 cell, enter the typical cost of a month's medical expeneses such as medications, office visits, and equipment rental. If you don't have any medical expenses, enter a 0 zero.
- For the Savings Account B24 cell, enter the typical you add to a savings account. If you don't save, enter a 0 zero.
- For the Other Loans B25 cell, enter the typical amount of loan payment(s). If you have more than one, use math in the cell to add up the monthly payments. If you don't have other loans, enter a 0 zero.
- For the Credit Cards B26 cell, enter the typical amount of card payment(s). If you have more than one, use math in the cell to add up the monthly payments. If you don't have credit cards, enter a 0 zero.
- Select the number cells in column B, cells 9 through 26, then choose the ∑SUM option to auto-fill the sum of numbers in the
Total Expense B27 cell.
- For any expenses that someone else paid, add them to the Family or other cells in the Income section.
For example, if your parents paid your cellphone and car insurance bills, then
click into the Family B3 cell and type =. Then, while that cell is still selected,
click on the B14 cell, then type a +, then click into the B19 cell.
Click Enter.
Data from the two cells gets added to the B3 cell. This adds up the expenses paid by others, which becomes part of your income.
- Save the file with Ctrls or ⌘s.
10.4 Determine discretionary funds.
- Determine how much you have left over by clicking into the Discretionary Funds B27 cell and typing =B6-B26. This will subtract the Total Expenses from the Total Income.
- If your discretionary amount is positive, then you theoretically have some leftover money to save for dreams and emergencies.
- If your discretionary amount is negative, then you spent more than you made.
This could be because some of the monthly amounts are incorrect or missing.
Make corrections to the amounts so that you end up with $0 or a small positive discretionary amount.
Doesn't your worksheet look great!
The results of your worksheet should look something like this.
Notice that all numbers are whole (no decimals) and align to the right. (Number columns are always aligned to the right in spreadsheets.) The row headings are aligned to the right as well to improve readability. Space is provided between Income totals and Expenses to make it easy to differentiate them. All total cells are bold, as are the column headers.
Earn Extra Credit
To earn 5 points of extra credit (and prepare for future financial planning), create a 1-year sheet:
- Add a column for each month of the year, starting in column C.
- Add actual values to the current month's column
(and any other months' columns that you know the amounts for).
Don't however, add amounts to future months if you don't know those amounts yet, as you cannot predict the future. :-)
- Use math in the cells as needed just like you did in step 10.3.
- When a monthly amount is exactly the same from month to month,
drag its cell handle to the right to automatically fill in the amount for each month.
- Use the =SUM() function in each of the 12 month columns to add up the total
Income and Expenses.
- Use the =AVERAGE() function in column B to see an actual monthly average of all 12 months' incomes and expenses.
- Add the year to the sheet name so it reads 2020 Income Expenses.
- Submit either a PDF file of the year-long sheet or paste the Sharing URL into the Assignment Commenting box.
- If you submit a PDF file, choose landscape orientation and fit to width so all columns fit on one sheet.
- If you submit a sharing URL, update the settings so that Anyone at OSU can view, so all TAs and the instructor can view it.
If, after the course is over, you continue to use this spreadsheet every month of every year to see what you're taking in and spending, then it'll be easier to plan for the future.
If you have large amounts left over each month, consider adding them to a savings account to help fulfill dreams and survive during emergencies.