# 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