Excel Project CIS 110 Excel Final Version 3

CIS 110 Excel Final Version 3

Project Description: 
<Project Description>

Instructions: 
For the purpose of grading the project you are required to perform the following tasks: 
Step    Instructions    Points Possible
1    Start Excel. Download and open the file named DonorsFile.xls    0.000
2    Save the file as an Excel 2010/2013 workbook named Excel Final 2    0.000
3    Create a new worksheet. Name the sheet Event Costs. Set the tab color to standard Yellow.    2.000
4    Enter the following data in the specified cells:Cell A1: 
Donors Appreciation EventCell A2: 
Event BudgetCell A3: 
Number of Guest:Cell C3: 
60    3.000
5    Type the data from the following figure into the range A4:D8 as shown:
Cell A4: Company
Cell A5: Site
Cell A6: Decorations
Cell A7: Entertainment
Cell A8: Miscellaneous
Cell B4: Signature Events
Cell B5: 350
Cell B6: 250
Cell B7: 260
Cell B8: 250
Cell C4: Small World Events
Cell C5: 430
Cell C6: 200
Cell C7: 340
Cell C8: 230
Cell D4: Martin Events
Cell D5: 325
Cell D6: 250
Cell D7: 270
Cell D8: 300    2.000
6    Set the width of column A to 13 characters.Set the 
width of columns B thru D to 90 pixels.
Wrap text cells in range A4:D4.Set 
height of rows 5 through 8 to 18 characters.    4.000
7    Insert a row above Miscellaneous, the new row should be row 8. Type Refreshments in cell A8.    1.000
8    In cell B8 type a formula that multiplies the number of guests in cell C3 by 8.In cell C8 
type a formula that multiplies the number of guests in cell C3 by 8.25.In cell D8 
type a formula that multiplies the number of guests in cell C3 by 7.55.    3.000
9    Type Total per Company in cell A10. Set cell A10 to Wrap text and the text align to right.    2.000
10    In cell B10 enter a Function to add all the values for the company Signature Events. Copy the formula into cells C10 and D10.    2.000
11    To get an idea of the amount of money spent on each category, enter a Function in cell E5 to average the three costs for “Site”. Copy the Function down to range E6:E10,    2.000
12    Type the word Average in cell E4.Apply 
accounting format to range B5:E10.Set range E5:E10 to 
no decimal places.Apply 
cell style “Total” to range A10:D10.    4.000
13    Merge and center range A1:E1. Set row 1 height to 60 pixels. Set font size to 24, bold. Fill cell color with Standard Color Dark Blue, set font color to White, Background 1. Center align (vertically align) cell.    5.000
14    Merge range A2:E2. Set row height to 40 pixels. Set font size to 18, bold. Fill cell color with White, Background 1, Darker 25%.    4.000
15    Select ranges A4:A10 and B4:E4. Set font to bold, set cell fill to Blue, Accent 1, Lighter 60%.    2.000
16    Center Text in range B4:E4.Set 
cell style for range E5:E10 to Explanatory.    2.000
17    Select ranges A5:A9 and E5:E9. Insert a 3D Pie Chart. Set pie chart to Style 10. Set the Quick layout to layout 2. Set the Data labels to center. Add the title Average Costs to the chart. Move the pie chart to a new sheet and name the sheet Average Costs. Change label options from value to percentage.    6.000
18    Click on the Event Costs tab to go back to that worksheet. Select range A4:D9 and insert a 3-D Clustered Column chart. Switch charts row and columns. Move the charts upper left corner to cell A12 and the lower right corner to cell E27. Set the chart style to 10.    6.000
19    Move the Event Costs worksheet tab so that it is the first tab from left to right. Move the Average Costs worksheet tab so that is the second from left to right. Change name of chart to Column Chart.    3.000
20    Open Sheet 1. Rename it Donations Summary.    1.000
21    Insert 2 rows before row 1. Type Current Date: in cell A1, type the date 1/31/2014 in cell A2.      3.000
22    Type Number of Days Since Pledge in cell F3.    1.000
23    Type a formula in F4 to calculate the number of days since the first donation.For this, subtract the date in cell E4 from the date in cell A2.
You will copy this formula down to all donors.
Now 
copy the formula in cell F4 into range F5: F203.    8.000
24    Type Approx. Donations to Date in cell G3.In cell G4, insert a
 Logical Function (Nested IF) that will calculate the approx. donation to date, copy this formula down to all donors.
CriteriaIf the donation is a one-time donation (OT) then the approx. donation to date will be the amount value in C4
If the donation type is monthly (M) then the approx. donation to date will be:         

(number of days since pledge / 30 ) * AmountOtherwise, the donation type is weekly (W) then the donation to date will be:          

(number of days since pledge/7) * Amount.    9.000
25    Sort the data so that the largest donor appears on the top.    0.000
26    Select range B3:G203 and set all borders.    2.000
27    Select range B3:G3 and set the text orientation to 90 degrees.    1.000
28    Copy the current worksheet, open a new worksheet and paste it there. Rename the new worksheet Donations Distribution. From this point forward, you will be working in the Donations Distribution Worksheet.    2.000
29    Select range H3:H203 and set all borders.    2.000
30    Select cell H3 and set the text orientation to 90 degrees, set Wrap Text, type Amount for Forest-Lake Area.    3.000
31    For the Donations Distribution worksheet, you will not need to see the information on columns C through F, so hide these columns.    2.000
32    In cell H4 select a Logical Function (Nested IF, IF and OR Functions) to calculate the amount of the donation in cell H4 that will go towards preserving the forest and lake area (by using the OR function you do not need to use a Nested IF Function).
CriteriaIf the Towns are 
Greenlake OR Clearwater, they have requested that 25% of their Approximate Donations to Date be designated specifically for the preservation of the lake and forest they share.Otherwise, the towns of 
Oakbridge and Chester have requested that 15% of their donations go to this area.    5.000
33    Copy formula down range H5:H203.    2.000
34    Sort the data in A to Z order based on the Donor column.    0.000
35    In cell J4 type Summary and use the Bold font.In cell J5 type 
Total Donations.In cell J6 type 
Lake-Forest Area.In cell J7 type 
Other Area.
Auto-fit column J.    2.000
36    In cell L5: Insert a Function to add the total donation amount in column G.
In cell L6: Insert a Function to add the total amount of the donation money that will be specifically dedicated to the forest and lake area (in column H).
In cell L7: Insert a formula that subtracts the amount in cell L6 from the amount in cell L5.    3.000
37    Select the range J4:L7, apply a Thick Box border to the range. Apply yellow fill to rangeJ4:L4.    1.000
38    Make the Event Costs worksheet the active worksheet. Make Cell A1 the active cell on this worksheet. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.    0.000
    Total Points    100.000