Assignment 12-2

Zero-based Budgeting Guide for Excel Spreadsheet

Introduction: This exercise is worth 15 points. It provides you with an opportunity to practice
managing a zero-based budget using practical scenarios. Assume, with limited work experience
and a college education, that you find a job with a starting salary of $40,000 per year. You are
fully financially responsible and accountable for all outlays.

Instructions: In the corresponding Excel spreadsheet for Assignment 12.2 "Zero-based

Budgeting Worksheet in Excel," insert the proper amounts in the designated lavender cells
within columns D and E based on your choices. You will earn full credit by allocating all your
income. This will occur when $0 appears in TOTAL ALLOCATED, cell C3 and ZERO
BALANCE, cell C83.

Step 1. Consider your net pay from your job. As you saw in a previous exercise, you can expect
to take home $36,000 in annual net pay if you gross $40,000 yearly. This translates into $3,000
once a month or $1,500 if you are paid twice a month.

Go to your budgeting spreadsheet. See that $1,500 has been entered in the pink cell D82
“Paycheck for 1st of Month” and the pink cell E82 cell “Paycheck for 15th of Month.”
Scroll up the spreadsheet to see these amounts automatically appear in cells D3 and E3 for
“Paycheck for 1st of Month” and “Paycheck for 15th of Month,” respectively.

Step 2. Plan to allocate all of the $1,500 taken home on the 1st and 15th of each month. Do so
with purpose and intent to achieve financial goals. Items can be split between paycheck 1 and
paycheck 2. Just enter your total committed amounts into the appropriate cells. As you enter
amounts, you will notice that TOTAL ALLOCATED, cell C3, will decrease by the amount
entered in paycheck 1 (D) and paycheck 2 (E) columns.

To achieve a zero-based budget, your TOTAL ALLOCATED, cell C3, should ultimately read
“$0” or zero. This means that you are purposely planning where to place all of your net income
-- every cent. Zero-based budgeting helps you strategically assign every dollar coming into your
household with the purpose of helping you make financial decisions that help you reach your
goals and achieve financial security!

Step 3. Now look below. Consider the following expense information and different scenarios
with regards to how you assign your paychecks. Remember, your take-home pay can be
allocated for different purposes, including but not limited to spending, saving, investing, paying
off debt, and giving. Consider tradeoffs and opportunity costs in a zero-balanced budget world.
If you spend today, you have less to spend tomorrow. If you spend on X you may not be able to
spend on Y. If you save today, you have funds for the future. If you invest in a well-diversified
portfolio today, you can generate investment income for the future. If you borrow today, you
commit yourself to repaying in the future. If you choose not to pay a balance in full, you commit
to paying interest. The list goes. To sum it up, it is important to always keep in mind how your
financial decisions affect you in the short, medium, and long-term.
1. Charitable Gifts: Do you plan to give to others? If so, enter the amount in the
designated cells D5 and E5. A target for CHARITABLE GIFTS is (10-15%) of net take
home pay.

This is the property of Common Sense Economics (2010, 2016).

 Assignment 12-2
Zero-based Budgeting Guide for Excel Spreadsheet
2. Emergency Funds, Real World Savings Account or Rainy Day Funds: This is the
dollar amount you give yourself each month when something unexpected happens. Think
about your own unexpected expenses for last year. Estimate all unexpected expenses and
divide by 24. Enter the amount you set aside for “Emergency Funds” in the cells D8 and
E8.

3. Retirement Funds: Start today. Save $2 a day, each day of the month. Assume that
there are thirty days in this month. Enter $30 in the designated cells D9 and E9. Feel
free to put more. Total savings and investing can account for 5-10% of your net-pay.
4. Other investments: Enter any amount you want to put away for a down payment on a
house, college for kids, or down payment on a rental property to generate income. Enter
the amounts in the designated cells D10 and E10.
5. Housing: Everyone needs shelter to live. Housing can claim 25-35% of your net pay.
With this in mind, please choose one housing option. Note that each choice has financial
tradeoffs. After entering your preferred option, adjust other expenses accordingly to
maintain a zero-based budget if necessary. Enter the amounts in the designated cells
D12-18 and E12-18.

