Intro to Computer Applications – CMAP120
Fall 2023 Final Project – Personal Budget
The final project for CMAP120 (Fall 2023), Intro to Computer Applications, is to use Microsoft Excel to create a workbook for planning a personal budget for yourself. Your workbook will have four tabs (worksheets) as follows:
Required:
1. Your budget (spreadsheet)
2. Car Payment
3. Apartment Rent or Mortgage Payment
4. Chart of Total Expenses
Optional
5. You can include a tab that has a screenshot showing the Job Information from the source that you have used (for example, the Bureau of Labor Statistics Occupational Outlook Handbook)
DO NOT USE A TEMPLATE! Format your own worksheet – be creative!
1. Tab 1: Your Budget (Excel Spreadsheet) Requirements:
a. Your budget will cover a one-year (12-month) period for the calendar year 2023. b. Your budget will be based on:
i. Your income from the job you have selected; and,
ii. The realistic costs of living in the DC-Maryland-Virginia area.
c. You will begin by choosing a job using theBureau of Labor Statistics Occupational Outlook Handbookwebsite. You will need to determine what the median salary is for the position selected. Once you have found the job of your choice you will begin creating your budget spreadsheet.
d. Your spreadsheet must include the title of the job you have selected, the annual
salary, and the source (website and URL) that you used to determine the salary.
e. Create a formula to calculate your monthly gross pay (annual pay divided by 12 months).
f. Create a formula to calculate your monthly federal taxes based on your monthly gross pay using the appropriate tax rate (based on your annual salary). See the tax bracket table below to determine your tax rate:
g. Create a formula to calculate your monthly net pay (monthly gross pay minus taxes)
h. Create a formula to calculate your annual net income (annual income minus annual taxes)
i. Expenses: you must include your mortgage or rent payment, homeowners or renter’s insurance, car payment, car insurance payment, gasoline/fuel for car, and at least seven additional items in your list of expenses, each broken down by month. (See detailed expense information on Page 4 of these instructions).
NOTE: Your expenses are not likely to be identical each month; some months your food, utilities (electric, water, gas), or fuel/gasoline expenses may be higher or lower than others. You may have greater savings in some months, or insurance costs may vary or be paid quarterly rather than monthly. Keep in mind that with inflation on the rise, many of our living expenses are also on the rise. You will need to be realistic with your expense estimates (i.e., $25 per month for food may not be realistic). Research living expenses in order to determine realistic estimates.
j. Calculate the Total for Each Type of Expense and Calculate the total for each Month of Expenses (Note: Expenses should not be more than your income).
k. Calculate the Monthly Balance (amount left over after subtracting expenses from income)
l. Formatting Your Spreadsheet:
i. Your budget spreadsheet must include appropriate formulas and formatting.
ii. Use the SUM function to total expenses (columns totals and row totals). iii. Use Accounting format with two decimal places on all monetary values.
iv. Use a top and double bottom border (or use the Total cell formatting) on the Net Monthly Balance row for each month.
v. Include a title with “Your Name 2023 Budget” merged and centered across the columns used. Bold the title.
vi. Apply the Wrap Text feature for column headings and bold column headings.
vii. For the spreadsheet, select a font that is easy to read and at least 12pt.
viii. Put a footer on your worksheet as follows: your name on the left, the date code in the center, and the file name code on the right side. Make sure you don’t type in manually the date or file name codes.
ix. Make sure all values/labels are visible; you may have to widen columns – if you see any #### that means the column just needs to be slightly widened.
x. In Page Layout change your paper orientation to Landscape.
xi. Rename the worksheet tab to a more appropriate name other than Sheet1 (suggested names include 2023 Budget or Your Name Budget)
xii. Change the color of the worksheet tab to your favorite color.
xiii. Save the workbook as “YourName_Budget”.
Below please find the required expenses that you must include along with ideas for expenses your household may have. You may use some of these but you will need to come up with at least two of your own expenses that are not on this list. Remember, in some months certainexpenses maybe higher than other months and some expenses you don’t pay every month. For example, I pay a water bill for my home—but I pay it quarterly, not monthly. Your gas bill may be higher in winter if you use gas to heat your home; major school expenses only really come at the beginning of each semester. You may not spend money on clothes every single month but you may spend money on clothes every other month or every three months. Don’tforget food/groceries!
|
Required Expenses |
|
. Rent or Mortgage . Homeowners or Renter’s Insurance . Car Payment . Car Insurance . Gasoline/Fuel for Car |
|
Other Potential Expenses |
|
. Cable/Internet/TV . Cell Phone . Utilities (Water, Electric, and/or Gas) . Groceries . Car Maintenance (Note: If you do not include a line item for car maintenance you should consider increasing the amount you put in savings each month to use toward car maintenance and/or repairs) . Medical (doctor visits, prescriptions, etc.) . Pet expenses . Education (tuition payment, books, supplies) . Entertainment (movies, music, dates, holidays, birthdays,etc.) . Vacation/holiday fund . Savings (I suggest saving at least 10% per pay period) |
2. Tab 2: Car Payment
a. You must include a car payment, even if you don’t plan to purchase a vehicle.
b. Look online to find a vehicle that fits into your budget (Can you afford a high-end car? Can you afford a new car, or should you consider purchasing a used car or is leasing a car a viable option?)
c. Take a screen shot of the ad which must show the price of the vehicle along with the vehicle details, and the dealership where you are purchasing the vehicle. Copy and paste that screen shot on to this tab. Rename the worksheet tab to a more appropriate name and change the tab color to a color that you like.
d. Calculate the car payment: Use the PMT function to calculate the monthly loan payment for the car you have selected. You can decide the APR (Annual Percentage Rate) which should be no less than 4.59% for a new vehicle and no less than 6.49% for a used vehicle. The car loan should be for a minimum of 4 years.
3. Tab 3: Apartment Rent or Mortgage Payment
a. You must include a mortgage or rent payment, even if you do not currently pay rent.
b. Look online to find an apartment to rent or home (condo, townhouse, single family home) to purchase that fits into your budget. You must select something in the DC- Maryland-Virginia area.
c. Take a screen shot of the ad which must show the price and location of the apartment or home, along with amenities offered and other pertinent details. Copy and paste the screen shot on this tab. Rename the worksheet tab to a more appropriate name and change the tab color to a color that you like.
d. For a home purchase, use the PMT function to calculate the monthly mortgage loan payment for the home that you have selected. You can decide the APR (Annual Percentage Rate) which should be no less than 6.99%. The mortgage should be for a minimum of 15 years.
4. Tab 4: Chart of Total Expenses
a. Create a 3D pie chart with your yearly expense totals listed as each piece of the pie. (Remember you will have to select non-adjacent ranges of data to create the pie chart as you need to show the name of each expense along with the expense total.) b. Move the pie chart to its own worksheet by using the Move Chart feature.
c. Make sure that the pie chart has an appropriate title, not Chart Title.
d. Apply data labels to each slice, showing the dollar value that each slice represents. e. Move the legend to the right.
f. Rename the worksheet tab to a more appropriate name and change the tab color to a color that you like.
Scoring:
. This assignment is worth 150 points possible
- 5-point deduction per error (incorrect or missing formatting; other errors)
- 10-point deduction per spelling error
- 15-point deduction: missing or incorrect formulas or functions/requirements; chart missing; supporting information (car details, housing details) missing
This final assignment for the fall 2023 semester is due by 11:59pm on Thursday, December 14, 2023. Late submissions will not be accepted.