troduction: In this tab assignment you will create a household budget for yourself using Microsoft Excel A budget is an estimate of expected revenues and expenses for a period of time Follow these instructions carefully. They will guide you step by step. However if you miss a step or enter/format something incorrectly, it may throw off everything that follows. Note: To complete this lab, you will need the desktop version of Excel as the What features are not currently Induted in the Cloud version of Excel Creating a Budget ... Gotting Started: Deliverable 1 --Master Budget Worksheet. The starter file worksheet provides the basic setup for the budget you will be building. It incorporates Ron Blues Money Management Principles which break personal finances into five key categories. The focus of which is the overall core is that God Owns It All." As servants of Christ, it is critical for us to understand that God truly owns it all as seen in (Psalm 24:1) which says, "God owns it all and I am manager of His resources. "As stewards, we open our hands to God and recognize that all money is God's to give or take away. Rooted in that central understanding, personal finances should be broken into the five categories of Live, Give, Grow, Owe Taxes, and Owe Debt without any leftover funds. Throughout fe, the percentages we contribute to each category may change but they all stay present except for the category of Owe Debt which we hope to eliminate. Step 1 Copy the starter file into a new worksheet Step 2 Rename the worksheet as Master Budget Status Check: At this point, you should have one worksheet 1-Master Budget Step 3 Populate the data for Revenue 1. Rename the Source to a name the represents your source (le Full-time Job, Disability, Unemployment...) 2. Enter the Monthly Pay figures (see note below) These estimates are to be in monthly figures. Use Take-home pay for your revenue (after taxes). This eliminates the need to calculate divece tax structures You may notice that the started file has a formula in cell 08 that sums your total revenue. There are two other formulas included-039 sums your total expenses and 43 shows your Net Income or loss. These are there to demonstrate how Excel will do the work for you if you know how to tell it in the remainder of these deliverables, you will be creating the needed formulas Note: You are not required to list your actual pay. Also, if you do not have multiple sources of income, enter fictitious sources and amounts for this exercise. I do caution that you do not want to put in unrealistic figures as some of the later exercises may not make sense if you say you make 1 million a month Step 4 Populate the data for expenses. For Expenses, Just complete the expenses for Live, and Debt Owe, The Grow will be covered in step 5 and the Glve will be addressed in Step 6 Step 5 Grow-Allow me a little discussion here. This part of your budget is for retirement planning. On this worksheet we have four areas to focus on 1. Retirement-setting aside funds for your retirement security. Employers typically offer some retirement plan where they match your contributions. Say for example, the match 5% of your salary. Always participate in this 100%. It literally means if you put 5% of your pay into the fund, your employer matches it thereby doubling your savings immediately. 2. Emergency Fund savings-this is money you have on hand to cover emergencies. It is recommended that you have enough in this fund to live of for 3 months without a paycheck. 6 months is better and something is always better than nothing. It can save you money in other areas. For example, if you set an emergency fund of $3000 (total, not monthly) you can then change your auto insurance deductibles to $1000 which will reduce your monthly premiums. 3. Life Insurance-In the event you pass away, you need to still provide for your family. Get a lot and get it earty (Whole life is CHEAP when you are in your 20s and VERY EXPENSIVE when you reach your 40s. 4. Savings-Much more liquid. If managed correctly, you can use savings (and investments) to purchase necessities of life without going to the bank for a loan. People pay cash for cars and even homes through well managed investments/savings. Let's Make Excel work a little here. In the Grow section put in formulas to calculate you Retirement and Savings The formula will multiply the percentages in column C by the Total Revenue (D8 in the started file). Feel free the change the percentages if you wish. For the Emergency Fund and Life Insurance lines, add in a monthly amount you will budget for these. Step 6 Let's Make Excel work a little more here. In the Give section put in formulas to calculate you Tithing and Other Charity The formula will multiply the percentages in column C by the Total Revenue (D in the started file). Feel free the change the percentages if you wish. in the Give section put in formulas to calculate you Tithing and Other Charity The formula will multiply the percentages in column C by the Total Revenue ( Din the started file). Feel free the change the percentages If you wish. Status Check: At this point, you should have 1 worksheet 1. Master Budget Step 1 Putting Excel to Work Deliverable 2--Simple Percentage Increase It's been a great day at work. Say you boss just come in and told you that you got a promotion to a manager position and with that a raise to your toke-home pay of 5%. He also noted that you would be required to have a manager parking spot for $50 a month and the key to the executive washroom cost $50 a month. Additionally you would need business suits which require dry cleaning total average monthly cost when spread 12 months = $200 a month (you like good sults). NOTE: There will also be an increase in your Give section as we want to continue to give back to the lord. With that being said, is it such a good day. Things are not always what they seem. I remember once in my career getting a significant pay raise the bumped me up a tax bracket and I actually ended up with less take-home pay. So Let's use excel to tell you if it is a good day or a bad day. Copy the Master Budget file into a new worksheet. Step 2 Rename the worksheet as Simple % Increase Status Check: At this point, you should have 2 worksheets 1. Master Budget 2. SimpleXIncrease Step 3 Find the Cell with the calculated Net Income/Loss Figure (cell D39 // you did not add any rows). In the cell directly under that cell manually type in the calculated amount (cell D40 if you did not add any rows). In the cell to the left type in "Baseline Net Before Ralse" Align the text to the right and set the Font color to red and bold the text. Step 4 In the row directly under the one you just added add a row Titled "Percentage Salary Increase." Format it exactly as you did the text above it. Note: Excel has a way to copy formats from one cell to another-in-look for the paintbrush Step 5 in the column associated with that row enter 5% (or.05) (in the starter file, this would have been cell 045. you added any rows it will be different) Step 6 Directly below the cell you typed in "Percentage Salary Increase Type in "Good Raise or Bad Raise" making it Bold and in red. Step 7 to the column associated with that row enter a formula that subtracts The Baseine Net Before Raise from the calculated Net Income/Loss. (it is very critical to get this formula right or your results will be reversed) At this point this will equal (rero or null value) After steps 8 and 9 you will see if the raise creates a gain or a loss Step 8 in cell De enter the formula that will multiple cell Ds times the percentage increase you put in the cell where you entered SX Look good so far but what about the employment expenses? 30 Step 9 Now add the 5300 into any amount you may have already put in the section for Employment Expenses (cell 019) Step 10 Look at the net income/loss after the raise. Was 5% raise a good thing or not? Step 11 Save your worksheet Deliverable 3-What If Goal Seek A sound financial plan in personal finance budgeting is to have a plan for every dollar of income. In other words, you want the Net Income/Loss figure to be zero. Now is the time to do some planning Step 1 Copy the Master Budget file into a new worksheet Step 2 Rename the worksheet as "What-if Goal Seek" Status Check: At this point, you should have 3 worksheets-- 1. Master Budget 2. Simple % Increase 3. What if Goal Seek Step 3 Let's assume that you are thinking about scaling back a bit or you are thinking to open your own business and need to know what is the minimum amount you need to bring in each month to allow you to stay on your current retirement plan. Let's make Excel do the work. Using "What-if" analysis. This is a complex manual process as some of your calculations (Give for example) are based on percentage of income. Just guessing on the lower salary will get you there eventually but with Excel's What-lf function, you get there in the blink of an eye. Note: WhatIf currently only work in the Excel Desktop app. If you have not already downloaded the desktop apps you will need to do so. Step 4 For our problem we want to know how much of a salary decrease for increase) will we need to cover all our expenses and still have "wiggle Room" (in this case $500). From the Data menu select What-If analysis. The select Goal Seek. (Screenshot for how this looks in both MAC and PC are to the left.) 1. Set the value of the cell containing the dollar value of Net Income/Loss to 500 2. And you are going to change the cell of your primary income. 3. Once you have done that, dick on OK and watch Excel do the work. 4. The resulting value in the primary revenue celi is what you would need to make to cover your expenses and have $500 extra each month. Note: this is a great tool to uses as well when looking at when you can retire on your Social Security Income. Step 5 Save your worksheet Deliverable 4-What If Scenario Summary What is the impact of salary adjustments to your bottom-line (Net Income/Loss) For this deliverable we will assume that your management has decided there is a need to cut your hours on a month to month bass. Assume they will need to cut your monthy take home pay by either 5%, 10%, or 20%. To be a solid financial planner, you need to know how this will effect you monthly Net Income/Loss Note: You do not need to make a sheet for this as Excel will build the sheet for you. Start with the Master Budget sheet 57 Step 1 Open the Master Budget worksheet. From the Data menu select What If analysis. The select Scenario Manager Step 2 You will need to build three scenarios that 1. Decreases your current salary by 5%, 2. Decreases it by 10% and 3. Decreases it by 20% The example shown in the PC Screenshot shows the formulas needed to 6000 salary by 20%. (multiplying by.8 reduces the amount by 20%) Step 3 Each time you add a Scenario it will bring you back the Scenario Manager screen. You can add as many as you like, but for this deliverable, you only need these three. For Each Scenario you will need to program in the proper code by following these steps. 1. Select Add 2. Put in the Scenario Name 3. Enter the Cell number to be changed (it will be the same for all three scenarios). 4. Click on OK 5. This will bring you to the Scenario Values form. Type in the correct formula using the number you provided in your master budget as your main income and multiply by the percentage .e.5% 95, 10%9, and 20% -.8) Step 4 Once the three are completed click on "Summary" button and it will bring up the screen to identify the results cell This is the cell you want to see the scenario results in. In this case, it is the cell showing the Net Income/loss (cell 043 if you have not added any rows to the starter file. Note: Adding this cell will show you how each reduction Impacts your bottom line when you do the summary. Step 5 Select the button next to Scenario Summary 1 Select Ok and Excel will immediately create and display the new Scenario Summary worksheet which should look something like the one shown at the bottom of this page. Step 6 Save the Workbook Status Check: At this point, you should have worksheets 1. Master Budget 2. Simple Increase 3. What if Gool Seek 4. Scenario Summary Final Step Submit the xkx file to your assignment submission link First Name Last Name Budget Date Raise Revenue (takehome) Source 1 Source 1 Source 3 Total Revenue $ SUM(D4:07) Expenses Live Rent/Mortgage Electricity Gas (home) Cable/Internet Water Trash Insurance Gas (Auto) Employment Expenses Give Tithing Charity Goal 10% 5% Grow Goal Retirement Emergency Fund Life Insurance Savings Owe Taxes Taxes n/a Payoff Owe Debt Student Loans Car Payment Credit Card Payment Total Expenses =SUM(D11:035) S Expenses Live Rent/Mortgage Electricity Gas (home) Cable/Internet Water Trash Insurance Gas (Auto) Employment Expenses Give Tithing Charity Goal 10% 5% Grow Goal Retirement Emergency Fund Life Insurance Savings Owe Taxes Taxes n/a Payoff Owe Debt Student Loans Car Payment Credit Card Payment - SUMID 11:035) Total Expenses 1 2 4-08-037 3 Net Income/Loss troduction: In this tab assignment you will create a household budget for yourself using Microsoft Excel A budget is an estimate of expected revenues and expenses for a period of time Follow these instructions carefully. They will guide you step by step. However if you miss a step or enter/format something incorrectly, it may throw off everything that follows. Note: To complete this lab, you will need the desktop version of Excel as the What features are not currently Induted in the Cloud version of Excel Creating a Budget ... Gotting Started: Deliverable 1 --Master Budget Worksheet. The starter file worksheet provides the basic setup for the budget you will be building. It incorporates Ron Blues Money Management Principles which break personal finances into five key categories. The focus of which is the overall core is that God Owns It All." As servants of Christ, it is critical for us to understand that God truly owns it all as seen in (Psalm 24:1) which says, "God owns it all and I am manager of His resources. "As stewards, we open our hands to God and recognize that all money is God's to give or take away. Rooted in that central understanding, personal finances should be broken into the five categories of Live, Give, Grow, Owe Taxes, and Owe Debt without any leftover funds. Throughout fe, the percentages we contribute to each category may change but they all stay present except for the category of Owe Debt which we hope to eliminate. Step 1 Copy the starter file into a new worksheet Step 2 Rename the worksheet as Master Budget Status Check: At this point, you should have one worksheet 1-Master Budget Step 3 Populate the data for Revenue 1. Rename the Source to a name the represents your source (le Full-time Job, Disability, Unemployment...) 2. Enter the Monthly Pay figures (see note below) These estimates are to be in monthly figures. Use Take-home pay for your revenue (after taxes). This eliminates the need to calculate divece tax structures You may notice that the started file has a formula in cell 08 that sums your total revenue. There are two other formulas included-039 sums your total expenses and 43 shows your Net Income or loss. These are there to demonstrate how Excel will do the work for you if you know how to tell it in the remainder of these deliverables, you will be creating the needed formulas Note: You are not required to list your actual pay. Also, if you do not have multiple sources of income, enter fictitious sources and amounts for this exercise. I do caution that you do not want to put in unrealistic figures as some of the later exercises may not make sense if you say you make 1 million a month Step 4 Populate the data for expenses. For Expenses, Just complete the expenses for Live, and Debt Owe, The Grow will be covered in step 5 and the Glve will be addressed in Step 6 Step 5 Grow-Allow me a little discussion here. This part of your budget is for retirement planning. On this worksheet we have four areas to focus on 1. Retirement-setting aside funds for your retirement security. Employers typically offer some retirement plan where they match your contributions. Say for example, the match 5% of your salary. Always participate in this 100%. It literally means if you put 5% of your pay into the fund, your employer matches it thereby doubling your savings immediately. 2. Emergency Fund savings-this is money you have on hand to cover emergencies. It is recommended that you have enough in this fund to live of for 3 months without a paycheck. 6 months is better and something is always better than nothing. It can save you money in other areas. For example, if you set an emergency fund of $3000 (total, not monthly) you can then change your auto insurance deductibles to $1000 which will reduce your monthly premiums. 3. Life Insurance-In the event you pass away, you need to still provide for your family. Get a lot and get it earty (Whole life is CHEAP when you are in your 20s and VERY EXPENSIVE when you reach your 40s. 4. Savings-Much more liquid. If managed correctly, you can use savings (and investments) to purchase necessities of life without going to the bank for a loan. People pay cash for cars and even homes through well managed investments/savings. Let's Make Excel work a little here. In the Grow section put in formulas to calculate you Retirement and Savings The formula will multiply the percentages in column C by the Total Revenue (D8 in the started file). Feel free the change the percentages if you wish. For the Emergency Fund and Life Insurance lines, add in a monthly amount you will budget for these. Step 6 Let's Make Excel work a little more here. In the Give section put in formulas to calculate you Tithing and Other Charity The formula will multiply the percentages in column C by the Total Revenue (D in the started file). Feel free the change the percentages if you wish. in the Give section put in formulas to calculate you Tithing and Other Charity The formula will multiply the percentages in column C by the Total Revenue ( Din the started file). Feel free the change the percentages If you wish. Status Check: At this point, you should have 1 worksheet 1. Master Budget Step 1 Putting Excel to Work Deliverable 2--Simple Percentage Increase It's been a great day at work. Say you boss just come in and told you that you got a promotion to a manager position and with that a raise to your toke-home pay of 5%. He also noted that you would be required to have a manager parking spot for $50 a month and the key to the executive washroom cost $50 a month. Additionally you would need business suits which require dry cleaning total average monthly cost when spread 12 months = $200 a month (you like good sults). NOTE: There will also be an increase in your Give section as we want to continue to give back to the lord. With that being said, is it such a good day. Things are not always what they seem. I remember once in my career getting a significant pay raise the bumped me up a tax bracket and I actually ended up with less take-home pay. So Let's use excel to tell you if it is a good day or a bad day. Copy the Master Budget file into a new worksheet. Step 2 Rename the worksheet as Simple % Increase Status Check: At this point, you should have 2 worksheets 1. Master Budget 2. SimpleXIncrease Step 3 Find the Cell with the calculated Net Income/Loss Figure (cell D39 // you did not add any rows). In the cell directly under that cell manually type in the calculated amount (cell D40 if you did not add any rows). In the cell to the left type in "Baseline Net Before Ralse" Align the text to the right and set the Font color to red and bold the text. Step 4 In the row directly under the one you just added add a row Titled "Percentage Salary Increase." Format it exactly as you did the text above it. Note: Excel has a way to copy formats from one cell to another-in-look for the paintbrush Step 5 in the column associated with that row enter 5% (or.05) (in the starter file, this would have been cell 045. you added any rows it will be different) Step 6 Directly below the cell you typed in "Percentage Salary Increase Type in "Good Raise or Bad Raise" making it Bold and in red. Step 7 to the column associated with that row enter a formula that subtracts The Baseine Net Before Raise from the calculated Net Income/Loss. (it is very critical to get this formula right or your results will be reversed) At this point this will equal (rero or null value) After steps 8 and 9 you will see if the raise creates a gain or a loss Step 8 in cell De enter the formula that will multiple cell Ds times the percentage increase you put in the cell where you entered SX Look good so far but what about the employment expenses? 30 Step 9 Now add the 5300 into any amount you may have already put in the section for Employment Expenses (cell 019) Step 10 Look at the net income/loss after the raise. Was 5% raise a good thing or not? Step 11 Save your worksheet Deliverable 3-What If Goal Seek A sound financial plan in personal finance budgeting is to have a plan for every dollar of income. In other words, you want the Net Income/Loss figure to be zero. Now is the time to do some planning Step 1 Copy the Master Budget file into a new worksheet Step 2 Rename the worksheet as "What-if Goal Seek" Status Check: At this point, you should have 3 worksheets-- 1. Master Budget 2. Simple % Increase 3. What if Goal Seek Step 3 Let's assume that you are thinking about scaling back a bit or you are thinking to open your own business and need to know what is the minimum amount you need to bring in each month to allow you to stay on your current retirement plan. Let's make Excel do the work. Using "What-if" analysis. This is a complex manual process as some of your calculations (Give for example) are based on percentage of income. Just guessing on the lower salary will get you there eventually but with Excel's What-lf function, you get there in the blink of an eye. Note: WhatIf currently only work in the Excel Desktop app. If you have not already downloaded the desktop apps you will need to do so. Step 4 For our problem we want to know how much of a salary decrease for increase) will we need to cover all our expenses and still have "wiggle Room" (in this case $500). From the Data menu select What-If analysis. The select Goal Seek. (Screenshot for how this looks in both MAC and PC are to the left.) 1. Set the value of the cell containing the dollar value of Net Income/Loss to 500 2. And you are going to change the cell of your primary income. 3. Once you have done that, dick on OK and watch Excel do the work. 4. The resulting value in the primary revenue celi is what you would need to make to cover your expenses and have $500 extra each month. Note: this is a great tool to uses as well when looking at when you can retire on your Social Security Income. Step 5 Save your worksheet Deliverable 4-What If Scenario Summary What is the impact of salary adjustments to your bottom-line (Net Income/Loss) For this deliverable we will assume that your management has decided there is a need to cut your hours on a month to month bass. Assume they will need to cut your monthy take home pay by either 5%, 10%, or 20%. To be a solid financial planner, you need to know how this will effect you monthly Net Income/Loss Note: You do not need to make a sheet for this as Excel will build the sheet for you. Start with the Master Budget sheet 57 Step 1 Open the Master Budget worksheet. From the Data menu select What If analysis. The select Scenario Manager Step 2 You will need to build three scenarios that 1. Decreases your current salary by 5%, 2. Decreases it by 10% and 3. Decreases it by 20% The example shown in the PC Screenshot shows the formulas needed to 6000 salary by 20%. (multiplying by.8 reduces the amount by 20%) Step 3 Each time you add a Scenario it will bring you back the Scenario Manager screen. You can add as many as you like, but for this deliverable, you only need these three. For Each Scenario you will need to program in the proper code by following these steps. 1. Select Add 2. Put in the Scenario Name 3. Enter the Cell number to be changed (it will be the same for all three scenarios). 4. Click on OK 5. This will bring you to the Scenario Values form. Type in the correct formula using the number you provided in your master budget as your main income and multiply by the percentage .e.5% 95, 10%9, and 20% -.8) Step 4 Once the three are completed click on "Summary" button and it will bring up the screen to identify the results cell This is the cell you want to see the scenario results in. In this case, it is the cell showing the Net Income/loss (cell 043 if you have not added any rows to the starter file. Note: Adding this cell will show you how each reduction Impacts your bottom line when you do the summary. Step 5 Select the button next to Scenario Summary 1 Select Ok and Excel will immediately create and display the new Scenario Summary worksheet which should look something like the one shown at the bottom of this page. Step 6 Save the Workbook Status Check: At this point, you should have worksheets 1. Master Budget 2. Simple Increase 3. What if Gool Seek 4. Scenario Summary Final Step Submit the xkx file to your assignment submission link First Name Last Name Budget Date Raise Revenue (takehome) Source 1 Source 1 Source 3 Total Revenue $ SUM(D4:07) Expenses Live Rent/Mortgage Electricity Gas (home) Cable/Internet Water Trash Insurance Gas (Auto) Employment Expenses Give Tithing Charity Goal 10% 5% Grow Goal Retirement Emergency Fund Life Insurance Savings Owe Taxes Taxes n/a Payoff Owe Debt Student Loans Car Payment Credit Card Payment Total Expenses =SUM(D11:035) S Expenses Live Rent/Mortgage Electricity Gas (home) Cable/Internet Water Trash Insurance Gas (Auto) Employment Expenses Give Tithing Charity Goal 10% 5% Grow Goal Retirement Emergency Fund Life Insurance Savings Owe Taxes Taxes n/a Payoff Owe Debt Student Loans Car Payment Credit Card Payment - SUMID 11:035) Total Expenses 1 2 4-08-037 3 Net Income/Loss