Option A: 1 Bedroom apartment, 5 minutes from work, nicer amenities: Rent is

$900/month. The estimated monthly utilities are $150 per month and renters insurance is
$10/month.

Option B: 1 Bedroom apartment, further from work, no amenities: Rent is $750/month.

The estimated monthly utilities are $150 per month plus $10/month in renters' insurance.

Option C: 4 Bedroom apartment with 3 roommates, each person pays $300 for rent and
$75 in utilities. It is 15 miles from work but on a public transportation line. Renters’
insurance is $15 a month.

Option D: Home Purchase. To buy house at $150,000, you must have minimum of 20%
down. If you have $30,000 in relatively liquid investments and savings, you can use this
option. Your monthly payment is $800 or twice weekly is $400 for principal, interest,
taxes and insurance.

6. Utilities: Utilities, including communications and media, may claim between 5 and 10%
of your net pay. Communications and media consume a large part. Before making your
communications and media choices, research and input average utility costs in cells D20-
27 and E20-27.

7. Utilities: Communication and Media Options

Option A. Go without a phone ($0) or choose one or the other options: (i) Cell/smart
phone range $30 to $120 a month or (ii) land line range $35 to $50 a month or (iii) some

This is the property of Common Sense Economics (2010, 2016).

 Assignment 12-2
Zero-based Budgeting Guide for Excel Spreadsheet
combination of the two. Enter the amount in the designated cells D25-27 or E25-27.

Option B. Go without cable TV ($0/month) or choose one of the following options to

get cable: (i) $40/month (basic); (ii) $60/month (digital cable); (iii) $95/month (HD
cable); and (iv) $120/month (premium cable). Enter the amounts in the designated cells
D25-27 or E25-27.

Option C Go without internet access ($0/month) or choose one of the following: (i)
Internet at $90/month for DSL, (ii) $100 for cable, (iii) $110 for fiber or (ii) $200/month
bundle everything (DSL, TV and land-line phone) into one package. Enter the amounts in
the designated cells D25-27 or E25-27.

8. Food: Everyone needs to eat to live. Enter any amount you want to spend on eating at
home (groceries) and eating out at restaurants. Enter the amounts in the designated cells
D29/D30 (Grocery/Restaurants Pay Period 1) and E29/E30 (Grocery/Restaurants Pay
Period 2).

9. Transportation Options: Please choose one option. Many people want the freedom and
flexibility that owning a car can provide. Others prefer to walk, bike, car-pool, or take
public transportation. The choice and tradeoffs are yours. After entering your preferred
option, adjust other expenses accordingly to maintain a zero-based budget. If you choose
Option A-D, then enter the car payment amounts in the designated cells D73 and E73
when every expense is considered. If you choose Option E, then enter the public
transportation amounts in designated cell D36 and E36. Be mindful of the various
outlays associated with car ownership. See cells D32-36 and E32-36 for other
transportation details.
Option A: Buy a used and reliable car $8000 for 3 years (36 monthly payments)
Monthly payment = $245 (includes insurance and license and registration fees.)
Option B: Buy a used beat-up car $2400 for 2 years (24 monthly payments)
Monthly payment = $107 (includes insurance and license and registration fees.)
Option C: Buy a new midsize car with a warranty for $30,000 for 5 years (60 monthly
payments)
Monthly payment = $595 (includes insurance and license and registration fees.)
Option D: Buy a new compact car with a warranty for $18,000 for 4 years (48 monthly
payments)
Monthly payment = $431 (includes insurance and license and registration fees.)
Option E: Use public transportation, car-pool, or walk
Monthly payment = $? (Estimate based on your location.)
10. Gas: Average what you spend every two weeks if you own or plan to own a car. Enter
the amounts in the designated cells D32 and E32.

