Christy Chen works at SpringLeaf Designs, a Seattle-based company that makes jewelry and accessories out of recycled materials. Christy has created a workbook to analyze costs and profits by product line. She has also compiled sales forecasts for the next five years. She has asked you to complete the data entry and analysis and to format the worksheets so they have a consistent and professional look.
Download the following file from the SAM website:
Open the file you just downloaded and save it with the name: IL_Excel2013_CS_P1a_FirstLastName_2.xlsx
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 IL_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.
1. Switch to the Sheet1 worksheet. Make the following formatting changes: a. Merge and center the range F3:I3. Apply the Heading 2 cell style to the merged cell and change the fill color to Blue, Accent 5, Lighter 60% (9th column, 3rd row in the Theme Colors palette). Replicate these formatting steps for range C3:E3. b. Make the fill color of the range B19:I19 White, Background 1, Darker 35% (1st column, 5th row in the Theme Colors palette). c. Add a Thick Box Border around the range B2:I19.
d. Add a Left Border to the ranges C3:C19 and F3:F19.
2. Make the following formatting changes to the range C4:I4
a. Change the font to Verdana.
b. Change the font color to Blue-Gray, Text 2 (4th Column, 1st row in the Theme Colors palette). c. Change the font size to 10 pt.
d. Apply Bold formatting.
e. Center align cell contents.
f. Apply text wrapping.
3. Apply Bold formatting to the ranges B19:C19 and E21:E23. 4. Italicize text in the ranges B6:B8, B10:B12, and B15:B16. 5. Add a comment to cell F4 with the text: Average selling price listed. Prices vary slightly by store location and channel. Remove any existing text from the comment box, including the user name, before entering the new comment. 6. In cell F6, type a formula to calculate the average selling price per beaded earring unit. (Hint: The average selling price for the beaded earrings is calculated by dividing the total Sales values in cell C6, by the number of Units Sold value in cell D6.) Copy the formula from cell F6 to the ranges F7:F8, F10:F13, and F15:F18. 7. Apply the Currency Number format to the range F6:F18. (Tip: The Currency Number format should have 2 decimal places displayed by default. If it doesn’t, update the range to display 2 decimal places.) 8. Select the range D6:D18 and use the Quick Analysis tool to create a formula that calculates the total number of units sold. The formula will use the SUM function and should appear in cell D19. 9. Enter a formula in cell E6 to calculate the percentage of total sales accounted for by beaded earrings. The formula will divide beaded earring sales in C6 by total sales in C19. (Hint: Use an absolute reference to the total sales in cell C19, and a relative reference to beaded earring sales in cell C6). Copy the formula from cell E6 to the ranges E7:E8, E10:E13, and E15:E18. 10. Apply the Percentage Number format with no decimal places to the range E6:E18. 11. Based on range I6:I18, enter a formula in cell I21 that uses the MAX function to identify the highest average unit profit across SpringLeaf Design’s product lines. 12. Based on range I6:I18, enter a formula in cell I22 that uses the MIN function to identify the lowest average unit profit across SpringLeaf Design’s product lines. 13. Based on range I6:I18, enter a formula in cell I23 that uses the AVERAGE function to calculate SpringLeaf Design’s average profit per unit across...
Please join StudyMode to read the full document