ACCT 202 Accounting Estimated & Total Cash Receipts Excel Project

ACCT 202 O’Hara
A Lesson in Practicality
This project is designed to teach you how to use a cash budget to plan for
the future using a spreadsheet.
Format –
This will be your basic format.
• Cash Receipts should include your salary. Depending on your
circumstance, it may include any other sources of cash if applicable.
• Cash Payments should include numerous expenses.
• You should have a column for each month AND a column for the year
• The good news is that when you graduated you got cash gifts from
relatives amounting to $8,500.
This is a summarized format per your ebook.
You will need to obtain the following information:
Cash Receipts
•
You will need to obtain data about the starting salary you would
earn. For purposes of this problem, you are to assume that you graduate at
the end of this school year and that you will be starting a job in July of
2024. To ensure that you have a realistic number you will be required to
have supporting data for your starting salary in July. This may be found
online or as a hard copy.
•
You probably think that you take home all of your salary. You do
not! There will be deductions for federal and state income taxes (if
applicable), FICA (Social security and Medicare), and you will probably
spend some of your paycheck on medical coverage. You should factor in
these taxes in the Cash Receipts section when you list income from your
salary. The format should be as follows for each month and in total
Monthly Gross Salary
Less: FICA @7.65%
Federal Taxes (see below)
State Taxes (if applicable)
Monthly insurance co-pay (see below)
Net Take-Home Pay
•
Assume that FICA will be 7.65 % of your gross pay and your
parents can give you an approximation for medical insurance costs that
you will have to pay (most employers pay some but not all of these
insurance costs and you should just have some idea of what you will need
to pay). You do not have the option of declining medical insurance costs
for purposes of this budgeting problem.
•
For Federal Taxes you should assume the following tax rates for
your salary.
To get your Federal Taxes multiply your monthly gross pay by the
appropriate rate for your annual salary range that is detailed above.
Cash Payments
•
You should get some data on food costs and probably talk to your
parents about what meals cost to prepare (3 meals a day and 7 days a
week). Describe how you arrived at your estimate and/or show any
supporting data.
•
You need transportation to get back and forth between your job and
your apartment. Your documentation for the amount of transportation
expense must be a schedule of fares, if you are using public transportation.
If you are taking an automobile back and forth, your automobile cost
should be based on the leasing cost of the vehicle you plan to use. If you
are not using public transportation, you need to factor (and disclose) in any
additional costs associated with your transportation. You should obtain
documentation for much of this and list these individual items separately.
•
You will need to live someplace, other than at your parents, when
you start your job. You can choose to rent an apartment as an individual or
share an apartment with others. You will need to have documentation
from the area you plan to live in to support your apartment rental expense
number. You should include twice the rent amount during the first month
since you need to leave a security deposit (you will be reimbursed this
amount at the end of the lease assuming no damage to the apartment).
•
Include all other expenses in detail (a separate row for each). The
more detailed you are, the better! It’s realistic. Some monthly expenses
may stay the same each month and some will be expected to fluctuate.
Don’t forget about student loans if applicable. If you do not wish to share
your personal information with me, you can simply arrive at realistic
information for someone in your shoes.
Documentation/Support
•
Submit all of the documentation (or explanations where
documentation is not appropriate) that support what you are claiming to be
your budget. I encourage you to obtain support and to explain your line
items, when possible, as this will positively impact your grade.
Excel Spreadsheet Skills
•
You should have an Excel spreadsheet that details everything
described in the preceding. A portion of your grade will be based on
spreadsheet skills. Be sure to use formulas when appropriate. Do NOT
manually calculate numbers on a calculator. Be sure to also use proper
formatting (dollar signs, column widths that disclose the amounts, etc.).
•
DO NOT USE AN EXISTING BUDGET TEMPLATE, AS I
WILL NOT ACCEPT THIS. You MUST create your own unique
spreadsheet. BE SURE YOUR SPREADSHEET IS YOUR OWN.
This is not a group project!! If your project is substantially the same as
another classmate’s project, you will receive only a portion of your grade
(e.g., 50% if there are two people involved) or no grade at all.
Submission and Timeliness
Plan to submit your project electronically on Blackboard. You may include
explanations any way you wish. Please note ONLY Excel files will be
accepted. The late penalty is 10% per day (weekends included).
I hope this project is both interesting and will be meaningful to you as you
complete your personal budget.
monthly budget 2025
Annual Salary
$50,880.00
salary
yearly bonus
extra salary
total income
January
$4,240.00
$0.00
$6,700.00
$10,940.00
February
$4,240.00
$0.00
$0.00
$4,240.00
March
$4,240.00
$0.00
$0.00
$4,240.00
Expenses
Rent
car payment
car insurance
utilities
groceries
restaurants
internet
entertainment
vacation
savings account
gas
saving account return
total expenses
January
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
February
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
March
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
$7,070.00
0
$7,070.00
$370.00
$7,070.00
$7,440.00
$370.00
$7,440.00
$7,810.00
surplus
Beg Cash
Ending Cash
SOURCE
April
May
$4,240.00
$0.00
$0.00
$4,240.00
April
June
$4,240.00
$0.00
$0.00
$4,240.00
May
July
$4,240.00
$0.00
$0.00
$4,240.00
June
$4,240.00
$0.00
$0.00
$4,240.00
August
$4,240.00
$0.00
$0.00
$4,240.00
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
July
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
August
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
$370.00
$7,810.00
$8,180.00
$370.00
$8,180.00
$8,550.00
$370.00
$8,550.00
$8,920.00
$370.00
$8,920.00
$9,290.00
$370.00
$9,290.00
$9,660.00
September
$4,240.00
$0.00
$0.00
$4,240.00
October
$4,240.00
$0.00
$0.00
$4,240.00
November
$4,240.00
$0.00
$0.00
$4,240.00
December
$4,240.00
$1,167.00
$0.00
$5,407.00
Total
$50,880.00
$1,167.00
$6,700.00
$58,747.00
September
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
October
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
November
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$0.00
$1,500.00
$200.00
$20.00
$3,870.00
December
$1,200.00
$350.00
$120.00
$70.00
$150.00
$90.00
$50.00
$120.00
$2,500.00
$1,500.00
$100.00
$20.00
$6,270.00
$14,400.00
$4,200.00
$1,440.00
$840.00
$1,800.00
$1,080.00
$600.00
$1,440.00
$2,500.00
$18,000.00
$2,300.00
$240.00
$48,840.00
$370.00
$9,660.00
$10,030.00
$370.00
$10,030.00
$10,400.00
$370.00
$10,400.00
$10,770.00
-$863.00
$10,770.00
$9,907.00
$9,907.00
$98,120.00