Csci104 budget - Edinboro University of Pennsylvania



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’ll need 4 worksheets in one workbook due the day you take the excel exam.Income (Taxes)This is where you figure your taxes based on the job income you applied forDetermine what your yearly taxes would be then divide by 12 to figure your monthly tax expenses.This is the one where we used that complex vlookup and the tax table.CarAssume you are buying a car (whether you are or not)Use the PMT.Looks like the example below. LoanThis is for your student loan, if you don’t know how much you owe, or think you own nothing, just use $32,000.This is the only one where you use a templateLook for a loan amortization template like the image below. Monthly ExpensesProbably the easiestList of things like rent, food, entertainmentPull from the other 4 sheets using the “ ! “ for example “CAR!A22” to grab your monthly payment for your carSave the document as budget.xlsxUpload & submit it to D2L dropbox: “Budget”.IncomeBuild a worksheet which will allow you to estimate your income. This will depend on both your job and your geographical location. The BLS site page provides information on salaries by location. Find your local (this may be too difficult to find), state and federal income tax rates and compute the amount of taxes which will be deducted from your income. You should consider filing status as well. There are many sites that you can find tax information. For example if you google “what is the state tax rate for Pennsylvania” you’ll see there is only one tax bracket and that is 3.07%. Many of you predicted jobs far away, so look up that state’s income tax.This is the 2014 Federal Income tax table.Figure 1-: 2014 Federal Tax Table - Personal IncomeFind or estimate the amount which will be deducted from your pay due to other mandatory deductions. This might include: Health Insurance costs Contributions to retirement accounts Medicare/Medicaid Social SecurityUnion duesOnce you have figured out taxes and deductions you will need to pay throughout the year, total that up and subtract from your projected annual income. Divide that answer by 12 and you should produce a final monthly amount of income. We’ll use that later on.Here is an example (note that none of the values apply to the 2014 tax table):(This page contains a table lookup of the federal tax rate based on salary.)Figure SEQ Figure \* ARABIC 2: More ExampleHousing ExpensesDo your best to determine where you will live. After you have "found" your job, do your best to locate housing. Again, online sources and newspaper advertisements will be a great source of information. Let’s assume you are going to rent, you may not live in a cardboard box on the sidewalk. Remember, your location will determine the price of your housing. So this will just be a simple rent that you’ll add to your expenses.TransportationLet’s assume you plan to drive to work and you are buying a car (even if you own one already). Include the price for car payments, gas & car insurance. You should calculate the car payment on a separate worksheet similar to the one below:228602413000(This worksheet contains the payment calculation for the car payment.)Student LoansEstimate how much you will have borrowed by the end of your education, assume the current interest rate. Find the period of this loan. This will allow you to compute the amount you owe each month for your student loan. The easiest way to implement this is to use one of the loan amortization worksheets or templates. You can use the interest rate I have there. Similar to the following:Other ExpensesDo your best to come up with an honest estimation of other expenses including food, clothing, computer and entertainment. Again remember to record and document your sources. You’ll need separate expense worksheets for student loan and car loan. 34606962975500All expenses should be gathered on one expense worksheet, along with the income to provide a good monthly picture of your finances. For example:Workbook RequirementsYou must use formulas or functions when you can. You should incorporate at least one table look up, and one function to calculate a payment. Values that are calculated on one worksheet and needed on another should be referenced NOT COPIED. You should create a chart for your expenses – a column or pie chart correctly labeled. Use best practices in formatting and make it easy to read and communicate the expenses. Show it to others and see if it really communicates the relationship.All supporting data items (that you had to look up on the internet) 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 (cost of car, interest rate, numbers that you type in, etc…) 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.All worksheets and charts should be set up to print properly (print area set, fit-to-one page, landscape or portrait, headers and footers as described in the Style Guidelines).Make sure you keep track of your full source information for citations & bibliography (where you got your tax info, rent amount, interest rate info). ................
................

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

Google Online Preview   Download