Please help me make this excel spreadsheet. It is for CSIS 100 class and I have no access to excel. Thank you for your time! I can plug my numbers and what not in I just don't have enough time with excel at one time to complete. Thank you!

w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe Aa BbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Paste BI UV ab X 2 AvLAV Normal No Spacing Heading 1 Heading 2 Title Subtitle Styles Dictate Sensitivity Pane CSIS 100 LAB: EXCEL BUDGET AND WHAT-IF ANALYSIS ASSIGNMENT TUTORIAL Under your Lab: Excel Budget and What-if Analysis Resources, you will find Lab: Excel Budget and What-if Starter Template that will provide you the baseline for starting the budget. If you start with this file and complete each step in these instructions, you will not have too many issues with this lab. The What-if Analysis gets a little tricky but there is a video to watch in your course shell for this module/week and Google can be your friend. And of course, do not forget-your faculty is available to guide you along as well. I always suggest that if you have an issue that you send the work you have already completed along with your question. In the end of this assignment, you will need to submit a single Excel Workbook with four (4) worksheets. Master Budget Simple Precent Increase What-if Goal Seek Scenario Summary (you create the Scenario and Excel generated this worksheet) Master Budget Step 1 Open the Starter File workbook Step 2 Rename the worksheet as Master Budget 1. Right Click on the Tab that says Starter File 2. When you see Starter File is highlighted Type in Master Budget to change the name Status Check: At this point, you should have one worksheet Master Budget Step 3 Populate the data for Revenue . 1. Rename the Source 1 (Cell BS) to a name the represents your source (i.e., Full-time Job, Disability, Unemployment...) 2. Enter your Monthly Take-Home Pay for this source in cell D5 (see note below) We use take-home (after tax) figures for this lab as calculating the tax withholding is outside the scope for our purposes. 3. If you have additional sources use Source 2 and Source 3 rows. If you have more that three sources, do not add additional rows. Just limit to 3. Adding rows will throw off the instructions You may notice that the starter file already has a formula in cell D8 that sums your total revenue. There are two other formulas included--D39 sums your total expenses and D43 shows your Net Income or Loss. These are there to Page 1 of 11 Page 1 of 11 2419 words x English (United States) Accessibility: Investigate Focus E 90%w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Table Design Layout ? Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe Aa BbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Paste BI Uv ab X 2 Av LV Av EE Normal No Spacing Heading 1 Heading 2 Title Subtitle Styles Dictate Sensitivity Pane CSIS 100 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 in Column D. For Expenses, just complete the expenses for Live, and Debt Owe. The Grow will be covered in Step 5 and the Give will be addressed in Step 6. Again, do not add any rows. | Page 2 of 11 Page 2 of 11 2419 words X English (United States) Accessibility: Investigate Focus E 90%w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Table Design Layout ? Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe Aa BbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Paste BI Uv ab X 2 Av LAV Normal No Spacing Heading 1 Heading 2 Title Subtitle Styles Dictate Sensitivity Pane CSIS 100 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. . 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. . 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 off 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. . Life Insurance--In the event you pass away, you need to still provide for your family. Get a lot and get it early (Whole Life is CHEAP when you are in your 20s and VERY EXPENSIVE when you reach your 40s. . 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. 1. In the Grow section in Column C under the word Goal put in Your Goal amounts for each area 2. In column D put in the monthly amounts you plan to invest in each area Step 6 Let's Make Excel work a little more here. 1. In the Give section Column C 1djust the percentages for your personal Tithing and Charity 2. In column D put in formulas to calculate your contribution amounts for Tithing and Charity. The formula will multiply the percentages in column C by the Total Revenue (D8 in the started file). Later you will get to see how using formulas in Excel will adjust the amounts as your take-home goes up or down. You do not need to balance your budget for this lab, so the following is for your information only: At this point (with the initial formulas and the new ones you added) the worksheet now calculates your bottom line in Cell D43 (Net Income/Loss). The goal would Page 3 of 11 Page 2 of 11 2419 words X English (United States) Accessibility: Investigate Focus E 90%w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Table Design Layout ? Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe Aa BbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Normal No Spacing Heading 1 Heading 2 Title Subtitle Styles Dictate Sensitivity Paste BI Uv ab X 2 Av LV Av EE Pane CSIS 100 be for this to be 0 (zero) as that would indicate that every cent you make is being properly put to work for you. If the figure is positive-find a place in your Grow section to put the excess. If the number is negative find a place in your Live section to reduce. Status Check: At this point, you should have I worksheet 1. Master Budget Page 4 of 11 Focus E Page 4 of 11 2419 words X English (United States) Accessibility: Investigate 90%w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Table Design Layout ? Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe Aa BbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Paste BI Uv ab X 2 Av LAV Normal No Spacing Heading 1 Heading 2 Title Subtitle Styles Dictate Sensitivity Pane CSIS 100 Simple % Increase When you set up a budget in Excel with the proper formulas to calculate the factors, it is a great place to see how an adjustment in income (up or down) will affect your bottom line. It's been a great day at work. Say your boss just came in and told you that you got a promotion to a manager position and with that a raise to your take-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 suits). 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 was 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 ended up with less take-home pay-taking how less is never a good day. So, let's use excel to tell you if it is a good day or a bad day. Step 1 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. Simple % Increase Step 3 Find the Cell with that calculated Net Income/Loss Figure (cell D43 if you did not add any rows). 2. In the cell directly under that cell and manually type in the calculated amount from Cell D43 of the Master Budget 3. In the cell to the left type in "Baseline Net Before Raise" Align the text to the right and Set the Font color to red and bold the text. Step 4 In the row directly under (C45) Type "Percentage Salary Increase.' 3 Format it exactly as you did the text above it. In the D column associated with that row enter 5% (or.05) (in the starter file, this would have been cell D45, if you added any rows, it will be different) Step 5 Directly below the cell you typed in "Percentage Salary Increase" Type in "Good Raise or Bad Raise" making it Bold and in red. In the D column associated with that row enter a formula that subtracts The Baseline Net Before Raise from the calculated Net Income/Loss. (It is very critical to get this formula right or your results will be reversed) Page 5 of 11 Page 5 of 11 2419 words X English (United States) Accessibility: Investigate Focus E 90%w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Table Design Layout ? Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe Aa BbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Paste BI Uv ab X 2 Av LV Av Normal No Spacing Heading 1 Heading 2 Title Subtitle Styles Dictate Sensitivity Pane CSIS 100 The results will look like this (with different numbers) 43 Net Income/Loss 2,340 44 Baseline Net Before Raise $ 2,465 45 recentage Salary Increase 5% Good Raise or Bad Raise $ (125) At this point this will equal 0 (zero or null value) After steps 8 and 9 you will see if the raise creates a gain or a loss Step 6 In cell D4 (next to the word Raise) enter the formula that will multiple cell D5 times the percentage increase (cell D45). 2. Now add the $300 into any amount you may have already put in the section for Employment Expenses (cell D19) 3. Look at the Good Raise or Bad Raise (cell D46) If negative number shows, then it was not so good of a day. You should also have seen an increase in both Tithing and Charity as well Step 11 Save your worksheet Status Check: At this point, you should have 2 worksheets-- 1. Master Budget 2. Simple % Increase Page 6 of 11 Page 6 of 11 2419 words X English (United States) Accessibility: Investigate Focus E 90%w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Table Design Layout ? Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe Aa BbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Paste BI Uv ab X 2 Av LAV E Normal No Spacing Heading 1 Heading 2 Title Subtitle Styles Dictate Sensitivity Pane CSIS 100 What-If Goal Seek 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 the minimum amount is 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-If function, you get there in the blink of an eye. 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 the minimum amount is 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-If function, you get there in the blink of an eye. Note: What-if currently only work in the Excel Desktop app. If you have not already downloaded the desktop apps you will need to do so. Page 7 of 11 Page 7 of 11 2419 words QX English (United States) Accessibility: Investigate Focus E 90%w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Table Design Layout ? Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe AaBbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Paste BI Uv ab X 2 Av LAV Normal No Spacing Heading 1 Heading 2 Title Subtitle Styles Dictate Sensitivity Pane CSIS 100 Step 4 For our problem we want to know how much of a salary decrease (or increase) will we need to cover all our expenses and still have "wiggle Room" (in this case $500). 1. 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.) DC MAC Screenshot Goal Seek Goal Seek X Set cell: Set cell: 46 o value: To value By changing cell: By changing cell: OK Cancel Cancel OK 2. Set cell: Type in D43 (if you did add any rows or columns) 3. To Value: type in the value you want. In this case 500 4. By Changing: The value of your primary income cell D5 When you click on OK excel will change the value or your primary income source until Cell D43 equals 500. Keep in mind all the values in the worksheet, based on formulas are changing as well (i.e., Tithing and Charity). Step 5 Save your worksheet Status Check: At this point, you should have 3 worksheets-- 1. Master Budget 2. Simple % Increase 3. What-If Goal Seek Page 8 of 11 Page 7 of 11 2419 words QX English (United States) Accessibility: Investigate Focus E 90%w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Table Design Layout ? Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe Aa BbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Paste BI Uv ab X 2 Av LVAv E Normal No Spacing Heading 1 Heading 2 Title Subtitle Styles Dictate Sensitivity Pane CSIS 100 What-If Scenario Summary What would be the impact of a 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 basis. Assume they will need to cut your monthly take home pay by either 5%, 10%, or 20%. To be a solid financial planner, you need to know how this will affect you monthly bottom line. Using Scenario Summary feature in Excel, you can see what your take home would be based on multiple factors. It works much like the What-if you did on the last page except it allows you to do multiple what-ifs at the same time. Step 1 Open the Master Budget worksheet. 2. From the Data menu select What-if analysis. 3. The select Scenario Manager. 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%) .9 reduces the amount by 10%) .95 reduces the amount by 5%) Step 2 You will need to build the three scenarios in Scenario Manager. In Scenario Manager 1. Click on Add 2. Name the Scenario for 5 % name it 5% Salary Decrease 3. Changing Cells Make sure it says D5 4. Click OK 5. In Scenario Value type =D5*.95 Click OK 7. Repeat steps 1 through 6 for each Scenario Page 9 of 11 Page 8 of 11 2419 words X English (United States) Accessibility: Investigate Focus E 90%w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Table Design Layout ? Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe AaBbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Title Subtitle Styles Dictate Sensitivity Paste BI Uv ab X 2 Av LV Av E Normal No Spacing Heading 1 Heading 2 Pane CSIS 100 Step 3 When you have completed all three the Scenario Manager Screen should show the following: Scenario Manager ? 5% Decrease Add.. 10% Decreas 20% Decrease Delete Edit . Merge. Summary . Changing cells: SD$5 Comment: Created by User on 6/3/2020 Modified by User on 6 /3 /2020 Show Close Step 4 1. Once the three are completed click on "Summary" button and it will bring up the screen to identify the results cell. Scenario Summary X Report type Scenario summary Scenario PivotTable report Result cells: D43 OK Cancel . 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 D43 if you have not added any rows to the starter file. When this is done Page 10 of 11 Accessibility: Investigate Focus E 90% Page 10 of 11 2419 words X English (United States)w Lab Excel Budget and What-If Analysis Assignment Tutorial Home Insert Draw Design Layout References Mailings Review View Table Design Layout ? Tell me Share Comments Times New... 12 A A Aav AL AaBbCcDdEe AaBbCcDdEe Aa BbCcD AaBbCcDdEE AaBb( AaBbCcDdEE Paste BI Uv ab X 2 Av LVAv Normal No Spacing Heading 1 Heading 2 Title Subtitle Styles Dictate Sensitivity Pane CSIS 100 Step 5 3. Select the button next to Scenario Summary Select Ok and Excel will immediately create and display the new Scenario Summary worksheet which should look something like the one here. Scenario Summary Current Values: Decrease by 5% Decrease by 10% Decrease by 20% Changing Cells: SD$5 $ 5,000 $ 4,750 $ 4,500 $ 4,000 Result Cells: $D$43 $ 2,465 $ 2,290 S 2,115 $ 1,765 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Step 6 Save the Workbook Status Check: At this point, you should have 6 worksheets-- Master Budget 2. Simple % Increase 3. What-If Goal Seek Scenario Summary Final Submit the xlsx file to your assignment submission link in your course shell Step Page 11 of 11 Page 10 of 11 2419 words X English (United States) Accessibility: Investigate Focus E 90%