BUS 140 - Functions Practice

Practice Exercise #1 Input Area Penalty Percentage 3% Output Area Loan Loan Amount Loan Status Penalty Amount ANSWER KEY Loan #1 2500 Overdue $ 75.00 Loan #2 3600 Pending $ - Loan #3 4570 Paid $ - Loan #4 10260 Overdue $ 307.80 Loan #5 12350 Overdue $ 370.50 Loan #6 6800 Paid $ - Loan #7 7680 Pending $ - Loan #8 8400 Overdue $ 252.00 Loan #9 2600 Pending $ -

Steps 1. Use "Merge and Center" to center the text in cell A1 across the r 2. Apply Heading 1 style to the contents of cell A1. 3. Bold and center the column headings in the range A9:D9 4. Calculate the Penalty Amount for each loan. If the Loan Status is then the penalty amount is calculated as Loan Amount * Penalty Pe otherwise the penalty amount is 0. 5. Apply Accounting Format to all monetary values.

Practice Exercise #2 Input Area Threshold Result Total Sales Less than $ 1,000 Below Total Sales Greater than or equal to $ 1,000 Above Output Area Sales Rep Total Sales Meeting Goals? ANSWER KEY Rep #1 $ 500 Below Rep #2 $ 1,200 Above Rep #3 $ 850 Below Rep #4 $ 3,200 Above Rep #5 $ 2,000 Above Rep #6 $ 2,600 Above Rep #7 $ 720 Below Rep #8 $ 1,500 Above Rep #9 $ 1,860 Above Instr Your tota The $1,0 In th each

ructions r task is to categorize your sales reps as either above or below target, based on their al sales results. goal for each sales rep is between $1,000 or more in sales; if they are less than 000 they are below target, and if they are at $1,000 or above are above target. he "Meeting Goals?" column, use a formula that will return "Below", or "Above" for h sales rep, based on their Total Sales.

Practice Exercise #3a Input Area Unit Sales Price 15 Bonus 500 Sales Goals Revenue 10000 New Customers 5 Output Area Sales Rep Sales Volume Total Sales New Customers Bonus Rep #1 500 4 Rep #2 650 6 Rep #3 700 5 Rep #4 675 3 Rep #5 450 7 Rep #6 690 6 Rep #7 720 4 Rep #8 680 6 Rep #9 560 7 Practice Exercise #3b Input Area Unit Sales Price 15 Bonus 500 Sales Goals Revenue 10000 New Customers 5 Output Area Sales Rep Sales Volume Total Sales New Customers Bonus Rep #1 500 4 Rep #2 650 6 Rep #3 700 5 Rep #4 580 3 Rep #5 450 7 Rep #6 690 3

Rep #7 720 4 Rep #8 570 4 Rep #9 560 5

$ 500 $ - $ -

ould return the do not. es. o the Revenue uld return the o not. s.

ANSWER KEY Average Honour Roll? Average Honour Roll? 71% 76% 74% 91% Honours 76% 88% Honours 87% Honours 76% 70% 84% Honours Instructions Complete the works cells with Blue fill. Y column should retu qualifies for the hon they do not. Format all grades as decimal places.

sheet by adding formulas to Your formula in the Honour Roll urn "Honours" if the student nour roll, and be left blank if s Percent style, with zero

ANSWER KEY Delivery Amount Total Due Delivery Amount Total Due $ - $ 45.65 $ 100.00 $ 402.56 $ 25.00 $ 92.90 $ 50.00 $ 214.76 $ 100.00 $ 440.27 Instructions Use the VLOOKUP Function to determine the delivery amount for each delivery, based on the Delivery Cost Schedule provided. Then, calculate the total due for each order as the purchase amount plus the delivery amount.

Pr 123 2 Standard 124 1 Silver 125 4 Platinum 126 5 Gold 127 3 Gold 128 2 Standard 129 4 Silver 130 3 Platinum 131 5 Gold 132 1 Silver 133 2 Standard 134 4 Gold 135 2 Silver 136 1 Standard 137 3 Platinum 138 5 Gold 139 2 Standard 140 1 Platinum Membership Type Annual Fee Standard $ 50.00 Silver $ 100.00 Gold $ 200.00 Platinum $ 500.00 Member Number Membership Length (Years) Membership Type

ANSWE $ 50.00 $ 100.00 $ 100.00 $ 100.00 $ 500.00 $ 2,000.00 $ 200.00 $ 1,000.00 $ 200.00 $ 600.00 $ 50.00 $ 100.00 $ 100.00 $ 400.00 $ 500.00 $ 1,500.00 $ 200.00 $ 1,000.00 $ 100.00 $ 100.00 $ 50.00 $ 100.00 $ 200.00 $ 800.00 $ 100.00 $ 200.00 $ 50.00 $ 50.00 $ 500.00 $ 1,500.00 $ 200.00 $ 1,000.00 $ 50.00 $ 100.00 $ 500.00 $ 500.00 Annual Fee Total Fee round scription service is selling memberships at 4 levels: dard, Silver, Gold, and Platinum, with lengths from 1 to 5 goal is to use the provided information to determine the al Fee, Total Fee, APR and Monthly Payment for each ber. ctions the range B25:D28 as Fees. he VLOOKUP Function to determine the annual fee for each ber, based on their membership type. calculate their total fee, based on their annual fee and bership length. LOOKUP again to determine each member's APR, based on membership type. y, use PMT to calculate the monthly fee for each member, the total fee, membership length, and APR as inputs to the on.

ER KEY APR 3.50% $ 4.32 3.00% $ 8.47 2.50% $ 43.83 2.80% $ 17.88 2.80% $ 17.40 3.50% $ 4.32 3.00% $ 8.85 2.50% $ 43.29 2.80% $ 17.88 3.00% $ 8.47 3.50% $ 4.32 2.80% $ 17.64 3.00% $ 8.60 3.50% $ 4.25 2.50% $ 43.29 2.80% $ 17.88 3.50% $ 4.32 2.50% $ 42.23 Monthly Payment