Csci104 budget .edu



BUDGETBuild a workbook in Excel to compute your budget. General guidelines are provided below, but you will need to adjust this to meet your personal situation. This workbook should reflect the material we have learned in this class. Make sure that you isolate and label all assumptions when building this worksheet. This will make it easier for you to adjust it as you work your way through the exercise. You do not need to cite the sources in the workbook, but you will need to retain the bibliography information for the Presentation you will be creating at the end of the semester. Use the Excel “Comment” to record this data (hyperlink or general note). If some of the data is determined by your past experience (bills you already pay), this can be noted in the Presentation. Save the workbook as budget.xlsxUpload & submit it to D2L Assignment dropbox: “Budget”.Income WorksheetBuild a worksheet which will allow you to estimate your monthly income. This will depend on both your job and your geographical location. Gross Income – This is the amount of money your employer will pay you per year. This is the values that should be on your job posting you copied from the internet for your resume and cover letter or you can find the occupation on the website for The Bureau of Labor Statistics: Link Retirement – This is the percent of your yearly income that you want to contribute to your retirement (you determine this value). This will reduce the amount of Federal Tax you will have to pay – but monies contributed are usually not available until retirement. To determine total contribution per year, multiply your Gross Income by the Retirement Percent. Medical Spending – This is the percent of your yearly income that you want to use for medical expenses. Money is set aside to be used for doctor’s visits, prescriptions, medical tests etc. This will reduce the amount of Federal Tax you will have to pay – but monies contributed can only be used for medical expenses during that year. Extra money contributed to this account are lost at the end of the year. Often individuals determine this percentage by examining the past year’s medical expenses and making an educated guess (you determine this value). To determine the total contribution per year, multiply your Gross Income by the Medical Spending Percent. Health Insurance to Pay – Health Insurance is often subsidized by the employer and the employee, you pay a certain amount each month (or pay) toward the coverage. This will be an estimate as health care coverage changes every year. The Health Insurance to Pay will be for the year (you determine this value). Multiply the Health Insurance per month by 12.Calculating the Amount of Federal Tax;The federal government requires that you pay a portion of your annual federal taxes with each pay check. You can estimate the total you will need to have paid for the year using the tax guidelines for that year. There are websites available for you to look up tax information for a given year.Federal tax information for 2018: HYPERLINK "" need to determine your filing status (Single, Married Filing Jointly, Married Filing Separately, or Head of Household). Your filing status will determine which tax table to use and your standard deduction (you will not be itemizing your deductions for this exercise). AS of tax year 2018 there are no more exemptions so you should just put zero as your Personal Exemption total. The Personal Exemption total and the Standard Deduction and your Retirement and Medical Spending Contributions will all be subtracted from your Gross Income to give you your Taxable Income.You want your Taxable Income to be a low as possible since it will determine your Federal Tax Bracket. The higher the Taxable Income, the higher the percent of taxes you need to pay!!Example using 2014 Federal Tax data:Filing Status - SingleStandard Deduction for filing single - $6,200Exemption Amount - $3,950 (multiply this by number of dependents-including you)Dependents - Number of people plus yourself that you support (spouse, children)Total Exemption Amount – Multiply dependents by the exemption amountTotal Per-tax Contributions – Sum of Retirement, Medical Spending and Health Insurance.Taxable Income (For Fed Tax) – Subtract from your Gross Income the Standard Deduction, Total Exemption Amount, and Total Pre-tax Contribution. This will be the value that determines your tax bracket for the tax table.Federal Tax Data – You will need to enter the values for the Tax data for the tax year you are using and your filing status. The values that you enter should be pulled from the tax table given on the Federal Tax Information website mentioned above.Here is an example of a table from the Federal Tax Website for 2014:Single:Taxable IncomeTax Rate$0 to $9,07510%$9,076 to $36,900$907.50 plus 15% of the amount over $9,075$36,901 to $89,350$5,081.25 plus 25% of the amount over $36,900$89,351 to $186,350$18,193.75 plus 28% of the amount over $89,350$186,351 to $405,100$45,353.75 plus 33% of the amount over $186,350$405,101 to $406,750$117,541.25 plus 35% of the amount over $405,100$406,751 or more$118,118.75 plus 39.6% of the amount over $406,750Bold values – Taxable Income lower boundary for each tax bracketPercentages – Federal tax rate for that tax bracketRed values – Base amount of tax to pay in each bracketUsing this table of data you should enter the values on the Income Worksheet. Taxable Income are bold in the above table, Federal Tax Rate are the percentages, Base Amount are the red values.My Tax Rate – This is the tax bracket you are in based on your Taxable Income. Using the vlookup, lookup the federal tax rate using on your Taxable Income.My Base Amount – This is the base amount you must pay the Federal Government based on your tax bracket. There will be more money to pay the Federal government calculated below. This is just the base amount. Using a vlookup, lookup the base amount using your Taxable Income.Amount to Subtract – This is the amount you will subtract from your Taxable Income to determine the rest of the federal taxes you need to pay this year. Using vlookup, lookup the taxable income boundary using your Taxable Income.Amount Over Base – This is the amount of your Taxable Income that you need to pay a percentage of using your tax rate. Subtract the Amount to Subtract from your Taxable Income.Total Federal Tax to Pay – This will be the total amount this year you need to pay the Federal Government. To calculate this you should multiply Amount Over Base by My Tax Rate and add to that My Base Amount.Calculating Other Taxes to Pay:FICA – FICA tax is the combined percentage paid per year for Social Security and Medicare. For the Income worksheet you will do these two parts separately.Social Security Tax to Pay – You will need to search for the current Social Security Tax Percent online. Typically half of the social security percent is paid by the employer and half by you, the employee. Divide the Social Security Tax Percent by 2 and multiply this by your Gross Income. Medicare Tax to Pay - You will need to search for the current Medicare Tax Percent online. Multiply the Medicare Tax Percent by your Gross Income.State Taxes to Pay – You will need to search for the state Tax Percent based on the state where you will be employed. For example you can google “what is the state income tax rate for Pennsylvania”. This will show one tax bracket of 3.07%. A website that has tax bracket information for each state is . The state Tax Percent should be multiplied by the Gross Income to calculate the State Taxes to Pay.Local Taxes to Pay - You will need to search for the local Tax Percent based on the municipality or county where you will be employed. The local Tax Percent should be multiplied by the Gross Income to calculate the Local Taxes to Pay.Yearly Take Home Income – This is the actual amount after taxes, health insurance, retirement etc are paid. Starting with your Gross Income subtract the following values: Retirement Contribution, Medical Spending Contribution, Health Insurance to Pay, Total Fed Tax to Pay, Social Security Tax to Pay, Medicare Tax to Pay, State Tax to Pay, Local Tax to Pay.Monthly Take Home Income – This is the amount you have per month to cover your monthly expenses. Divide the Yearly Take Home Income by 12.Here is an example of the Income Worksheet using 2014 tax data:right174625Loan WorksheetBuild a worksheet which will allow you to calculate your monthly payments for any loans that you will have. Types of loans that you may have:Car LoanMortgageWeddingVacationStudent LoanOther…You must have at least two loan entries (just make them up if you do not have one to use). Organize each loan in an area of the worksheet and record all of the pertinent data for each: Amount to be financed (this may be calculated from the price of the item and the down payment), Annual interest rate, number of years for the loan. Calculate the monthly payment (PMT function) using this data.Here is an example of the Loan Worksheet with three different loans:0294640Monthly Expenses WorksheetBuild a worksheet which will allow you to calculate your monthly expenses and determine how much money you will have left over (if any) each month. Itemize all of your monthly bills and include any of the loan payments from the Loan Worksheet that you actually plan to pay each month. Make sure you reference the cells that exist in other worksheets – DO NOT copy the values. Some monthly expenses to consider:Housing Expenses Do your best to determine where you will live based on the job you selected in the Job Document. Again, online sources and newspaper advertisements will be a great source of information. Determine if you will rent or buy, you may not live in a cardboard box on the sidewalk. Remember, your location will determine the price of your housing, which will, in turn have an influence on your transportation costs. If you are buying a place to live then you should have an entry for the loan on the Loan Worksheet. If you are renting determine what other bills are included in the rent (gas, electric, water..). Those that are not included in the housing should be itemized in the Monthly Expenses.Transportation ExpensesDo your best to estimate transportation costs. If you will live where public transportation exits, and you plan take advantage of this service, find the rate for passes, tickets. If you plan to drive to work, include the price for car payment (should be on Loan Worksheet), parking fees, gas, and insurance. Other ExpensesItemize other expenses that you have each monthly. Try to be accurate with your estimates for each of these expenses. If you have a bill that is paid quarterly, then divide the quarterly bill by 3 to get the monthly estimate. right-63500Here is an example of the Monthly Expenses Worksheet:Loan Amortization Schedule WorksheetInsert a template for the Loan Amortization Schedule. Select one of the loans from your loan sheet and enter the data in the top portion of the Loan Amortization Worksheet. The Loan Summary and the payment details will automatically fill in once you enter the data at the top (orange filled cells on my example). Notice how much interest you will have paid on the loan. Also notice how paying an extra amount each month or on select months it will decrease the length of the loan and the amount of interest paid over the loan lifetime.Here is an example of the Loan Amortization Schedule Worksheet: right254518Workbook RequirementsFunctions and Formulas: You must use formulas or functions when you can. Each Worksheet has a running narrative that instructs you on the functions or formulas needed. Values that are calculated on one worksheet and needed on another should be referenced NOT COPIED. Chart: You should create a chart for your monthly expenses – a bar, column or pie chart correctly labeled. Use best practices in formatting and make it easy to read and communicate the relationship between the expenses. Show it to others and see if it really communicates the relationship. Remember that the total is not included in the chart of the detail data.Color Coding and comments: All supporting data items provided by a source (tax information, sticker price, rent, etc) should be highlighted using a light blue cell fill color. Provide a comment (using the “insert comment” option) indicating your data source. Tables of source data should include just one comment in an appropriate spot in the table. All input data provided by you should be highlighted using a light orange cell fill color. Labels should be formatted to stand out but not detract from the readability of the worksheet. All other values are calculated values and should have no fill color (there should be quite a few of these!). All numeric values (calculated, source & input) should be properly formatted with a numeric format.Print Settings: All worksheets and charts should be set up to print properly (print area set, fit-to-one page, landscape (or portrait if it looks better), headers and footers as described in the Style Guidelines). Make sure all cells are resized to fit the contents of the cell (NO cells should be filled with #). The only item that does not need to fit-to-one page or contain headers & footers is the loan amortization schedule. You do not need to provide the color coding for source data and provided data on the loan amortization schedule.Saving and Submitting the file: Save the workbook naming it “Budget”. Submit this saved workbook to the Assignment dropbox “Budget”.Sources and Citations: You will not do any bibliography or citations in the Budget. Source or hyperlink for data that you looked up should be noted in the comment associated with a cell or range of cells. Make sure you keep track of your full source information for the bibliography in your Presentation (an up-coming assignment). ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download