Jan's Working with Numbers

Start with: , ex2-2-soccerbudget-Lastname-Firstname.xlsx (created in previous exercise)

You will practice moving, inserting, and formatting by making changes to ex2-2-soccerbudget-Lastname-Firstname.xlsx, which you previously created in Exercise 2-2. You will include new columns for the calculations that were used to get certain budget amounts.

1. From your Class disk open  ex2-2-soccerbudget-Lastname-Firstname.xlsx in the excel project2 folder to Sheet1.
2. Save to your Class disk in the excel project3 folder with the name  ex3-2-soccerbudget-Lastname-Firstname.xlsx 
3. Move: Move the Income cells, including the Totals, to be above the Expenses cells. [Hint: Cut and Insert Cells.] There should be a blank row between the Totals and Expenses rows. Move the column labels from below Expenses to above Income.

4. Insert: Between the columns Category and Budget, insert 2 columns. Label the columns Budget Quantity and Budget Cost each and wrap the text. These will be used to calculate the Budget column instead of the values that are in that column right now.

   Below the Trophies row insert a blank row for a second type of trophy.
   

5. Enter Data: Use the numbers in the illustration on the right to fill in the data in the new columns.
6. Create Budget formulas: In the Budget column, wherever values exist in both Budget Quantity and Budget Cost each, replace the values with a formula. [Multiply the value for Budget Quantity by the value for Budget Cost each. Skip rows for Equipment and Referees] For the two rows of Trophies, create a single formula that totals the cost of both types of trophies and put it on the row with the label Trophies.
7. Format: Cells A1, A2,and A3 use the font Impact and are merged and centered across the table. Font size for A1 = 22, A2 and A3 = 18. Make all of the row labels in Column A Bold.
8. Cell Style for labels: Create a cell style for the column labels named Label: Fill = Accent 2, Darker 50%, Font Color = White, Font = Calibri, Font Size = 11, Centered, Bold, Wrap Text. Apply this style to the column labels in row 5. Adjust the column widths and row heights as necessary to have labels on two lines for columns B, C, and F.
9. Cell Style for totals: Create another cell style for the Totals rows named Totals. (Select the total for the Budget column to base the style on and the current number format will be retained.) Use Font = Calibri, Font Size = 11, Bold, Font Color = Dark Blue Text 2; Fill Color = White, Background 1, Darker 15%. Apply the style to the table cells in the two Totals rows and also to the cells containing the words Income and Expenses. All totals should show a $ and no decimals.

10. Revise Over/Under Column: The last column is supposed to show how the actual income and expenses compare to the budgeted amounts. Being "over budget" for expenses is a bad thing. But being "over budget" for income is good since you earned more money than expected. This is confusing! You need a different approach.

    Change the column label to read Difference = Actual - Budget. Resize the column so that 'Difference =" is on one line and the rest is on the second line. Revise the formulas in this column to calculate this way.
    

11. Grand Totals: Leave a blank row below the expense Totals row and create a Grand Totals row below it. Type Grand Totals: in column A of the new row. In columns D and E write formulas that subtract the Expenses value from the Income value in that column. In column F use the same formula as in the rest of Column F. Format the Grand Totals row with the Totals style and border it with a dark wide border. The totals should show the $ and no decimals.

12. Rename Sheet: Change the name of Sheet1 to Soccer Budget and of Sheet2 to Budget Charts.
    

13. Charts: On the sheet Budget Charts, there is an extra square with no text by it in the Legend because you added a row for the second type of trophy. Change the Source Data, reselecting the labels cells and the Actual column values cells and omitting the blanks that are causing the problem. [Hint: In the Edit Series and Axis Labels dialogs, delete the existing formula for the data, and hold CTRL down while you select the cells.]

14. Create Column Chart: Copy the existing pie chart and paste it below on the same sheet. Change the Chart Type of the copy to Column. Format the chart area with the Fill Effect texture Bouquet. If necessary, format the Plot Area to use No Fill. Delete the Legend since there is only one series.

    It can be tricky to select cells with a gap like this. Errors are easier to see in a Column style chart than in a Pie style chart. Be sure to inspect your chart carefully to be sure you did not catch a blank cell in the labels or values.
    Notice that a comma goes between the non-adjacent ranges in the range selection.
    

15. Unneeded sheets: If you have a Sheet3, which you are not using, delete it.
16. Prepare to print: Edit or create the header for both sheets to show Exercise Excel 3-2 on the right, your name and the date on the left, and the workbook name and sheet name in the middle. Spell Check. Page Break Preview. Print Preview. (Each sheet should fit on one page.) Fix any problems. The horizontal axis labels in the column chart maynot be angled in your print preview even if they are on the sheet.  If the column labels overlap, make the chart narrower until the labels angle in Print Preview.
17. Save.
    [ex3-2-soccer budget-Lastname-Firstname.xlsx]
18. Print the Entire workbook.
19. Print formulas: Show formulas and print the Soccer Budget sheet only, in Landscape orientation, scaled to fit on a single page.
    

20. When all printing is complete, close the workbook without saving again.

    Your version of Excel may use somewhat different colors and chart styles than the illustrations below.