DS $680$

Assignment $4$

Points: $100$

$1.$ The Excel file Firm Data shows the prices charged and different product

sizes. Prepare a worksheet using VLOOKUP function that will compute the

invoice to be sent to a consumer when any product type, size and order

quantity are entered.

$2.$ In the Excel file, Banking Data, apply the following data visualization tools:

a$.$ Use data bars to visualize the relative values of Median Home Value.

b$.$ Use color scales to visualize the relative values of Median Household

Wealth.

c$.$ Use an icon set to show high, medium and low bank balances where

high is above $$30,000,$ low is below $$10,000$ and medium is anywhere in

between.

$3.$ In Purchase Orders database, conduct a Pareto Analysis of the Cost per

order data. What conclusions can you reach?

$4.$ In the Sales Transaction database, use Excel$$s filtering capability to extract

all orders that used PayPal, all orders under $$100$ and all orders that were

over $$100$ and used a credit card.

$5.$ A manager needs to identify some information from the Purchase Order

Excel file but has only order number. Modify the Excel file to use VLOOKUP

function to find the item description and cost per order for the following

order numbers: Aug$11008,$ Sep$11023$ and Oct$11020$

there is $4$ files banking data, firm data, purchase order and sales transaction give me please ready excel sheet file