11. Oil Change: Set $35 aside, if you own a car. It is time to change the oil. Enter $35 in
the designated cell D33 or E33.
3

This is the property of Common Sense Economics (2010, 2016).

 Assignment 12-2
Zero-based Budgeting Guide for Excel Spreadsheet
12. Student Loans: Choose the option below that is as close to your actual, real-life
situation. (Monthly amounts are based on weighted average between subsidized and
unsubsidized loans, approx. 6.2%) Enter the amounts in the designated cells D77 and
E77.

Option A: No Student Loans to payoff = $0/ month

Option B: Student Loans $10,000 outstanding = $112/month
Option C: Student Loans $20,000 outstanding = $ 224/month
Option D: Student Loans $30,000 outstanding = $ 336/month
13. Clothing: Professional outfit or uniform. Assume you must spend $100 to purchase
appropriate professional attire. Enter $100 in designated cell D39 or E39.

14. Disability Insurance: $35/month. To buy or not to buy? That is the question. This
insurance pays a portion of salary if you are disabled for the long-term. (Assume you are
a healthy 23 year old). If you choose to buy, enter $35 in the designated cell D42 or E42.

15. Co-pay for doctor visit: $25: Surprise! You have a serious infection. Enter $25 in
designated cell D44.

16. Prescription medication: $10: You need medication to fight the infection. Enter $10 in
designated cell D48.

17. Identity Theft Protection: $10/month covers all types of identity theft. The company’s
caseworker handles all related issues. If you decide to buy, enter $10 in designated cell
D51 or E51.

18. Hair Care/Toiletry Items: Estimate what you think you will spend twice a month on
personal hygiene items, toiletries, and hair care. Enter the amounts in designated cells
D52 and E52.

19. Work Supplies: You must buy a few things for your office! So enter $50 in designated
cell D59 or E59.

20. Professional Magazine or Subscription: Look up the cost of a professional journal,

newsletter, or organization. You must keep up with trends in your line of work. Enter the
amount in designated cell D60 or E60.

21. Pet Care Do you have or plan to get a pet? Budget in the monthly cost of feeding,
cleaning, and veterinary care. Enter the appropriate amount into designated cell D62 or
E62.

22. Postage: $7/month is needed to pay bills, send letters, and so forth: Enter $7 in
designated cell D63 or E63.
4

This is the property of Common Sense Economics (2010, 2016).

 Assignment 12-2
Zero-based Budgeting Guide for Excel Spreadsheet
23. Two Gifts: Enter the total amounts of $50 into designated cell D65 or E65.

24. Gym Membership Options: You have three options in this exercise. a) Pay $40/month
for a membership at the YMCA, b) Spend $75/month at a professional gym, c) Pay
$0/month and workout at home or go out and enjoy the great outdoors. Enter the
appropriate amount into designated cell D67 or E67.

25. Entertainment, Recreation, and Vacation: Estimate what you plan to spend on going
out, entertainment, and planning for your next vacation. Enter the appropriate amounts
into designated cell D70-71 or E70-71.

26. Credit Cards: Did you know that the average college senior graduates with over $3000
in credit card debt? This translates into about $180/month. Assume the balance is due at
the end of each month. Enter $180 into designated cell E75.

27. Child Outlays: Do you have children? How many? Do have a one or two parent family
to divide costs? Enter estimated amounts in the designated cells. Estimate and adjust the
appropriate amounts in the following line items: food, clothing, medical care,
transportation, child care, babysitting, and so forth.

28. Miscellaneous: Other areas that are not listed above but are on the excel spreadsheet are
optional. If you currently spend or are planning to spend in the near future, enter the
estimated amount in the appropriate cells.

This is the property of Common Sense Economics (2010, 2016).