Question

The Scenario: A corporation has offered a scholarship that will cover 75% of the technology costs for college students who qualify. You have decided to apply for the scholarship and must provide a detailed spreadsheet that itemizes your technology needs. For this project, you will create a spreadsheet that has three categories: hardware, software, and accessories. You will look up prices for each item listed in the spreadsheet. Please provide:

Four items and prices under hardware category

Four items and prices under software category

Four items and prices under accessories category

Afterwards, create formulas that provide the following information for the scholarship committee:

Totals for each category

Sub total amount

Tax (8%) amount

Grand Total (including the tax)

In addition, please provide:

Amount of a 15% student discount

Total that reflects the 15% discount

Amount covered by the 75% scholarship

Amount covered by the student

Format the spreadsheet by incorporate Cell Styles where applicable

Next, create two charts based on the following data:

A column chart that reflects the itemized equipment and their costs

A pie chart that reflects the costs per category

(please solve this) Thank You.

Answer 1

The steps are given as per the sheet designed

1. Open MS Excel and create a new Workbook.

2. In A1, enter Category, in B1 enter Item and in C1 enter Cost

3. Enter the data in Cell A2 as Hardware. Select cells A2:A7 and click Merge&Center in the Home Ribbon.

4. Enter the data in Cell A9 as Software. Select cells A9:A14 and click Merge&Center in the Home Ribbon.

5. Enter the data in Cell A16 as Accessories. Select cells A16:A21 and click Merge&Center in the Home Ribbon.

6. Column B,C add the items per category, cells B2:B5 Hardware items and cells C2:C5 will have the cost of the item, cells B9:B12 Software items and C9:C12 will have price of the items and cells B16:B19 will have the Accessories item and C16:C19 will have the cost of the items.

7. In B6, Enter the data as Hardware Cost and in C6 fill the cell as =SUM(C2:C5), which will have the subtotal of Hardware category.

8. In C7, Enter the data as Tax(8%) and D7 as =D6*8% , which gives the tax for the subtotal.

9. In C13, Enter the data as Software Cost and in D13 fill the cell as =SUM(C9:C12), which will have the subtotal of Software category.

10. In C14, Enter the data as Tax(8%) and D14 as =D13*8% , which gives the tax for the subtotal.

11. In C20, Enter the data as Accessories Cost and in D20 fill the cell as =SUM(C16:C19), which will have the subtotal of Hardware category.

12. In C21, Enter the data as Tax(8%) and D21 as =D20*8% , which gives the tax for the subtotal.

13. In C24, Enter Grand Total and in D24 as =SUM(D2:D21), which gives the grand total.

14. In C26:C29 Enter the titles or labels as given in the screenshot of the sheet.

15. In D26, enter the formula as =D24*15%, which gives the 15% of the grand total.

16. In D27, enter the formula as =D24-D26, which gives the total after deducting the student discount.

17. In D28, enter the formula as =D27*75%, which gives the 75% of the amount to be paid (i.e. the scholarship).

18. In D29, enter the formula as =D27-D28, which gives the amount to be paid by the student after scholarship.

Now, formatting, as there is no special mention about the formatting in the question, normal formatting is done in this sheet. The steps to do the formatting done in the sheet.

All the cost and total are formatted as currency.

Next comes the Chart

First Column Chart:

1. Click Insert Tab-> Charts. Choose Column or Bar chart, Empty chart will be created.

2. Right click Chart Area and Select Data.

3.

4. The chart can be formatted as per the requirements.

5. The chart title can be changed as the Itemized Cost.

Pie Chart:

1. Select Insert->Pie chart. An Empty chart will be inserted

2. Right click Chart Area and Select Data.

3.

4. Select the data and in the Horizontal (Category) Axis Labels Click Edit and enter the values as shown below

5.

6. Click Ok. The pie chart will be inserted. Change the Title as per the requirements.