+1(978)310-4246 credencewriters@gmail.com
  

CSIS 100
LAB: EXCEL BUDGET AND WHAT-IF ANALYSIS ASSIGNMENT INSTRUCTIONS
OVERVIEW
Believer or non-believer personal financial management is just the smart thing to do. As servants
of Christ, it is critical for us to understand that God truly owns it all as seen in (Psalm 24:1 NIV)
which says, “God owns it all and I am a 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 life, the percentages you contribute to each category may change but they all stay
present except for the category of Owe Debt which the financial manager in each of us would
hope to eliminate. This all starts with a personal budget.
INSTRUCTIONS
In this lab, we will use Excel to develop and budget and analyze it with Excel’s powerful What-if
analysis.
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)
Please see the Lab: Excel Budgeting and What-if Assignment Tutorial for step-by-step
instructions and use the Lab: Excel Budget and What-if Starter Template to get started.
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
Page 1 of 11
CSIS 100
Step 3
Populate the data for Revenue
1. 1. Rename the Source 1 (Cell B5) 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
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
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
Page 3 of 11
CSIS 100
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
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 1 worksheet
1. Master Budget
Page 4 of 11
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
1. 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”
4. Align the text to the right and Set the Font color to red and bold the text.
Step 4
1. In the row directly under (C45) Type “Percentage Salary Increase.”
2. Format it exactly as you did the text above it.
3. 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)
Page 5 of 11
CSIS 100
Step 5
1. Directly below the cell you typed in “Percentage Salary Increase” Type in
“Good Raise or Bad Raise” making it Bold and in red.
2. 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)
The results will look like this (with different numbers)
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
1. 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.
4. 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
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
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.)
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
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
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
6. Click OK
7. Repeat steps 1 through 6 for each Scenario
Page 9 of 11
CSIS 100
Step 3
When you have completed all three the Scenario Manager Screen should show the
following:
Step 4
1. Once the three are completed click on “Summary” button and it will bring
up the screen to identify the results cell.
2. 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
CSIS 100
Step 5
3. Select the button next to Scenario Summary
4. Select Ok and Excel will immediately create and display the new Scenario
Summary worksheet which should look something like the one here.
Step 6
Save the Workbook
Status Check: At this point, you should have 6 worksheets-1. Master Budget
2. Simple % Increase
3. What-If Goal Seek
4. Scenario Summary
Final
Step
Submit the xlsx file to your assignment submission link in your course shell
Page 11 of 11
First Name Last Name Budget
Date
Revenue (takehome)
Source 1
Source 2
Source 3
Raise
$

⇦ =SUM(D4:D7)
Total Expenses
$

⇦ =SUM(D11:D35)
Net Income/Loss
$

⇦ =D8-D37
Total Revenue
Expenses
Live
Rent/Mortgage
Electricity
Personal Entertainment
Cable/Internet
Water
Household Allowance (Food & Such)
Insurance
Gas (Auto)
Employment Expenses
Give
Goal
10%
5%
Tithing
Charity
Grow
Goal
Re rement
Emergency Fund
Life Insurance
Savings
Owe Taxes
Taxes
n/a
Owe Debt
Payoff
Student Loans
Car Payment
Credit Card Payment
ti
1
2
3
4
First Name Last Name Budget
Date
Revenue (takehome)
Source 1
Source 2
Source 3
Raise
$

 =SUM(D4:D7)
Total Expenses
$

 =SUM(D11:D35)
Net Income/Loss
$

 =D8-D37
Total Revenue
Expenses
Live
Rent/Mortgage
Electricity
Personal Entertainment
Cable/Internet
Water
Household Allowance (Food & Such)
Insurance
Gas (Auto)
Employment Expenses
Give
Tithing
Charity
Grow
Goal
10%
5%
Goal
Retirement
Emergency Fund
Life Insurance
Savings
Owe Taxes
Taxes
Owe Debt
n/a
Payoff
Student Loans
Car Payment
Credit Card Payment

Purchase answer to see full
attachment

  
error: Content is protected !!