{br} STUCK with your assignment? {br} When is it due? {br} Get FREE assistance. Page Title: {title}{br} Page URL: {url}
UK: +44 748 007-0908, USA: +1 917 810-5386 [email protected]

This project requires you to develop a spreadsheet that tracks the cost of elementary school supplies distributed to teachers over a two-month period. You’ll assume the role of Office Manager, who has multiple responsibilities, including distributing and maintaining classroom supplies. For this project, you must prepare an Excel spreadsheet and chart that will be sent to the school District Manager.

Instructions

Create a Spreadsheet

1. Start Excel and create a blank workbook.

2. Enter the data shown in the following table and then format, sizing columns as necessary and right-aligning all number values. Save your file, naming it School Supplies.

Any Town Elementary School

 

 

 

 

 

 

 

School Supplies Distribution

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Classroom Supplies

Cost/Unit

Sept. Units

Sept. Cost

% of Grand Total

Oct. Units

Oct. Cost

% of Grand Total

Copy Paper

3.99

40

 

 

32

 

 

#2 Pencils

0.75

29

 

 

12

 

 

Pens

4

22

 

 

8

 

 

Highlighters

3.29

12

 

 

10

 

 

Markers

4.79

35

 

 

22

 

 

Crayons

3.1

15

 

 

7

 

 

Colored Pencils

2.29

60

 

 

28

 

 

Staples

1.89

12

 

 

4

 

 

Tissues

1.49

22

 

 

9

 

 

Hand Sanitizer

2

16

 

 

7

 

 

3. Merge and center cells A1 through H1. Format the school name as Title style.

4. Merge and center cells A2 through H2. Format the label as Heading 1 style.

5. Bold the labels in row 4 and right align the labels above the numeric values.

6. Format the Cost/Unit values as Currency with 2 decimal places. Your spreadsheet should look like the figure below.

An image of the School Supplies excel workbook with formatted data.

7. In column D, enter appropriate formulas to calculate the September costs for each school supply. Your formulas must use cell references. Format the calculated values as Currency with 2 decimal places.

8. In cell A16, enter the label “Grand Total” and format it as bold.

9. In cell D16, enter a formula that uses a function to calculate the total of the column.

10. In column E, enter formulas that calculate the percentage of the grand total for each type of school supply (total cost of each item divided by the grand total). Your formulas must use appropriate absolute cell references. Format the calculated values as Percentage with 0 decimal places.

11. In cell E16, enter a formula that uses a function to calculate the total of the column (it should total 100%).

12. Repeat the process you used in steps 7–11 to calculate values for October.

13. Sort the school supplies alphabetically. (Be sure to select cells A5:H14 to sort the entire row of data.) Your spreadsheet should look similar to the figure below.

An image of the School Supplies excel workbook with the data sorted in alphabetical order.

14. Save the modified spreadsheet.

Create a Chart

1. Create a chart by first selecting the cell ranges for the chart. Drag from cell A4 to A14 to select the range and then press and hold the Ctrl key while you drag from cell D4 to D14. Continue to press and hold the Ctrl key while dragging from cell G4 to G14. Your selected cells should look like the figure below.

An image of selected cell ranges in the School Supplies excel workbook.

2. Insert a 3-D Clustered Column chart.

3. Change the chart title to read “Cost of Supplies.”

4. Position the chart with the upper-left corner in cell A18 and then size the chart until it extends to column H, as shown in the figure below.

An image of a chart made with the selected cell ranges in the School Supplies Excel workbook.

5. Put the following information into cell A41:

· Your name

· Your email address

· Your student number

· The course name and number

· Project number (584042)

6. Save and close the workbook.

image1.jpeg

image2.jpeg

image3.jpeg

image4.jpeg

This question has been answered.

Get Answer
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?