Excel Personal Budget

Part 1 – Income and Expenses In this assignment, you will be creating an Excel worksheet to show your MONTHLY (January – December) Expenses and income.
(See the MyBudget.jpeg file attached – screenshot of what a sample monthly budget could look like. You can keep or change the items for Expenses and the items for Income.) Be sure you have the months labels and the items of each Expense and type of Income. Once you have filled in data (highlighted in yellow), you are ready to total the Expenses by month (columns), and then for each Expense item (rows). You may use addition formula or the SUM function. Now, do the same for the Income range of data. Total the Income by month (columns) and for for each type of Income item (rows). Part 2 – Difference between Income – Expenses Now, we are ready to see the difference between your Income minus Expense. Compute this by MONTH (for example for January only, total Income minus total Expense for January). If it is a negative number, that means you did not earn enough to cover expenses for that month. Now, copy the formula to find the differences for months February – December. You will see my results for this calculation by again, looking at the attached MyBudget.jpeg file. What does this data tell you? Did you make enough to cover your expenses? For fun, I applied Conditional Formatting, to the row of differences between the Income (how much you made) – the Expenses, for any value that was less than 0. See how the red color brings attention to the reader of the worksheet? Part 3 – Chart Now, select an item (row) to chart. It can be the difference between Income and Expenses, or perhaps your income for a certain item, let’s say your wages for the months Jan – December. Note, select the labels (months Jan – Dec) by highlighting it, THEN, click on the CTRL Key on your keyboard, then the row of data cells you wish to chart. If it is the row for Wages, then select that row of data across months Jan – December. Now, Insert Chart, and select your chart type. You can place the Chart on this same worksheet. What did you learn by charting this item? See attached .jpeg of what a sample Chart of Wages would look like from my sample worksheet. That’t it. I hope you see the power of using Excel by completing this assignment. What-If-Analysis – Goal Seek tool (THIS IS NOT REQUIRED FOR YOUR ASSIGNMENT, BUT A FUN THING TO DO) You might want to try your hand at doing a What-If-Analysis on your MyBudget worksheet. I copied the entire worksheet to a new tab, worksheet called What-If-Analysis. I wanted to know how much I needed to earn in December, in Wages (income) to have a zero or “0” balance, meaning I covered all of my expenses using the formula for Income – Expenses. So, my Goal Seek was to determine how much I needed to earn by working in December, to cover all my expenses for December. I wanted a 0 balance. Can you think of a Goal Seek for you worksheet? part 4 Requirements: 1111 Open excel file: excel2016_conditionalformatting.xlsx download Click the Challenge worksheet tab in the bottom-left of the workbook. Select cells B3:J17. Let’s say you’re the teacher and want to easily see all of the grades that are below passing. Apply Conditional Formatting so it Highlights Cellscontaining values Less Than 70 with a light red fill. Now you want to see how the grades compare to each other. Under the Conditional Formatting tab, select the Icon Set called 3 Symbols (Circled). Hint: The names of the icon sets will appear when you hover over them. Open excel file: excel2016_intropivottables.xlsx download Create a PivotTable in a separate sheet. We want to answer the question What is the total amount sold in each region? To do this, select Region and Order Amount. When you’re finished, your workbook should look like this: In the Rows area, remove Region and replace it with Salesperson. Add Month to the Columns area. Change the number format of cells B5:E13 to Currency. Note: You might have to make columns C and D wider in order to see the values. Open excel file: excel2016_morepivottables.xlsx download In the Rows area, remove Region and replace it with Salesperson. Insert a PivotChart, and choose the type Line with Markers. Insert a slicer for Regions. Use the slicer to only show the South and East regions. Change the PivotChart type to Stacked Column. In the PivotChart Fields pane to the right, add Month to the Legend (Series) area. Note: You can also click the PivotTable and then add Monthto the Columns area; the result will be the same. Open excel file:excel2016_whatifanalysis.xlsx download Click the Challenge tab in the bottom-left of the workbook. In cell B8, create a function that calculates the average of the sales in B2:B7. The workbook shows Dave’s monthly sales amounts for the first half of the year. If he reaches a $200,000 mid-year average, he will receive a 5% bonus. Use Goal Seek to find how much he needs to sell in June in order to make the $200,000 average. Open excel file: excel2016_intropivottables.xlsx download
Create a PivotTable in a separate sheet.
We want to answer the question What is the total amount sold in each region? To do this, select Region and Order Amount. When you’re finished, your workbook should look like this:
In the Rows area, remove Region and replace it with Salesperson.
Add Month to the Columns area.
Change the number format of cells B5:E13 to Currency. Note: You might have to make columns C and D wider in order to see the values.
Open excel file: excel2016_morepivottables.xlsx download
In the Rows area, remove Region and replace it with Salesperson.
Insert a PivotChart, and choose the type Line with Markers.
Insert a slicer for Regions.
Use the slicer to only show the South and East regions.
Change the PivotChart type to Stacked Column.
In the PivotChart Fields pane to the right, add Month to the Legend (Series) area. Note: You can also click the PivotTable and then add Monthto the Columns area; the result will be the same.
Open excel file:excel2016_whatifanalysis.xlsx download
Click the Challenge tab in the bottom-left of the workbook.
In cell B8, create a function that calculates the average of the sales in B2:B7.
The workbook shows Dave’s monthly sales amounts for the first half of the year. If he reaches a $200,000 mid-year average, he will receive a 5% bonus. Use Goal Seek to find how much he needs to sell in June in order to make the $200,000 average.