1. Sort the data in the provided database of stores and regional sales
by units sold, high to low.
Complete the partial Excel table, having pasted the database in cell A1 and sorted by units sold, high to low. For rows with the same number of units sold, enter IDs from smallest to largest from top to bottom.
2.The accompanying table provides data for 15 samples of an important measurement in the manufacturing process for medical syringes. Define range names for each of the samples and then use these range names in formulas to find the average value for each sample.
3. The accompanying retail survey file provides data about customers’ preferences for denim jeans. Use PivotTables and slicers to draw conclusions about how preferences differ by gender and age group and summarize your results in a brief memo.
Construct a table for color preferences.
(Type whole numbers.)
Construct a table for style preferences.
(Type whole numbers.)
Construct a table for purchase influences.
(Type whole numbers.)
4. The accompanying database provides a list of customers, rooms they occupied, arrival and departure dates, number of occupants, and daily rate for a small bed-and-breakfast inn during one month. Room rates include breakfast and are the same for one or two guests; however, any additional guests must pay an extra $22 per person per day for breakfast. Parties staying for seven days or more receive a 10% discount on the room rate as well as any additional breakfast fees. Modify the database to calculate the number of days that each party stayed at the inn and the total revenue for the length of stay.
5. Use the accompanying Credit Risk Data to perform the activities in parts a through d below.
a. Compute the combined checking and savings account balance for each record. Then sort the records by the number of months as a customer of the bank in descending order. Complete the table below with the missing values for the sorted, combined balances.
rom examining the data, does it appear that customers with a longer association with the bank have more assets?
There
does not appear
to be a significant difference because the average amount in combined checking and savings for customers with greater than 24 months (2 years) of association with the bank is
$2,056.89
and the average amount in combined checking and savings for customers with less than or equal to 24 months (2 years) of association with the bank is
$2,258.18.
(Round to the nearest cent as needed.)
b. Apply Pareto analysis to draw conclusions about the combined amount of money in checking and savings accounts. Complete the Pareto analysis table below.
What can be concluded about the combined amount of money in checking and savings accounts?
A.
Less than 25% of the customers have 80% of the total balance.
B.
Less than 80% of the customers have 25% of the total balance.
C.
More than 25% of the customers have 80% of the total balance.
Your answer is correct.
D.
More than 80% of the customers have 25% of the total balance.
c. Use Excel’s filtering capability to extract all records for new-car loans. How many individuals with new-car loans are single, married, and divorced? Complete the table below with the missing values.
d. Use Excel’s filtering capability to extract all records for individuals employed less than 12 months. Can you draw any conclusions about the credit risk associated with these individuals?
Can you draw any conclusions about the credit risk associated with these individuals?
A.
Risk is never high when employment time is low.
B.
Risk is lower if employment time is low.
C.
Risk is never low when employment time is low.
D.
Risk is higher if employment time is low.
Your answer is correct.
E.
There is no association between risk and employment time.
6. The Excel file Payroll Data provides hourly salaries for a group of employees. Create an Excel template that allows the user to select an employee by employee ID, enter the number of regular hours and overtime hours worked, and display a payroll summary with the employee name, gross pay, federal tax, state tax, Social Security, Medicare withholding deductions, and net pay. Assume the federal tax rate is 13%, the state tax rate is 1.145%, Social security withholding is 6.7%, and Medicare withholding is 1.22%. Use a form control to select the employee ID. (Hint: Use the CONCATENATE function to join the first and last name in the template.)
Use a form control to select the employee ID in cell H3. Choose the correct answer.
A.
Use a list or combo box with input range $A$4:$A$17 and cell link $H$2. In cell H3, enter the formula =INDEX($A$4:$A$17,$H$2).
This is the correct answer.
B.
Use a scroll bar with minimum value 1, maximum value 14, and cell link $H$3.
C.
Use a list or combo box with input range $A$4:$A$17 and cell link $H$3.
Your answer is not correct.
D.
Use an option button with cell link $H$3.
7. Enhance the Sales Transactions database to perform a database query that finds the transaction code and amount associated with any customer ID input. Apply your results to customer ID 10186.
8. Convert the provided database of stores and regional sales
to an Excel table to find the requested information.
a. The total number of units sold
b. The total number of units sold in the South region
c. The total number of units sold in December
a. The total number of units sold is
3,171.
(Type a whole number.)
b. The total number of units sold in the South region is
759.
(Type a whole number.)
c. The total number of units sold in December is
1,105.
(Type a whole number.)
9. In the accompanying credit risk data, use Excel’s database functions to find the average amount of savings for each of the situations listed.
a. All new car loans obtained for single females
b. All furniture loans obtained for single individuals who rent
c. All education loans obtained by unskilled workers
d. All used car loans obtained by individuals who are unemployed or employed 12 months or less
e. All car loans (new or used) obtained by males who have been employed at least 36 months
Part 1
a. Find the average amount of savings for all new car loans obtained for single females. Select the correct choice and, if necessary, fill in the answer box within your choice.
A. The average amount of savings for new car loans obtained for single females is
$enter your response here. (Round to the nearest cent as needed.)
B.
No recipients correspond to new car loans obtained for single females.
Your answer is correct.
Part 2
b. Find the average amount of savings for all furniture loans obtained for single individuals who rent. Select the correct choice and, if necessary, fill in the answer box within your choice.
A. The average amount of savings for furniture loans obtained for single individuals who rent is
$480.00.
Your answer is correct.
B.No recipients correspond to furniture loans obtained for single individuals who rent.
Part 3
c. Find the average amount of savings for all education loans obtained by unskilled workers. Select the correct choice and, if necessary, fill in the answer box within your choice.
A. The average amount of savings for education loans obtained by unskilled workers is
$171.67.
Your answer is correct.
B.No recipients correspond to education loans obtained by unskilled workers.
Part 4
d. Find the average amount of savings for all used car loans obtained by individuals who are unemployed or employed 12 months or less. Select the correct choice and, if necessary, fill in the answer box within your choice.
A. The average amount of savings for used car loans obtained by individuals who are unemployed or employed 12 months or less is
$6,643.00.
Your answer is correct
B.No recipients correspond to used car loans obtained by individuals who are unemployed or employed 12 months or less.
Part 5
e. Find the average amount of savings for all car loans (new or used) obtained by males who have been employed at least 36 months. Select the correct choice and, if necessary, fill in the answer box within your choice.
A. The average amount of savings for car loans (new or used) obtained by males who have been employed at least 36 months is
$401.67.
Your answer is correct.B.
No recipients correspond to car loans (new or used) obtained by males who have been employed at least 36 months.
10. Conduct a Pareto analysis of the cost per order data in the provided database of purchase orders.
What conclusion can be reached?
