Create the attached workbook and worksheet click here for copy.   Save the workbook under the name: Projected Budget FY. Periodically save the file every 5-10 minutes.

Complete the steps as follows:

1. At the cell address A1, enter the worksheet title: CAPE COD ARTS COUNCIL
2. Merge and center the worksheet title across columns A-I
3. Change the font style to: Times Roman, font size to: 26 pts.
4. Apply the bold attribute
5. Apply the yellow fill color using the fill color palette on the toolbar
6. Apply the dark blue color from the font color palette on the toolbar
7. Move to cell A3 and enter the sub-title: Projected Budget. Merge and center the cell's content across columns A-I
    
8. Move to cell A4 and enter the sub-title: January to June . Merge and center the cell's content across columns A-I. Font style: Times Roman, font size: 14 pts.
9. Apply the following attributes: bold and the dark blue font color as indicated in step 6
   
    
10. Move to cell A6 and enter the row heading: Income
     
11. Move to cell C7, type in the text: Jan. Use the AutoFill feature to insert the remaining months. Be sure to type in the text: Totals in the appropriate cell address
    
     
12. Move to C7, highlight the column headings, and apply the appropriate command to rotate the headings. Apply the bold attribute to the column headings
13. Move to A8 and begin entering the text and values as indicated on the sample copy. Use the appropriate command and/or function to enter repetitive text. Format the text as it appears on the sample copy.
     
14. Use the appropriate function/formula wherever a ?????? appears to perform the necessary calculation. Format the cells with a comma, no decimal places.
    
     
15. Use the appropriate function to calculate the average, minimum, maximum, expenses for the 6 months. Format the cells to currency, no decimal places.
     
16. Use the appropriate function to calculate the total number of label expenses.  Hint:  Use the appropriate function to calculate labels not values.
     
17. Apply a color to the sheet tab and rename the worksheet to CC Training Budget.  Note:  Only Office XP users can apply a color to the sheet tab.  
18. Arrange the expenses in alphabetical [ascending] order. Be sure to highlight all the labels, values, and expenses before invoking the Sort command.
     
19. Insert a red double border style below the entire last expense row
     
20. Using the conditional formatting feature, establish a conditional format using all the monthly expenses that fall within the range 2700-5500. Apply a color pattern format to the condition. Click here to obtain step-by-step instructions for establishing a conditional format.
     
21. Format the page setup for: landscape, remove the gridline (if necessary). Horizontally and vertically center the text on the page.
22. Create a custom footer. Use the appropriate command icon to display the worksheet's name in the left section, your full name in the center section, and the filename in the right section.
     
23. Request 2 printouts. One printout displaying the normal view of a worksheet. The other printout should display the formulas. When printing the formulas select the option, "Fit on One Page", to print the worksheet on one page.
    
     
24. Create a clustered column bar graph with a 3-D visual effect (click to view a sample of the chart) using the column headings and total income data for the six months. (Request on-line help to assist with creating a chart). The simplest manner in which to create a chart after obtaining on-line help, is to use the Chart Wizard.  Note: The chart should be inserted as a new sheet, when prompted via the Chart Wizard window.
     

    If you have access to a color printer, apply a different color and/or pattern to each column bar for enhancement purposes.

    Chart Information

    • Chart Title: Projected Budget (Enter Current Year)
    • Category X Axis: Monthly Income
    • Value Z Axis: Total Amount
    • Remove Gridlines from chart area
    • Clear chart walls
    • Remove the Legend
    • Rotate Value Axis Title
    • Increase Chart Title, X & Y Axis Font
    • Apply any additional features to enhance the appearance of the chart
      
       
25. Attach Assignment 4 Evaluation Sheet and submit all copies to instructor.