AutoSave On Copy of HW1.Tiffany.Hou Saved Search File Home Insert Page Layout Formulas Data Review View...

Transcribed Image Text:

AutoSave On Copy of HW1.Tiffany.Hou Saved Search File Home Insert Page Layout Formulas Data Review View Automate Help X Cut Calibri 11 - ' Wrap Text Copy Paste BIU A Merge & Center Accounting $% 900-00 Accent4 Accent5 Conditional Format as Comma Comma [0] Format Painter Formatting Table Undo Clipboard Font Alignment Number ly Styles 5 Xfx =IF($G6 3000,$G6 $B$3,($B$2+($C$3*($B$3-$B$2))+($C$4*($G6-$B$3))))) B C D E F fully Paid $3,000.00 100.00% half paid beyond 3000 $5,000.00 50.00% quarter paid beyond 5000 25.00% M N P Write a function in cell 16, to determine the reimbursement amount Expenses for any given expense level. Copy the Formula down the Column. Reimbursed - ABC corporation periodically resets it's travel reiumbursement policy for conference travel. The current policy is given below. You must develop the worksheet in such a way that if in the future the policy changes, cells B2:C4 will be updated and the worksheet should still work, without changes to formulae. 1) If Total expense is less than or equal to 3000, the office will cover the full cost. 2) If Total expense is more than 3000 but less than or equal to 5000, the office will cover full expenses up to 3000 and 50% beyond 3000. 3) If Total expense is more than 5000, the office wil cover full expenses up to 3000 and 50% between 3000 and 5000 and 25% beyond 5000. Hint: You need to make judicious use of Relative and absolute referencing. Name Registration Plane Tickets Taxi Fare Car Rental Meals Total John Close John Watkins $ Allison Charles $ Margie Lyons George Winkler $ Jean Lawson $ Harold Graening $ 400.00 $ Julie Baker $ 300.00 $ Carol Sundry 825.00 $ Thomas Bernard $ 325.00 $ Sandi McLean $ 800.00 $ Linda Janes $ 200.00 Christine Miller $ 1,050.00 $ David Klein $ Robert Karns $ Dawn Elliot $ 825.00 $ 125.00 $ 675.00 $ $ 923.00 $ 2,006.00 $652.00 $ 543.00 $ 3,029.00 $ 7,153.00 $ 600.00 $ 1,540.00 $300.00 $ 450.00 $ 2,000.00 $4,890.00 $ 3,945.00 540.00 $ 1,200.00 $250.00 $375.00 $ 1,500.00 $ 3,865.00 $3,432.50 600.00 $ 1,252.00 $220.00 $ 300.00 $ 1,105.00 $ 3,477.00 $3,238.50 460.00 $ 1,005.00 $326.00 $ 200.00 $ 954.00 $ 2,945.00 $ 2,945.00 800.00 $ 2,890.00 $820.00 $ 560.00 $ 2,500.00 $7,570.00 $ 800.00 $130.00 $ 200.00 $ 600.00 $2,130.00 $ 2,130.00 450.00 $112.00 $ 100.00 $ 500.00 $1,462.00 $ 1,462.00 1,600.00 $430.00 $ 255.00 $ 3,250.00 $6,360.00 $ 642.00 $100.00 $230.00 $ 860.00 $ 2,157.00 $ 2,157.00 1,850.00 $512.00 $610.00 $ 2,950.00 $6,722.00 $ $0.00 $0.00 $340.00 $ 250.00 $ 790.00 $ 790.00 3,560.00 $350.00 $ 645.00 $ 3,725.00 $9,330.00 $ 1,643.00 $400.00 $575.00 $ 3,200.00 $6,643.00 $ 256.00 $20.00 $ 200.00 $ 250.00 $ 851.00 $ 851.00 1,200.00 $200.00 $575.00 $ 1,750.00 $4,400.00 $ 3,700.00 AutoSave On Copy of HW1.Tiffany.Hou Saved Search File Home Insert Page Layout Formulas Data Review View Automate Help X Cut Calibri 11 - ' Wrap Text Copy Paste BIU A Merge & Center Accounting $% 900-00 Accent4 Accent5 Conditional Format as Comma Comma [0] Format Painter Formatting Table Undo Clipboard Font Alignment Number ly Styles 5 Xfx =IF($G6 3000,$G6 $B$3,($B$2+($C$3*($B$3-$B$2))+($C$4*($G6-$B$3))))) B C D E F fully Paid $3,000.00 100.00% half paid beyond 3000 $5,000.00 50.00% quarter paid beyond 5000 25.00% M N P Write a function in cell 16, to determine the reimbursement amount Expenses for any given expense level. Copy the Formula down the Column. Reimbursed - ABC corporation periodically resets it's travel reiumbursement policy for conference travel. The current policy is given below. You must develop the worksheet in such a way that if in the future the policy changes, cells B2:C4 will be updated and the worksheet should still work, without changes to formulae. 1) If Total expense is less than or equal to 3000, the office will cover the full cost. 2) If Total expense is more than 3000 but less than or equal to 5000, the office will cover full expenses up to 3000 and 50% beyond 3000. 3) If Total expense is more than 5000, the office wil cover full expenses up to 3000 and 50% between 3000 and 5000 and 25% beyond 5000. Hint: You need to make judicious use of Relative and absolute referencing. Name Registration Plane Tickets Taxi Fare Car Rental Meals Total John Close John Watkins $ Allison Charles $ Margie Lyons George Winkler $ Jean Lawson $ Harold Graening $ 400.00 $ Julie Baker $ 300.00 $ Carol Sundry 825.00 $ Thomas Bernard $ 325.00 $ Sandi McLean $ 800.00 $ Linda Janes $ 200.00 Christine Miller $ 1,050.00 $ David Klein $ Robert Karns $ Dawn Elliot $ 825.00 $ 125.00 $ 675.00 $ $ 923.00 $ 2,006.00 $652.00 $ 543.00 $ 3,029.00 $ 7,153.00 $ 600.00 $ 1,540.00 $300.00 $ 450.00 $ 2,000.00 $4,890.00 $ 3,945.00 540.00 $ 1,200.00 $250.00 $375.00 $ 1,500.00 $ 3,865.00 $3,432.50 600.00 $ 1,252.00 $220.00 $ 300.00 $ 1,105.00 $ 3,477.00 $3,238.50 460.00 $ 1,005.00 $326.00 $ 200.00 $ 954.00 $ 2,945.00 $ 2,945.00 800.00 $ 2,890.00 $820.00 $ 560.00 $ 2,500.00 $7,570.00 $ 800.00 $130.00 $ 200.00 $ 600.00 $2,130.00 $ 2,130.00 450.00 $112.00 $ 100.00 $ 500.00 $1,462.00 $ 1,462.00 1,600.00 $430.00 $ 255.00 $ 3,250.00 $6,360.00 $ 642.00 $100.00 $230.00 $ 860.00 $ 2,157.00 $ 2,157.00 1,850.00 $512.00 $610.00 $ 2,950.00 $6,722.00 $ $0.00 $0.00 $340.00 $ 250.00 $ 790.00 $ 790.00 3,560.00 $350.00 $ 645.00 $ 3,725.00 $9,330.00 $ 1,643.00 $400.00 $575.00 $ 3,200.00 $6,643.00 $ 256.00 $20.00 $ 200.00 $ 250.00 $ 851.00 $ 851.00 1,200.00 $200.00 $575.00 $ 1,750.00 $4,400.00 $ 3,700.00