Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ITIS 1P97: Data Analysis and Business Modeling

Lab Exercise 2: Analytics on Spreadsheets

Problems and exercises (Chapters 2 of the textbook). The data files are available on Sakai for download.

1. (Problem 2.1) The Excel file Science and Engineering Jobs shows the number of jobs in thousands in the year 2000 and projections for 2010 from a government study. Use the Excel file to compute the projected increase from the 2000 baseline and also the percentage increase for each occupational category.

2. (Problem 2.2) The Excel file Store and Regional Sales Database provides sales data for computers and peripherals showing the store identification number, sales region, item number, item description, unit price, units sold, and month when the sales were made during the fourth quarter of last year. Modify the spreadsheet to calculate the total sales revenue for each of the eight stores as well as each of the three sales regions.

3. (Problem 2.3) The Excel file President’s Inn Guest 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 are the same for one or two guests; however, additional guests must pay an additional $20 per person per day for meals. Guests staying for seven days or more receive a 10% discount. Modify the spreadsheet to calculate the number of days that each party stayed at the inn and the total revenue for the length of stay.

4. (Problem 2.4) The worksheet Base Data in the Excel file Credit Risk Data 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 credit-risk classification by the bank.

a. Use the COUNTIF function to determine (1) how many customers applied for new-car, used-car, business, education, small-appliance, and furniture loans and (2) the number of customers with checking account balances less than $500. 

b. Modify the spreadsheet using IF functions to include new columns, classifying the checking and savings account balances as low if the balance is less than $250, medium if between $250 but less than $2000, and high otherwise.

5. (Problem 2.9) The following exercises use the Purchase Orders database. Use MATCH and/or INDEX functions to find the following:

a. The row numbers corresponding to the first and last instance of item number 1369 in column C (be sure column C is sorted by order number).

b. The order cost associated with the first instance of item 1369 that you identified in part (a).

c. The total cost of all orders for item 1369. Use the answers to parts (a) and (b) along with the SUM function to do this. In other words, you should use the appropriate INDEX and MATCH functions within the SUM function to find the answer. Validate your results by applying the SUM function directly to the data in column G.