PROJECT DESCRIPTION
Andres Gonzales runs Eclipse Solar Solutions, a New Mexico-based company that manufactures solar panels and other solar products. Andres has created a workbook that tracks employee data and examines various growth scenarios. He has asked you to format the workbook so the worksheets have an appealing and consistent look. He then wants you to complete the scenario planning worksheet using formulas and charts so he can better decide on a future business strategy.
GETTING STARTED
• Download the following file from the SAM website:
o NP_Excel2013_CS_P1a_FirstLastName_1.xlsx
• Open the file you just downloaded and save it with the name:
o NP_Excel2013_CS_P1a_FirstLastName_2.xlsx
o Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.
• With the file NP_Excel2013_CS_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
1. Change the theme of the workbook to Office.
2. On the Employees worksheet, change the width of columns B through D to be 17.00 characters.
3. Change the height of row 2 to 30 pts.
4. Merge and center the contents of the range B2:J2.
5. Format the merged ranges B2:J2 and L2:M2 as described below:
a. Apply the Title cell style.
b. Change to font color to Orange, Accent 2, Darker 50% (6th column, 6th row in the Theme Colors palette).
c. Apply bold formatting.
d. Change the cell fill color to Gold, Accent 4, Lighter 40% (8th column, 4th row in the Theme Colors palette).
6. Format the ranges B3:J3 and L3:M3 as described below:
a. Center cell contents
b. Change the font to Copperplate Gothic Bold.
c. Change the font size to 10 pt.
d. Change to font color to Orange, Accent 2, Darker 50% (6th column, 6th row in the Theme Colors palette).
e. Apply a Top and Bottom cell border.
f. Apply text wrapping to cell H3.
7. Select range B4:C13 and right-indent the cell contents by two levels.
8. Italicize the range B14:B17 and the range B19:B20.
9. Enter a formula in cell J4 using the IF function to calculate the bonus for Sonya Martinez. Sonya receives a bonus if her Performance rating (in cell H4) is greater than or equal to 4.
a. If this condition is true, the employee receives a bonus of $10,000. (Hint: The if_true value should be 10000)
b. If this condition is false, the employee does not qualify for a bonus, and the bonus amount is 0. (Hint: The if_false value should be "0")
c. Copy the formula created in cell J4 to the range J5:J13.
10. Format range I4:J17 with the Accounting Number format with no decimal places.
11. Enter a formula in cell F4 to calculate employee tenure. Tenure is calculated by subtracting the start date in E4 from the Data Updated date in cell C19. Because you want to show tenure in years, you will then divide that result by 365. You will need to use an absolute reference to the Data Updated date in cell C19. Copy the formula from cell F4 to cells F5:F13.
12. For range F4:F17, update the number format by decreasing the number of decimal places displayed to 1.
13. Enter a formula in cell I14 using the SUM function to total the salaries in the range I4:I13.
14. Enter a formula in cell I15 using the AVERAGE function to calculate the average salary at Eclipse based on the range I4:I13.
15. Enter a formula in cell I16 using the MAX function to calculate the highest, or maximum, salary at Eclipse based on the range I4:I13.
16. Enter a formula in cell I17 using the MIN function to calculate the lowest, or minimum, salary at Eclipse based on the range I4:I13.
17. Add Gradient Fill Red Data Bars to range I4:I13.
18. Enter a formula in cell M6 using the VLOOKUP function to find an exact match for the bonus amount for employee Joyce Sommers. (Hint: Use "Sommers" as the lookup_value, the range B3:J13 as the table_array, 9 as the col_index_num argument, and False as the range_lookup argument.)
19. In cell C20, enter a formula using the TODAY function that displays the current date.
20. Hide column G.
21. On the Planning worksheet, modify the width of column B to best fit cell contents.
22. Use the Format Painter to copy the format only from range B3:F3 to the range B11:F11. Then use the Format Painter to copy the format only from the range B9:F9 to the range B16:F16.
23. Enter a formula in cell C9 using the PMT function to calculate the monthly payment on a loan using the assumptions listed in the Status Quo scenario. In the PMT formula, use C6 as the monthly interest rate, C8 as the total number of payments, and C4 as the loan amount. Enter this formula in cell C9, then copy the formula to the range D9:E9.
24. Select cell F9 and use Goal Seek Analysis to determine the loan amount in the Aggressive Expansion scenario based on a monthly payment of $10,000. In the Goal Seek calculations, set the value of cell F9 to -10,000 and select cell F4 (the Total Loan Amount) as the changing cell. Keep the outcome of the Goal Seek Analysis as the value of cell F4.
25. Enter a formula in cell C16 using the SUM function to calculate total revenues for the current year (or the range C12:C15). Copy the formula from cell C16 to the range D16:F16.
26. Insert Line Sparklines in range G12:G15 based on data in range C12:F15. Change the line color to Orange, Accent 2, Darker 50% (6th column and 6th row of the Theme Colors palette) and line weight to 1.5 pt.
27. Select range B12:C15. Use the Quick Analysis tool to insert a Pie chart segmenting revenues by product. Reposition the chart so the upper-left corner is in cell H2.
28. Make the following changes to the chart:
a. Change the chart style to Style 5.
b. Change the chart title to Current Year Revenues and change the font color to Dark Red (1st column, 1st row of the Standard Colors palette).
c. Add Inside End data labels to the chart.
d. Move the chart legend to the Right position.
29. Select the Actual and Projected Revenues chart located in the range H17:M30. Make the following changes to the chart:
a. Change the major units of the vertical axis to 500000.
b. Type Scenario as the horizontal axis title and Revenues as the vertical axis title.
c. Remove the data labels from the graph.
d. Move the chart legend to the Bottom position.
30. Set the print area as the range B1:G16. Change the worksheet orientation to Landscape.
31. Delete the Customers worksheet.
NOTE:
I am using Office 2007. So, following steps could not be completed:
5d, 17, 26, 29c and 29d.