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:
At the cell address A1, enter the worksheet title: CAPE COD
ARTS COUNCIL
Merge and center the worksheet title across columns A-I
Change the font style to: Times Roman, font size to: 26 pts.
Apply the bold attribute
Apply the yellow fill color using the fill color palette on the toolbar
Apply the dark blue color from the font color palette on the toolbar
Move to cell A3 and enter the sub-title: Projected Budget.
Merge and center the cell's content across columns A-I
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.
Apply the following attributes: bold and the dark blue font color as
indicated in step 6
Move to cell A6 and enter the row heading: Income
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
Move to C7, highlight the column headings, and apply the
appropriate command to rotate the headings. Apply the bold attribute to the
column headings
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.
Use the appropriate function/formula wherever a
?????? appears to perform the necessary calculation. Format
the cells with a comma, no decimal places.
Use the appropriate function to calculate the average, minimum, maximum,
expenses for the 6 months. Format the cells to currency, no
decimal places.
Use the appropriate function to calculate the total number of label
expenses. Hint: Use the appropriate function to calculate labels not
values.
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.
Arrange the expenses in alphabetical [ascending] order. Be sure
to highlight all the labels, values, and expenses before invoking the
Sort command.
Insert a red double border style below the
entire last expense row
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.
Format the page setup for: landscape, remove the gridline (if
necessary). Horizontally and vertically center the text on the page.
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.
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.
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
Attach Assignment 4 Evaluation Sheet and submit all copies to
instructor.
