DIY Shelving Inc Download both files,one has the instructions and the second is the start of the excel project Project 1 Instructions Starting File: Projec

DIY Shelving Inc Download both files,one has the instructions and the second is the start of the excel project Project 1 Instructions
Starting File: Project 1 – Profit Analysis – Student.xlsx
DIY Shelving has asked you to develop a profit analysis report for upper management. Follow the steps to produce the
final results shown in Figure 1 at the end of this document.
Notes:
•
•
•
•
Remember: Project work must be your own individual work – no group work allowed!
This workbook has 2 worksheets: the Documentation worksheet (for the history of this workbook), and the
Profit Analysis worksheet. Be sure to work on the correct worksheet as indicated in the instructions.
You should use cell references in all formulas – do not re-enter data.
If your version of Excel does not have the same names for the colors and styles referenced here, select a color
and style that gives an equivalent appearance (use the image at the end of this document to verify).
Steps:
Download the starting file from the Assignment Dropbox and save it on your computer. Then open it to work on it.
1. In the Documentation worksheet, enter your name in cell B3 and the date you created this workbook in cell B4.
2. Enter a brief statement describing the business purpose of this workbook in cell B5.
3. Merge and center cell A1 across cells A1:B1. Increase the font size to 20. Add a fill with color Dark Red Accent 1 (or
equivalent color). Change the font color to White.
4. Change the font color of cells A3:A5 to Dark Red Accent 1 (or equivalent color) and bold these cells.
5. Add cell borders (normal weight) with color Dark Red Accent 1 (or equivalent color) for cells A3:B5.
6. Increase the height of row 5 to approximately one inch. Change the vertical alignment of cells A5:B5 to MiddleAlign,
so that the text will be centered vertically from top to bottom within the cell.
Switch to the Profit Analysis worksheet. Use formulas with cell references to complete any required calculations in these
steps.
7. Merge and center cell A1 across cells A1:H1. Increase the font size to 20. Add a fill with color Dark Red Accent 1 (or
equivalent color). Change the font color to White.
8. Change the font color of cells A3:H3 to Dark Red Accent 1 (or equivalent color) and bold the text. Center align these
cells, and turn on Wrap Text. Add top and bottom cell borders (medium weight) with color Dark Red Accent 1 (or
equivalent color). Adjust the columns to show the text in row 3 as shown in Figure 1 below.
9. In cell D4, create a formula that will calculate the profit markup amount, using the profit markup percentage in cell
J6. (Hint: Remember to use cell references – do not re-enter data.)
10. As you did in step 9 above, complete the rest of the rows in col. D, calculating profit markup amount for each of the
remaining items.
11. In cell F4, create a formula to calculate the total sales value for the item in this row. Total sales value for an item is
calculated as the sum of cost and profit, times the number of units sold.
12. As you did in Step 11, complete the rest of the rows in col. F, calculating total sales value for each of the remaining
items.
13. In cell G4, create a formula to calculate the total profit amount for the item in that row, which is calculated as item
profit times the number of units sold.
14. As you did in step 16, complete the rest of the rows in col. G, calculating total profit amount for each of the
remaining items.
Copyright © 2019 by P. McDermott-Wells – All Rights Reserved.
May not be posted on any internet site except FIU Canvas.
15. In cell H4, create a formula to calculate the total tax collected for the item in the row, using the sales tax percentage
in cell J9. Tax is collected on the entire amount that the customer pays (total sales amount in this case). (Hint:
Remember to use cell references – do not re-enter data.)
16. As you did in Step 14, complete the rest of the rows in col. H, calculating total sales tax collected for each of the
remaining items.
17. In cells E15:H15, create formulas to calculate the totals for each of those columns.
18. In cells C16:H16, create formulas to calculate the smallest value for each of those columns.
19. In cells C17:H17, create formulas to calculate the largest value for each of those columns.
20. In cells C18:H18, create formulas to calculate the average of the values in each column.
21. Format all money values with the Currency style. Adjust column widths as needed to display all of the results.
22. Format the all units sold values with the Number style, with no decimal places.
23. Center justify the product numbers in col. A.
24. Add a top border to cells A15:H15 with color Dark Red Accent 1 (or equivalent color).
25. Add a double bottom border to cells A15:H15 with color Dark Red Accent 1 (or equivalent color).
26. Bold the font in cells A15:H15, and change the font color to Dark Red Accent 1 (or equivalent color).
27. Bold the font in cells A16:A18, and change the font color to Dark Red Accent 1 (or equivalent color).
28. Set the page orientation to Landscape.
29. Add a page footer containing your name in the left-hand section, and the date you completed the workbook in the
right-hand section.
30. Set page breaks to ensure that only columns A:H appear on page 1. You should only have 2 pages in the print
preview.
31. Ensure that your file is in Normal view (not page layout or print preview). Save your file, close Excel, and then submit
into Canvas.
Figure 1
Copyright © 2019 by P. McDermott-Wells – All Rights Reserved.
May not be posted on any internet site except FIU Canvas.
DIY Shelving, Inc.
Author
Date
Purpose
DIY Shelving, Inc.
Item #
C2100
C2300
B2320
B2360
B2500
S3800
S3810
S3820
S4800
S4810
S4820
Totals
Lowest
Highest
Average
DesciptionCost per Unit
Profit per Unit
Units Sold Total SalesTotal ProfitTotal Tax Collected
13-inch stackable
8.15
cube
48562
16-inch stackable
9.75
cube
79855
Profit markup
16×24 stackable
16.45
box shelf
63847
35%
24×36 stackable
22.15
box shelf
72586
30×36 stackable29.5
box shelf
42893
Sales Tax
12X24 shelf – white
12.1
83512
7.00%
12X24 shelf – black
12.45
64281
12X24 shelf – natural
12.15
77413
15X24 shelf – white
17.5
59425
15X24 shelf – black
17.95
51007
12X24 shelf – natural
17.65
56823

Purchase answer to see full
attachment