CISC 1050 Assn 5: Excel Tutorials 2, 3, and 4 due Oct. 31, 2016

Paul and Louise Anderson are a recently married couple in Laredo, Texas. Paul is currently in graduate school and Louise is the manager at a local restaurant. They want to use Excel to help manage their family budget, but they need help setting up the formulas and functions to project their monthly expenses and help them meet their financial goals.

Chapter 2 material:

1. Open the Anderson workbook, which is on your class Web page. Put your name in Backstage View, and then save the workbook as Anderson Budget.

2. On the Documentation sheet, do the following:

a. enter your name and the date.

b. Choose cell A1. Merge and Center the value in that cell over cells A1:B1

c. Put a border around cell A1

d. Select A3:B6 and format with All Borders

e. Fill A1:A6 with light gray, Background 2, Darker 10%

f. Format A1 in Title style.

3. Apply a theme, other than the Office theme, to format the workbook. Put the name of the chosen theme in Documentation in cell B6.

Chapters 2 and 3 (I can't separate it easily):

4. On the Budget worksheet, format the cells as follows:

a. Format A1 as Title style

b. Format A3 as Heading 2

c. Format A7:B7 and B37:N37 in Totals style

d. Make Bold the text in cells A3, A4, D4, A9, A20, A23, B38, B39, B41.

e. Using Fill Color, color cells A24:A26 in standard color Green; using Fill Color, color cells A27:A37 in standard color Red.

f. Merge and center text in A24:A26. Merge and center text in A27:A37.

g. Rotate text in cells A24 and A27 so that it is vertical (reads bottom to top).

h. Rotate values in cells C23:N23 45 degrees.

i. Using Fill Color, color cells C23:N23 some light color other than white.

j. Make the text color of cells A24 and A27 white.

k. Format ranges D5:E6 and A23:N37 with all borders. Put a border around E2.

l. Using Fill Color, make cells B5:B7, E2, E5:E6, B10:B20, B26:N26, B37:N37, B38:N38, B39:N39, and B41:N41 in a light gray.

5. In cell B7, calculate the couple’s total monthly income.

6. In row 23, use AutoFill to replace the numbers 1 through 12 with the month abbreviations Jan through Dec.

7. In rows 24 and 25, enter the couple’s monthly income by referencing the monthly income estimates in cells B5 and B6. Use absolute cell references. Compute the values in cells C24 and C25 and copy the values from column C to the rest of rows 24 and 25.

8. In cell C26, calculate the couple’s monthly income based on cells C24 and C25. Copy these values to the rest of row 26.

9. In row 37, enter formulas to calculate the total estimated expenses for each month.

10. In row 38, calculate each month’s net cash flow, which is equal to the total income minus the total expenses.

11. In row 39, calculate the running total of the net cash flow so that Louise and Paul can see how their net cash flow changes as the year progresses.

12. In the range B10:B19, calculate the average monthly expenses by category based on the values previously entered in rows 27 through 36.

13. In cell B20, calculate the total average monthly expenses.

14. The couple currently has $7,580 in their savings account. Each month the couple will either take money out of their savings account or deposit money. In row 41, calculate the end-of-month balance in their savings account by adding the value in cell E5 to the running total values of the net cash flow in row 39. Use an absolute cell reference for cell E5.

15. In cell E6, enter a formula to display the value of the savings balance at the end of December, which is the value in cell N41.

16. In cell F6, write an IF formula that will fill the cell with one of two values, based on the relationship between the Final Savings amount and the Initial Savings amount. If Final is greater, the value should be "Profit"; if not, the value should be "Loss".

17. Add conditional formatting to cells C38 to N38, so that the cell text is in red if the value in the cell is negative.

18. Add conditional formatting to cells C41 to N41 to color in green fill with dark green text all cells containing a value greater than 10,000.

19. In cells D10:D20, calculate the percent of total income (B7) allocated to each of the corresponding values in cells C10:C20.

20. Format cells C10:C20 in percent format with a % sign and one decimal place.

21. Format the numeric values in rows 24, 37 and 41 in Accounting Format with no decimal places.

22. Format the numeric values in cells B10:B20 in Currency format with no decimal places.

23. Create a new worksheet and label the tab Expense Chart.

Chapter 4 material:

24. Create a 3D pie chart for the expenses (cells B10:B19), using the values in cells A10:A19 as data labels.

25. Move the pie chart to the new worksheet starting in cell B2.

26. Format the pie chart as follows:

· Title the chart Expenses

· Add data labels on the Outside End

· Do not include the legend

· Pick a chart style (or format the chart yourself) so that each slice is labelled with both the name and the value represented by each slice

· Format the data labels to include the percent represented by each slice

· Rotate the chart so that the largest slice is in the front (at the bottom of the pie).

· Explode the largest slice (data point) from the pie 25%.

27. Paul and Louise would like to have $35,000 in their savings account by the end of the year. Louise is planning to ask for a raise at her job. Use Goal Seek to determine the value of cell B6 that will achieve a final savings balance of $35,000.

After you find the value, record what her new income should be by putting that into cell E2 that is labeled "calculated value" and save the workbook with the values of when Louise's income was still 4650.

28. Save and close the workbook.

Submit with the subject line A5 1050.