Camp Bright Firewood, create excel program help

Anonymous
timer Asked: Jul 4th, 2017
account_balance_wallet $20

Question Description

create excel programs , all the requirement attached

Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from t SAM website. New Perspectives Excel 2016 | Module 8: SAM Project 1a Camp Bright Firewood WORKING WITH ADVANCED FUNCTIONS Saleh Almutairi edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. Staff ID 1103 1053 1056 1035 1094 1066 1050 1054 1070 1040 1010 1073 1069 1060 1043 1034 1084 1078 1036 1074 1076 1009 1089 1022 1033 1065 1078 1048 1053 1064 Name Adam Moriarty Artie Jimenez Bo Peterson Claire Hunter Eileen Randall Flossie Frampton Grace Richards Hayley Jin Hiro Marumi Isaac Runyon Isabella Thorne James Gutierrez Joey Stockton Julio deSouza Kira Weston Liam Swanson Maria Flatley Melinda Masterson Oscar Alberts Philip Jones Richard Tepper Robert Miller Sarah Winters Sienna Shapiro Stephanie Ito Stephen McAllister Sylvia Lee Tanya Oldman Tristan Reinholt Walter Kaminsky Age Service Years 18 28 24 35 18 23 30 25 23 33 42 22 23 24 32 35 19 20 33 22 21 44 19 38 35 23 20 30 25 23 Base Salary 1 9 7 10 1 0 5 8 4 8 25 3 0 7 4 18 1 2 16 5 4 25 2 16 17 6 3 13 8 6 First Aid Certification Year 2017 2015 2013 2016 2013 2016 2017 2013 2013 2016 2017 2014 2015 2013 2015 2016 2017 2017 2015 2015 2014 2014 2015 2016 2016 2017 2013 2013 2014 2013 Years since First Aid Certification College Graduate No Yes Yes No No Yes Yes Yes Yes Yes Yes Yes Yes Yes No No No No Yes No No Yes No Yes Yes No No Yes Yes Yes Leadership Training No Yes No Yes No No Yes No Yes No Yes No No Yes No Yes No Yes Yes No Yes Yes No Yes Yes Yes Yes Yes Yes Yes Transportation Group Leader Bunk Leader Candidate for Leadership Training Staff ID Staff Name Service Years 1036 Oscar Alberts Total Staff Leadership Trained Staff All Staff Service Years Base Salary Average Service Years 0 $ 0 15,00 $ 1 15,50 $ 2 16,75 $ 5 17,50 $ 7 18,50
New Perspectives Excel 2016 | Module 8: SAM Project 1a Camp Bright Firewood WORKING WITH ADVANCED FUNCTIONS GETTING STARTED • Open the file NP_EX16_8a_FirstLastName_1.xlsx, available for download from the SAM website. • Save the file as NP_EX16_8a_FirstLastName_2.xlsx by changing the “1” to a “2”. o • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the file NP_EX16_8a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Dean Hilson is the director of Camp Bright Firewood. He started planning the camp staffing assignments for next year in an Excel table, and needs your help completing the table. First, he’d like to confirm that all the staff data was entered correctly into the worksheet. Go to the Staff Listing worksheet and complete the following steps: a. Apply a conditional formatting Highlight Cells Rule to the range A2:A31 that formats any duplicate values with Light Red Fill with Dark Red Text. b. Correct the duplicate values by updating the Staff ID for Artie Jimenez to 1055 and the Staff ID for Sylvia Lee to 1087. (Hint: When this sub-step is completed, the conditional formatting rule should no longer highlight any values in the range.) 2. Delete the conditional formatting rule applied to the range D2:D31. 3. In cell E2, enter a formula using the HLOOKUP function and structured references to determine a staff member’s base salary (which is based on Service Years): a. Use a structured reference to the Service Years column ([Service Years]) as the lookup_value argument. b. Use an absolute reference to the range Q15:U16 as the table_array argument. c. Use 2 as the row_index_num argument. New Perspectives Excel 2016 | Module 8: SAM Project 1a d. Use TRUE as the range_lookup argument, so that the formula will find the closest approximate value (as staff members do not receive a raise after every year of service.) Fill the formula into the range E3:E31 if necessary. 4. In cell G2, enter a formula without a function using structured references to determine the number of years since Adam Moriarty received his last First Aid Certification. The formula should subtract the value shown in the First Aid Certification Year column ([First Aid Certification Year]) from 2018 and should automatically fill to the range G3:G31. 5. The conditional formatting rule Dean created in the range G2:G31 highlights staff members who were last First Aid certified 3 or more years ago (and will be required to retake the First Aid Certification class), but it doesn’t stand out enough. Edit the conditional formatting rule applied to the range G2:G31, so that the highlighted cells are formatted using the bold font style and the Orange, Accent 2 font color (6th column, 1st row in the Theme Colors palette). 6. According to camp policy, staff members need to be at least 23 years old to transport students. Dean now wants to determine how many members of his staff will be available to drive. In cell J2, enter a formula using the IF function and a structured reference to determine if Adam Moriarty can be a Transportation team member. 7. a. The IF function should first determine if the staff member’s age is greater than or equal to 23. Use a structured reference to the Age column ([Age]). b. If the staff member’s age is greater than or equal to 23, the function should return the text Yes (using “Yes” for the value_if_true argument). c. If the staff member’s age is not greater than or equal to 23, the function should return the text No (using “No” for the value_if_false argument). To be a Group Leader, the staff member must either have more than 3 service years or be a college graduate. In cell K2 enter a formula using the IF and OR functions, as well as structured references, to determine if Adam Moriarty can be a group leader. 8. a. The IF function should first determine if the staff member’s Service Years is greater than 3 OR if the staff member’s college graduate status is “Yes”. Remember to use a structured reference to the Service Years ([Service Years]) and the College Graduate columns ([College Graduate]). b. If a staff member meets one or both of those criteria, the function should return the text Yes (using “Yes” for the value_if_true argument). c. If a staff member meets neither of those criteria, the function should return the text No (using “No” for the value_if_false argument). To be a Bunk Leader, a staff member must have over 4 service years and have completed leadership training. New Perspectives Excel 2016 | Module 8: SAM Project 1a In cell L2, enter a formula using the IF and AND functions, as well as structured references to determine if Adam Moriarty can be a bunk leader. 9. a. The logical test in the IF function should determine if the staff member’s Service Years is greater than 4 AND the staff member’s Leadership Training status is “Yes”. Remember to use a structured reference to the Service Years ([Service Years]) and the Leadership Training columns ([Leadership Training]). b. If a staff member meets both of those criteria, the function should return the text Yes (using “Yes” for the value_if_true argument). c. If a staff member meets none or only one of those criteria, the function should return the text No (using “No” for the value_if_false argument). Many of the special staff teams require leadership training, which is offered to staff with more than 1 year of service at Camp Bright Firewood. Dean wants to identify the staff members eligible for leadership training in the table. In cell M2, enter a formula using a nested IF function and structured references to determine first if a staff member already has completed leadership training, and if not, whether that staff member is eligible for leadership training. 10. a. If the value of the Leadership Training column is equal to the text “Yes”, the formula should return the text Completed (using “Completed” as the value_if_true argument). Remember to use a structured reference to the Leadership Training column ([Leadership Training]). b. If the value of the Leadership Training column is not equal to yes, the formula should determine if the value in the Service Years column ([Service Years]) is greater than 1. c. If the staff member’s Service Years value is greater than 1, the formula should return the text Yes (using “Yes” as the value_if_true argument of the nested IF function). d. If the staff member’s Service Years value is not greater than 1, the formula should return the text No (using “No” as the value_if_false argument of the nested IF function). Staff members with over 3 years of experience are considered Senior Staff members and receive first choice of staffing assignments. Dean needs a new calculated column in the table to identify senior staff members. In cell N1, enter the text Staff Level as the calculated column heading. (Hint: If a filter arrow appears in cell N1, ignore it.) 11. In cell N2, enter a formula using the IF function and a structured reference to determine if Adam Moriarty is a Senior Staff member. a. The IF function should first determine if the staff member’s Service Years is greater than 3. Remember to use a structured reference to the Service Years column ([Service Years]). b. If the staff member’s Service Years is greater than 3, the function should return the text Senior (using “Senior” for the value_if_true argument). New Perspectives Excel 2016 | Module 8: SAM Project 1a c. 12. If the staff member’s Service Years is not greater than 3, the function should return the text Junior (using “Junior” for the value_if_false argument). Dean wants a quick way to look up staff members by their Staff ID. In cell Q3, nest the existing VLOOKUP function in an IFERROR function. If the VLOOKUP function returns an error result, the text “Invalid Staff ID” should be displayed by the formula. (Hint: You can test that this formula is working by changing the value in cell Q2 to 0, but remember to set the value of cell Q2 back to 1036 when the testing is complete.) 13. 14. In cell Q4, enter a formula using the VLOOKUP function using the following parameters. a. Use cell Q2 as the lookup_value argument. b. Use CBFStaff[#Data] (which represents the table shown in A2:N31) as the table_array argument. c. Use 4 as the col_index_num argument. d. Use FALSE as the range_lookup argument, so that the function will only return an exact match to the Staff ID value shown in cell Q2. Dean wishes to determine several totals and averages for his staff. In cell Q9, enter a formula using the COUNTIF function to count the number of staff with Leadership Training. Use the Leadership Training column (CBFStaff[Leadership Training]) as the range in the formula and “Yes” as the formula criteria. 15. 16. In cell R9, enter a formula using the AVERAGEIF function to determine the average number or service years of staff members with leadership training. a. Use the Leadership Training column (CBFStaff[Leadership Training]) as the range in the formula. b. Use “Yes” as the formula criteria. c. Use the Service Years column (CBFStaff[Service Years]) as the average_range argument. In cell R10, enter a formula using the AVERAGE function to determine the average service years of all staff as shown in the Service Years column (CBFStaff[Service Years]). Your workbook should look like the Final Figures on the following pages. Save your changes, close the document, and then exit Excel. Follow the directions on the SAM website to submit your completed project. New Perspectives Excel 2016 | Module 8: SAM Project 1a Final Figure 1: Staff Listing Worksheet – Columns A:N Final Figure 2: Staff Listing Worksheet – Columns O:U
New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a SpringLeaf Designs FORMATTING, FORMULAS, AND CHARTS GETTING STARTED • Open the file NP_EX16_CS1-4a_FirstLastName_1.xlsx, available for download from the SAM website. • Save the file as NP_EX16_CS1-4a_FirstLastName_2.xlsx by changing the “1” to a “2”. o • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically. With the file NP_EX16_CS1-4a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. Geneva Huddleston works for SpringLeaf Designs. Over the years, the company has released a variety of products, and Geneva’s manager would like her to make a spreadsheet to help the company keep track of sales figures. Change the theme of the workbook to Office. 2. On the Merchandise worksheet, change the width of columns D through G to 14.00 characters. 3. Change the height of row 2 to 32 pt. 4. Merge and center the contents of the range B2:J2. 5. Format the merged ranges B2:J2 and L2:M2 as described below: 6. a. Apply the Title cell style. b. Apply bold formatting. c. Change the font color to Green, Accent 6, Darker 50% (10th column, 6th row of the Theme Colors palette). d. Change the cell fill color to Green, Accent 6, Lighter 40% (10th column, 4th row of the Theme Colors palette). Format the ranges B3:J3 and L3:M3 as described below: a. Center cell contents. b. Change the font to Arial Black. c. Change the font size to 9 pt. New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a d. Change the font color to Green, Accent 6, Darker 50% (10th column, 6th row of the Theme Colors palette). e. Apply the Top and Double Bottom Border cell border. f. Apply wrap text formatting. 7. Select range B4:B14, and then increase the indent of the cell contents once. 8. Italicize the range B15:B18 and the range B20:B21. 9. Format the range I4:I18 with the Accounting number format with zero decimal places and use $ as the symbol. (Hint: Depending on how you complete this action, the number format may appear as Custom instead of Accounting.) 10. Geneva’s manager would like to see some additional information to help make sales decisions. Enter a formula in cell J4 using the IF function to it whether or not the item Beaded Earrings should go on sale. It will go on sale if its current sales rank (in cell G4) is 8th or lower. (Hint: If the cell value is greater than or equal to 8, it will go on sale.) a. If this condition is true, the item will go on sale. (Hint: The if_true value should be “YES”.) b. If this condition is false, the item doesn’t need to go on sale. (Hint: The if_false value should be “-”.) c. Copy the formula created in cell J4 to the range J5:J14. 11. Enter a formula in cell E4 to calculate how long each item has been on sale. This is calculated by subtracting the launch date in cell D4 from the Data Updated date in cell C20. To show the time in years, divide that result by 365. Use an absolute reference to the date in cell C20. Copy the formula from cell E4 to cells E5:E14. 12. For the range E4:E18, update the number format by decreasing the number of decimal places displayed to 1. 13. Enter a formula in cell I15 using the SUM function to total the current sales in the range I4:I14. 14. Enter a formula in cell I16 using the AVERAGE function to calculate the average current product sales based on the range I4:I14. 15. Enter a formula in cell I17 using the MAX function to calculate the highest selling product based on the range I4:I14. 16. Enter a formula in cell I18 using the MIN function to calculate the lowest selling product based on the range I4:I14. 17. Add Solid Fill Green Data Bars to range I4:I14. 18. Enter a formula in cell M6 using the VLOOKUP function to find an approximate match for the sale status of scarves. Use “Scarves” as the lookup_value, the range B3:J14 as the table_array, 9 as the col_index_num argument, and TRUE as the range_lookup argument. New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a 19. In cell C21, enter a formula using the TODAY function that displays the current date. 20. Hide column F. 21. The company is considering expanding, and Geneva’s manager asked her to review some numbers for growth options. On the Projections worksheet, use AutoFit to modify the width of column B to best fit cell contents. 22. Use the Format Painter to copy the format only from the 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 B15:F15. 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 (rate), C8 as the total number of payments (nper), and C4 as the loan amount (pv). Enter this formula in cell C9, and then copy the formula to the range D9:F9. 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 $12,000. In the Goal Seek calculations, set the value of cell F9 to -12,000 and select cell F4 (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 C15 using the SUM function to calculate total revenues for the current year (or the range C12:C14). Use the Fill Handle to copy the formula from cell C15 to the range D15:F15. 26. There’s a lot of numerical information in this worksheet, so some visuals might help make the data easier to interpret. Insert Column Sparklines in the range G12:G14 based on data in the range C12:F14. Change the sparkline color to Green, Accent 6, Darker 50% (10th column, 6th row of the Theme Colors palette). 27. Select the range B12:C14, and insert a 2-D 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: 29. a. Change the chart style to Style 2. b. Change the chart title to Current Year Revenues and change the font color to Blue (8th column, 1st row of the Standard Colors palette). c. Add Center data labels to the chart. d. Move the chart legend to the Left position. Select the Actual and Projected Revenues chart located in the range H16:M30. Make the following changes to the chart: a. Change the major units of the vertical axis to 5000000. b. Add Growth Scenarios as the horizontal axis title and Revenue as the vertical axis title. c. Remove the data labels from the graph. New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a d. Move the chart legend to the Bottom position. 30. Set the range B1:G15 as the print area of the worksheet. Change the worksheet orientation to Landscape. 31. Delete the Sales Summary worksheet. Your workbook should look like the Final Figures on the following pages. (The value in cell F4 on the Projections worksheet generated by the Goal Seek Analysis has intentionally been blurred in the Final Figure.) Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. Final Figure 1: Merchandise Worksheet New Perspectives Excel 2016 | Modules 1–4: SAM Capstone Project 1a Final Figure 2: Projections Worksheet – A1:G16 Final Figure 3: Projections Worksheet – H1:M31
Author: Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from website. New Perspectives Excel 2016 | Modules 1-4: SAM Capstone Project 1a SpringLeaf Designs FORMATTING, FORMULAS, AND CHARTS Saleh Almutairi edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. SpringLeaf Designs - Merchandise Data Product Name Beaded Earrings Metallic Earrings Combination Earrings Beaded Bracelets Bangle Bracelets Cuff Bracelets Necklaces Leather Belts Fabric Belts Scarves Purses Total: Average: High: Low: Data Updated: Current Date: Accessory Type Launch Time on Date Market Overall (Yrs) Sales Current Rank Sales Rank Head Head Head Arm Arm Arm Body Body Body Body Body ##### ##### ##### ##### ##### ##### ##### ##### ##### ##### ##### 1 3 9 2 5 6 4 8 7 11 10 #DIV/0! 0,0 0,0 10/14/2016 1 4 11 7 6 5 9 3 8 10 2 Product Lookup Total Sales $ $ $ $ $ $ $ $ $ $ $ 4.495.870 2.375.905 686.809 2.574.871 1.784.517 1.373.430 1.912.569 741.057 974.569 508.022 647.318 Current Sales 11898700% 10504400% 2992900% 9649000% 10111600% 10171200% 7877100% 10523100% 9396200% 5654400% 10630200% Sale Needed Product Metallic Earrings Cuff Bracelets Scarves Sale Needed $ $ - SpringLeaf Designs - Loan and Revenue Scenarios Loan Term (Yrs) Conservative Status Quo Total Loan Amount Annual Interest Rate Monthly Interest Rate Loan Term (Yrs) Total Number of Payments Growth $0 7% 0,40% 3 36 Moderate Growth $150.000 7% 0,40% 3 36 $350.000 7% 0,40% 4 48 Monthly Payment Amount Revenues Head Accessories Arm Accessories Body Accessories Total Current Year $ $ $ Conservative Growth 3.047.520 $ 3.591.816 $ 5.289.720 $ 3.352.272 $ 3.950.998 $ 5.818.692 $ Moderate Growth 4.266.528 5.028.542 7.405.608 cenarios Aggressive Expansion $ 450.000 7% 0,40% 5 60 Aggressive Expansion $ $ $ 5.333.160 6.285.678 9.257.010 ACTUAL AND PROJECTED REVENUE Head Accessories $22.000.000 $21.000.000 $20.000.000 $19.000.000 $18.000.000 $17.000.000 $16.000.000 $15.000.000 $14.000.000 $13.000.000 $12.000.000 $11.000.000 $10.000.000 $9.000.000 $8.000.000 $7.000.000 $6.000.000 $5.000.000 $4.000.000 $3.000.000 $2.000.000 $1.000.000 $- Arm Accessories Body Accessories $7.405.608 $5.289.720 $5.818.692 $5.028.542 $3.591.816 $3.950.998 $3.047.520 $3.352.272 $4.266.528 Current Year Conservative Growth Moderate Growth JECTED REVENUES Body Accessories $9.257.010 $7.405.608 $5.028.542 $6.285.678 $4.266.528 $5.333.160 Moderate Growth Aggressive Expansion Camile's Electronics Sales Summary Note: Incomplete Document City Product TVs Laptops Tablets Cell phones Video Games Total Tax Rate Product Sales by Items and State New Orleans Philadelphia Miami Baltimore Total 84520,60 75426,65 64897,32 65982,24 290826,81 92584,72 95864,22 105645,59 58964,37 353058,90 75650,25 77590,48 80547,10 56800,00 290587,83 35724,48 42587,99 56879,14 35675,25 170866,86 25874,47 27845,69 32470,58 30985,89 117176,63 Sales Taxes Collected 9% 7606,85 8332,62 6808,52 3215,20 2328,70 28291,91 8% 6034,13 7669,14 6207,24 3407,04 2227,66 7% 4542,81 7395,19 5638,30 3981,54 2272,94 Average tax rate Lowest tax paid across products Final revenue after deducting taxes Rounded final revenue after deducting taxes Average tax rate TBD 18183,80 23396,95 18654,06 10603,78 6829,30

Tutor Answer

criss53
School: Cornell University

Here is the first.

Author:

Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from t
SAM website.

New Perspectives Excel 2016 | Module 8: SAM Project 1a

Camp Bright Firewood

WORKING WITH ADVANCED FUNCTIONS
Saleh Almutairi

edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the
SAM website.

Staff ID
1103
1055
1056
1035
1094
1066
1050
1054
1070
1040
1010
1073
1069
1060
1043
1034
1084
1078
1036
1074
1076
1009
1089
1022
1033
1065
1087
1048
1053
1064

Name
Adam Moriarty
Artie Jimenez
Bo Peterson
Claire Hunter
Eileen Randall
Flossie Frampton
Grace Richards
Hayley Jin
Hiro Marumi
Isaac Runyon
Isabella Thorne
James Gutierrez
Joey Stockton
Julio deSouza
Kira Weston
Liam Swanson
Maria Flatley
Melinda Masterson
Oscar Alberts
Philip Jones
Richard Tepper
Robert Miller
Sarah Winters
Sienna Shapiro
Stephanie Ito
Stephen McAllister
Sylvia Lee
Tanya Oldman
Tristan Reinholt
Walter Kaminsky

Age

Service Years
18
28
24
35
18
23
30
25
23
33
42
22
23
24
32
35
19
20
33
22
21
44
19
38
35
23
20
30
25
23

Base Sal...

flag Report DMCA
Review

Anonymous
Goes above and beyond expectations !

Similar Questions
Hot Questions
Related Tags
Study Guides

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors