CS 385 Exam 5

Calculate the Present Value 1. Calculate the periodic interest rate in cell E3. 2. Calculate the total number of payments in cell E4. 3. In cell E2, calculate the Present Value of an auto loan that has a $450.00 payment, 5.25% annual interest rate, and a 4-year repayment period. The function should return a positive number. 4.Apply the Accounting Number Format to cell E2.

1. Click cell E3 and type =B3/B5 (APR/PMTS per year) 2. Click cell E4 and type =B4*B5 (Years*PMTs per year) 3. Click cell E2, and then click the formulas tab. In the formulas group select financial, scroll down to select PV. In the Rate box select E3 (Periodic Rates) Click in the Nper box, type E4 (# of payments) Click in the PMT box and type -B2 (Payment)

Nest an AND Function within an IF Function 1. Go to cell L9 and insert an IF function with a nested AND function to display Due for raise to all managers who earn a salary of less than $80,000 and N/A for anyone who does not fit the criteria. 2. Use the fill handle to copy the function down, populating the rest of the Raise Status column. Select Fill Without Formatting to preserve the existing formatting

1. Click cell F9. On the formulas tab in the Library function group, select Logical and then select IF. In the Logical_Test Box type AND(D9="manager",J9<F$3) In the Value_if_True box type "Due for raise" In the Value_if_False box type "N/A" Click OK. 2. Drag down, select fill wihtout formatting

Use the COUNTIF Function: 1. In cell ​J3​, insert the COUNTIF function to calculate the number of employees who work in Georgia as indicated by the state abbreviation GA. Use mixed cell references in C$2:C$26 for the range. Use cell I3 as the criteria. 2. Copy the COUNTIF function in cell ​J3​ to the range ​J4​:​J6​ to count the number of employees in the other states.

1. With J3 selected, click the formulas tab. Click More functions, point to statistical, scroll to, and then click COUNTIF. Select C2:C26, select the text and press F4 two times. Select I3. 3. Drag down

Insert Formulas in a Loan Amortization Table 1. In cell D8, insert the IPMT function to calculate the current interest for each payment. Ensure that the function returns a positive number and use mixed references where necessary. Copy the IPMT function to the range D8:D55. 2. In cell E8, insert the PPMT function to calculate the current principal for each payment. Ensure that the function returns a positive number and use mixed references where necessary. Copy the PPMT function to the range E9:E55. 3. In cell F8, create a formula to subtract the value in cell E8 from cell B8. Copy the function in cell F8 to the range F9:F55. Select the range C8:E56 and then use the SUM function to enter totals in the range C56:E56 at the same time.

1. Click cell D8, Type =IMPT( and then click the insert function button to the left of the formula bar. In the Rate box type E$3 (Periodic Rate) In the Per box type A8 (payment number) In the Nper box type E$4 (# of payments) In the Pv box type -E$2 (Loan) 2. Click cell E8, type =PPMT( click insert function on the formula bar In the Rate box type E$3 (Periodic Rate) In the Per box type A8 (payment number) In the Nper box type E$4 (# of payments) In the Pv box type -E$2 (Loan) 3. =B8-E8 4. Select range, and click autosum in formulas

Calculate the Cumulative Interest with the CUMIPMT Function 1. In cell H8, insert the CUMIPMT function to calculate the cumulative interest for the first payment. Convert the CUMIPMT results to a positive number. 2. Copy the function in cell H8 down to complete the CUMIPMT column.

1. Click cell H8, in the formulas tab select financial and then CUMIPMT. In the Rate box type E$3 (Periodic Rate) In the Nper box type E$4 (# of payments) In the PV box type E$2 (Loan) In the Start_Period box type A$8 (payment number) In the End_Period box type A8 In the Type box type 0 Put a negative sign in front of the function

Use the WEEKDAY Function 1. Insert the WEEKDAY function in cell ​H9​ to return a number representing ​the day of the week​. Use cell ​E9​ as the Serial_number argument and ​2​​ as the Return_type argument. 2. Insert a VLOOKUP function in cell ​I9​ to identify the day of the week for the results of the WEEKDAY function in cell ​H9​. Use the range ​H$2:I$6​ as the table array. 3. Select the range ​H9:I9​, the range containing the WEEKDAY and VLOOKUP functions and use Auto Fill to copy the functions to the range H10:I33. Select Fill Without Formatting to preserve the formatting, including borders.

1. Click cell H9. On the formulas tab in the Function library group, select DATE&TIME, then select WEEKDAY. In the Serial_number box select cell E9. In the return_type box type 2. Click OK. 2. Click cell I9. On the formulas tab in the Function library group, select lookup&reference, then select VLOOKUP. In the Lookup_value box, click cell H9. Press tab to select the Table_Array box and select range H2:I6. (make sure it reads H$2:I$6). Insert 2 in the column box. 3. Select the range H9:I9. Double click the fill handle in I9. Click the Autofill option. Click fill with out formatting.

Calculate Cumulative Principal Paid wth the CUMPRINC Function 1. In cell I8, insert the CUMPRINC function to calculate the cumulative principal for the first payment of the loan. Convert the CUMPRINC results to a positive number. Copy the function in cell I8 down to complete the CUMPRINC column.

1. Click cell I8, in the formulas tab click financial and then CUMPRINC. In the Rate box type E$3 (Periodic Rate) In the Nper box type E$4 (# of payments) In the PV box type E$2 (Loan) In the Start_Period box type A$8 (Payment number) In the End_Period box type A8 In the type box type 0 Make sure to put a negative sign infront of function

Use the IFS Function 1. Insert an IFS function in cell K9 to compare the hire date in cell E9 to see if it is before the date 1/1/2010 in cell K2. If true, multiply the salary in cell J9 by the 8% bonus rate in cell L2. In the Logical_test2 box, compare the hire date to see if it is before the date 1/1/2015 in cell K3. If true, multiply the salary in cell J9 by the 4% bonus rate in cell L3. In the Logical_test3 box, compare the hire date in cell E9 to see if it is before the date 1/1/2020. If true, multiply the salary in cell J9 by the 2% bonus rate in cell L4. Type TRUE in the Logial_test4 box and 0 in the Value_if_true4 box. Use the mixed references to the references in column K and L. The function should be =IFS(E9<K$2,J9*L$2,E9<K$3,J9*L$3,E9<K$4,J9*L$4,TRUE,0). 2. Copy the function in cell K9 and paste the function in the range K10:K33 using the Paste Formulas option, and then press ESC.

1. Click cell K9. On the Formulas tab in the Function library group, click Logical, and select IFS. In the Logical_Test1 box type E9<K$2 then press tab. In the Value_if_True1 box, type J9*L$2. Press Tab. In the Logical_Test2 box type E9<K$3. Press Tab In the Value_if_true2 Box type J9*L$3. Press tab. In the Logical_Test3 box type E9<K$4. Press tab. In the Value_if_True3 box type J9*K$4. Press tab In the Logical_Test4 box type TRUE. Press Tab In the Value_if_True4 box type 0 Click ok. 3. Copy, select K10:K33, Paste using formula

Use the SUMIF Function 1. In cell K3, insert the SUMIF function to calculate the total salaries of employees who work in Georgia (GA). Use mixed cell references in C$2:C$26 for the range. Use cell I3 as the criteria. Use mixed cell references in F$2:F$26 for the sum_range. 2. Copy the SUMIF function in cell K3 to the range K4:K6 to calculate the total salaries for employees in the other states.

1. Click on Math and Trig in formula group, use C$2:C$26 AS RANGE. Cell I3 as Criteria. Sum_Range is F$2:F$26

Use the AVERAGEIF Function 1. In cell J9, insert the AVERAGEIF function to calculate the average salary for Account Reps Use mixed references for D$2:D$26 and F$2:F$26. Use cell H9 for the criteria. Copy the AVERAGEIF function in cell J9 down through cell J10 to calculate the average Manager salary.

1. Click on more functions, statistical, averageif.

Use the SWITCH Function 1. Insert the SWITCH function in cell C9 that looks up the expression in cell B9 and switches a city for a region. The function should be =SWITCH(B9,"Atlanta",C$2,"Boston",C$4,"Chicago",C$3,"Cleveland",C$3). 2. Copy the function in cell C9 and paste the function in the range C10:C33 using the Paste Formulas option. Press ESC.

1. Select Cell C9. In the formulas tab, in the library function group, select LOGICAL, scroll to select SWITCH. In the Expression box type B9. In the Value_1 type Atlanta. In the Result_1 Box type C$2. In the default_or_value2 box type Boston, in the Result2 box type C$4. In the default or value3 box type Chicago. In the result3 box type C$3. Continue pattern. click OK 2. Click cell C9 and press copy in the clipboard group. Select range C10:C33 and click the paste arrow. Select formulas. Press escape.

Insert a Map Select the non-adjacent ranges I2:I6 and K2:K6 in the 2-Stats Map worksheet and insert a Filled Map. Type Payroll by State as the chart title and click the chart area. Cut the selected map and paste it in cell M1.

1. Select I2:I6 and K2:K6 at the same time. Click insert. In the chart group select MAP and then click filled map.

Use the INDEX Function: 1. Go to cell F1 and insert a function that will look up the employee ID in cell B1 and return that employee's current Title from column D. 2. Test the newly created search box using employee number 4070.

1. Select cell F1. On the formulas tab in the Function library group, clikc Lookup&Reference, click INDEX. In the Select Arguments dialog box, click ok. In the Array box type A7:I26 In the Row_num box type MATCH(B1,A7:A26,0) Click the Column_num and type 4 2. Click cell B1 and then type 4070 and press enter

Use the YEAR and YEARFRAC Functions 1. Insert the YEAR function in cell ​F9​ in the ​1-Date Logic​ worksheet. Use cell ​E9​ for the Serial_number argument. 2. Insert the YEARFRAC function in cell ​G9​ to enter =YEARFRAC(E9,F2) to calculate difference between an employee's hire date and the date in cell F2. 3. Change the reference of ​F2​ in the YEARFRAC function to the mixed reference F$2. 4. Select the range ​F9:G9​, the range containing the YEAR and YEARFRAC functions and use Auto Fill to copy the functions to the range F10:G33. Select Fill Without Formatting to preserve the formatting, including borders.

1. Select cell F9 and click the formulas tab. Click Date&Time in the function library group. Select YEAR. Select cell E9 to insert it into the Serial_number argument 2. Click cell G9 in the 1-Date Logic worksheet. On the formulas tab in the Function Library group, click the Date& Time. Select YEARFRAC. In the start date box click cell E9. In the End_Date box select cell F2. Click OK. 3. Edit the formula in the formula bar 4. Select range F9:G9. Double click the fill handle in cell G9. Click the AutoFill Options button. Click fill without formatting.

Use the COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS Functions 1. In cell J13, insert the COUNTIFS function that counts the number of Account Reps hired before 1/1/2015. Use cell references in the function. 2. In cell J14, insert the SUMIFS function to calculate the total salaries for Account Reps hired before 1/1/2015. Use cell references in the function. 3. In cell J15, insert the AVERAGEIFS function that calculates the average salary for Account Reps hired before 1/1/2015. Use cell references in the function. 4. In cell J16, insert the MAXIFS function that calculates the highest salary for Account Reps hired before 1/1/2015. Use cell references in the function. 5. In cell ​J17​, insert the ​MINIFS​ function that calculates the lowest salary for Account Reps hired before 1/1/2015. Use cell references in the fu

1. Select cell J13. oN THE FORMULAS TAB CHOOSE MORE OPTIONS, Point to statistical, choose COUNTIFS. In the criteria_range box select D2:D26. In criteria1 choose H9. In the criteria_range2 box select E2:E26. in the criteria2 box type <1/1/2015. click ok 2. Select SUMIFS. Type F2:F26 in the sum_range1 box(Salaries). In criteria_range 1 box type D2:D26 In criteria 1 box type H9 In criteria_range2 type E2:E26 In criteria2 type <1/1/2015