Part A

Creating Charts and Linking Worksheets

Scenario

Now, you want to use the knowledge of Microsoft Excel that you have gained in your office. Your manager has asked you to create a report showing the variance between the actual and projected results. You realize that just looking at numbers does not give a clear picture. Therefore, you decide to use the powerful charting feature of Microsoft Excel.

Discuss the following in relation to charts:

• What are the different ways of representing numerical data? What does each type of chart represent? Does each chart have a different or specific function? Explain with the help of examples.

In the meeting, your manager is impressed. He now asks you to change the data in worksheets for January, February, and March, as it has now been finalized. As the data changes, so do the chart and the report you created. Your manager questions you as to how this was achieved.

Discuss with reference to the following:

• Linking worksheets and 3-dimensional (3D) formulas

Part B

Using Microsoft Excel as a Budgeting Tool

Microsoft Excel can be used for data analysis, and thereby, decision making. At a personal level, the benefit to support decision making can be utilized to create family budgets. Budgeting is a strategic planning initiative, allowing a family to organize and control financial resources, recognize goals, and project future spending. Even more, a family budget, if used as intended, helps a family prepare for unidentified or unexpected expenses.

Task

Use the following steps to prepare monthly and summary budget worksheets.

1. Open a new Microsoft Excel workbook. Save this workbook at an easily accessible location. (Easily accessible locations could include your desktop, your documents folder, or an external flash drive.) Also, while completing the steps for this assignment, it would be a good idea to periodically save your work so that you do not lose any progress in case of computer malfunction.
2. In the new and open workbook, notice the three available worksheets, labeled as Sheet1, Sheet2, and Sheet3, in the bottom-left area of the window. Rename Sheet1 to Master Monthly Budget.
3. Design and format a budget layout similar to the illustration provided by using all the formatting skills you have learned from your textbook and from the graded exercises of Week 1 and Week 2.
4. Ensure that you use all the income and expense labels given in the illustration. You can also add additional income or expense labels, as you design your monthly budget layout. Also, use standard number formats for all data entry and output areas.
5. The Starting Balance could be $67,000. However, you can also start with a balance of your choice. The Ending Balance should be calculated by using a formula with cell references of the Starting Balance plus the actual Total Cash Flow.
6. The Change in Balance should be calculated by using a formula with cell references of the Ending Balance divided by the Starting Balance, then subtracted by 100 percent. Also, remember to format this output as a percentage.
7. For the Current Status, write an IF function to output Good Standing when the change in balance is equal to or greater than 0 percent and to output Monitor Spending Habits when the change in balance is less than 0 percent.
8. Other formulas using cell references will also be needed:
   1. Use the SUM or any other function of your choice to calculate Total Income (Projected and Actual).
   2. Use the SUM or any other function of your choice to calculate Total Expense (Projected and Actual).
   3. Use a function with cell references to subtract Total Expense from Total Income to calculate Total Cash Flow (Projected and Actual).
   4. For all income-related variances, use a function with cell references, subtracting the projected value from the actual value.
   5. For all expense-related variances, use a function with cell references, subtracting the actual value from the projected value.
9. For all the Variance output values, apply conditional formatting using the icon set 3 Traffic Lights (Rimmed). For specifying the formatting rule, use the number type and set color to green for all values greater than zero, yellow for a value equal to zero, and red for all values less than zero. Notice in the illustration provided that a value of zero will output the yellow color.
10. For the Change in Balance output value, apply conditional formatting using the icon set 3 Triangles. For specifying the formatting rule, use the number type and set color to green triangle for all values greater than zero, yellow triangle for a value equal to zero, and red triangle for all values less than zero.
11. Now, your master worksheet is ready. Right-click the Select All button and copy all the contents of the worksheet. Go to Sheet2, right-click the Select All button, and paste the copied contents in the worksheet. All the content, formats, and formulas will carry over; however, you may need to make formatting adjustments if needed. Rename this worksheet tab to “January 2012” and the worksheet title in the first row to “Family Budget January 2012.”
12. For the Starting Balance, use a 3D cell reference which links the Ending Balance from the Master Monthly Budget worksheet.
13. For all Monthly Income and Monthly Expense cells, enter the projected and actual amounts. Projected values are estimated amounts of income and expenses you would foresee in a given month. These values can be made up. Actuals would be what actually comes in and goes out.
14. Click here to view an illustration showing what your January 2012 worksheet will represent after the income and expense values (projected and actual) are entered.
15. Now, use the same process (the one used to create the January 2012 worksheet) to create additional monthly budget worksheets for February, March, April, May, and June. For each new month, copy the contents of the Master Monthly Budget worksheet and use the previous month’s Ending Balance linked to the current month’s Starting Balance as a 3D cell reference. For example, the Starting Balance of February 2012 will be the Ending Balance of January 2012.
16. When entering the income and expense values (projected and actual) for the remaining months of the year, use different values. This would apply to income and expense types that could vary from month-to-month. For example, the actual cost of groceries is sure to vary each month. Also, allow at least three months to have expenses that exceed beyond the actual income levels for the month.
17. After all the monthly budget sheets are prepared and completed, create a similar budget sheet to represent a summary of the averages for the six months. For this, copy the contents of the Master Monthly Budget worksheet to a new worksheet and rename the new worksheet to 6 Month Averages.
18. Provide the worksheet title “Family Budget 6 Month Average” in row 1. Also, adjust all the projected and actual labels to Average Projected and Average Actual. Use a similar labeling for the Starting Balance, Ending Balance, Change in Balance, and Status labels.
19. Next, use 3D cell reference formulas to average all the projected and actual values, starting balance, ending balance, and change in balance using all appropriate cell reference values from all monthly budget worksheets from January through June. In addition, use the same type of IF function as used in the monthly budget worksheets to determine the status of the summary budget worksheet.
20. After the 6 Month Averages worksheet is complete, create two charts of your choice using this data to output the 6 Month Average Summary of Projected vs. Actual Income and 6 Month Average Summary of Projected vs. Actual Expenses. Move each chart to a separate worksheet. Rename the worksheets and apply formatting and appropriate labeling based on the concepts learned for creating charts in the textbook.
    Note: The income chart should include all income labels and the expense chart should include all expense labels.
21. Finally, using the South University Online Library or the Internet, research a minimum of two sources listing the benefits of using a home budget. Create a documentation worksheet including a name for the workbook, your name, date, and purpose of the workbook. In addition, use the Insert ribbon to include a text box. Size the text box to approximately the size of a normal page or a littler larger. Based on your research, in the text box, write a 250- to 500-word reflection describing what you have learned from this assignment.

Cite all research sources using APA format.