Excel program basic steps , computer science homework help

User Generated

Snunq512

Computer Science

Description

create excel pages following the requirements attached

Unformatted Attachment Preview

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
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

This is the final version. Have a nice day.

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 | 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
Met...


Anonymous
Awesome! Made my life easier.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags