Question

The Science and Engineering Jobs Excel file shows the number of jobs in thousands in the year 2000 and 2010 with projections for 2020 from a government study. Use the Excel file to compute the following for each occupational category:

a. The numeric change in 2010 compared to the 2000 baseline (HINT: Change = New Number - Original Number)

b. The percentage change in 2010 compared to the 2000 baseline (HINT: % Change = Change ÷ Original Number). Format as a percent with no decimal place

c. The numeric change in 2020 compared to the 2010 baseline (HINT: Change = New Number - Original Number)

d. The percentage change in 2020 compared to the 2010 baseline (HINT: % Change = Change ÷ Original Number). Format as a percent with no decimal place e. In the “Insight from Data Analysis section, briefly describe the trends that you see in the data over 20 years. Any factors that could have impacted the changes? What occupations have had significant changes?

2. The Credit Risk Data Excel file provides information about 425 bank customers who had applied for loans. The data include the purpose of the loan, checking and savings account balances, number of months as a customer of the bank, months employed, gender marital status, age housing status and number of years at current residence, job type, and creditrisk classification by the bank.

a. In the “Checking Class” column, use an IF function that will classify the checking account balance: 1. LOW – if the balance is less than $1,000

2. AVERAGE – if the balance is equal to or greater than $1,000

b. In the “Savings Class” column, use an IF function that will classify the savings account balance: 1. LOW – if the balance is less than $5,000

2. AVERAGE – if the balance is equal to or greater than $5,000 Page 2 of 2

c. Use the COUNTIF function to determine how many customers: i. Applied for New-car loans ii. Applied for Used-car loans iii. Applied for Business loans iv. Applied for Education loans v. Applied for Small Appliance loans vi. Applied for Furniture loans vii. Have a checking account balance less than $1,000 viii. Have a savings account balance greater than $5,000

d. In the “Insight from Data Analysis” section, identify opportunities for the bank (i.e. which types of loans to offer, which type of customer to market to, etc.)

3. A manager has a Purchase Orders Excel file which lists detailed information on purchase orders over the four months. Modify the Excel file as follows:

a. The manager needs information on orders but only has the order numbers. Use the VLOOKUP function to find the item description and cost per order for the following order numbers: i. Aug110008 ii. Sep11023 iii. Oct11020 iv. Nov11006

b. The manager needs to determine any patterns that may exist in the amount of time for delivery of orders. i. Calculate the delivery time for each order. ii. Identify the Maximum, Minimum, and Average delivery length iii. Use conditional formatting in the “Delivery Time” column to highlight the maximum and minimum delivery times – Red for Maximum and Green for Minimum. iv. In the “Insight from Data Analysis” section, detail any patterns that you see in the orders that took the longest or the least amount of time to deliver.

Answer 1

(1)

Open Science and Engineering Jobs.xlxs

Enter the formula for calculating the projected increase in column D.

Enter the formula for calculating the projected increase in column E.

The calculated projected increase and percent increase are shown.