Create the below table as it is in a new Excel workbook.
|
Item Name |
Total Items |
Units Sold |
Price ($) |
|
Smartphone |
150 |
120 |
700 |
|
Laptop |
100 |
60 |
1200 |
|
Tablet |
200 |
140 |
350 |
|
Smartwatch |
180 |
100 |
250 |
|
Bluetooth Speaker |
220 |
180 |
80 |
|
Wireless Earbuds |
300 |
250 |
50 |
|
Digital Camera |
90 |
45 |
850 |
|
Gaming Console |
70 |
50 |
500 |
|
External Hard Drive |
200 |
150 |
100 |
|
Smart TV |
50 |
40 |
1000 |
- Change the header row's font size to 12 and make the text bold.
- Fill the header row cells with black colour and change the font colour of the header row to white.
- Remove the dollar ($) sign from the price column header. Change the data format of the price column to currency and set it dollars ($) with two decimal places and a thousand separator.
- Create a new column in between Total Items and Units Sold. Name it Units Unsold. Use the formula below to calculate the units unsold. Units Unsold = Total Items - Units Sold
- Add another column next to the price. Name it as selling price. Calculate the selling price using the below formula. Selling Price = Price + Price * 20%
- Create another column next to the selling price. Name it Total Sales per Item. Use the formula below to calculate the total sales per item. Total Sales per Item = Units Sold * Selling Price
- Create another column next to the Total Sales per Item. Name it Total Spend per Item. To calculate the total spend per item, use the formula provided below. Total Spend per Item = Total Items * Price
- Create another column next to the Total Spend per Item. Name it Profit/Loss Amount. Calculate the Profit/Loss amount using the below formula. Profit/Loss Amount = Total Sales per Item - Total Spend per Item
- Below the data table, calculate the net total profit/loss amount. To do that, use the SUM function.
- Create another column next to the profit/loss amount. Name it Status. Display the status based on the profit/loss amount. If the profit/loss amount is greater than 0, then display "profit," otherwise display "loss."
- Count and display the number of items on the table. To do this, use the COUNT function.
- Count and display the number of unsold items when they exceed 20. Use the COUNT function.
- Count and display the number of items that are profitable. Use the COUNT function.