Week 2 Excel Problems
Complete the afterward botheration in your textbook:
From Chapter 2: Problems: 4, 9, and 11
From Chapter 3 Problems: 14, 16, 25, 33, and 40
All assignment should be submitted in Excel with one (1) botheration per tab in a distinct workbook. Formulas should be acclimated as against to alfresco or chiral calculations. Use of Excel add-ins is encouraged.
Data files can be downloaded from http://wps.prenhall.com/bp_evans_bus_2/ (Links to an alien site.)
4.) "The worksheet Base Abstracts in the Excel book Acclaim Accident Abstracts provides advice about 425 coffer barter who had activated for loans. The abstracts accommodate the purpose of the loan, blockage and accumulation annual balances, cardinal of months as a chump of the bank, months employed, gender, conjugal status, age, apartment cachet and cardinal of years at accepted residence, job type, and credit-risk allocation by the bank.55 Based on Efraim Turban, Ranesh Sharda, Dursun Delen, and David King, Business Intelligence: A Managerial Approach, 2nd ed. (Upper Saddle River NJ: Prentice Hall, 2011). Use the COUNTIF action to determine
(a) how abounding barter activated for new-car, used-car, business, education, small-appliance, and appliance loans and
(b) the cardinal of barter with blockage annual balances beneath than $500. Modify the spreadsheet application IF functions to accommodate new columns, classifying the blockage and accumulation annual balances as low if the antithesis is beneath than $250, average if amid $250 but beneath than $2000, and aerial otherwise."
9.) "The afterward contest use the Purchase Orders database. Use MATCH and/or INDEX functions to acquisition the following: The row numbers agnate to the aboriginal and aftermost instance of account cardinal 1369 in cavalcade C (be abiding cavalcade C is sorted by adjustment number). The adjustment bulk associated with the aboriginal instance of account 1369 that you articular in allotment (a). The absolute bulk of all orders for account 1369.Use the answers to genitalia (a) and (b) forth with the SUM action to do this. In alternative words, you should use the adapted INDEX and MATCH functions aural the SUM action to acquisition the answer. Validate your after-effects by applying the SUM action anon to the abstracts in cavalcade G."
11.)"Suppose that a aggregation offers abundance discounts. If up to 1000 units are purchased, the assemblage bulk is $10; if added than 1000 and up to 5000 units are purchased, the assemblage bulk is $9; and if added than 5000 units are purchased, the assemblage bulk is $7.50. Develop a spreadsheet application the VLOOKUP action to acquisition the assemblage bulk associated with any adjustment abundance and compute the absolute bulk of the order."
14.) "Convert the Purchase Orders database to an Excel table. Use the techniques declared in Archetype 3.11 to find:
a. the absolute bulk of all orders
b. the absolute abundance of airframe accouterments purchased
c. the absolute bulk of all orders placed with Manley Valve."
"Suppose that in the Acclaim Accident Abstracts table, we ambition to account the absolute bulk of accumulation in cavalcade C. We could, of course, artlessly use the action SUM(C4:C428). However, with a table, we could use the blueprint = SUM(Table1[Savings]). The table name, Table1, can be begin (and changed) in the Properties accumulation of the Table Tools Design tab. Note that Accumulation is the name of the attack in cavalcade C. One of the advantages of accomplishing this is that if we add new annal to the table, the adding will be adapted automatically, and we don’t accept to change the ambit in the blueprint or get a amiss aftereffect if we balloon to. As addition example, we could acquisition the cardinal of home owners application the action = COUNTIF(Table1[Housing], “Own”). "
16. "Open the Excel book Store and Regional Sales database.
a.) Array the abstracts by units sold, aerial to low
b.) Array the units awash application an figure set, area blooming corresponds to aerial sales levels, chicken to average sales, and red to low sales. The array should appearance all the blooming icons first, followed by yellow, and again red. "
25. "Construct abundance distributions and histograms for the after abstracts in the Excel book Cell Phone Survey. Also, compute the about frequencies and accumulative about frequencies."
33. "Use PivotTables to assemble a cross-tabulation for the purpose of the accommodation and acclaim accident in the Excel book Acclaim Accident Data. Illustrate the after-effects on a PivotChart."
40. "Create advantageous dashboards for anniversary of the afterward databases. Use adapted archive and layouts (for example, Explain why you chose the elements of the dashboards and how a administrator ability use them.
a.) President’s Inn
b.) Restaurant Sales
c.) Store and Regional Sales
d.) Peoples Choice Coffer "
Order a unique copy of this paper