CASE STUDY Excel FORMULAS DATA REVIEW VIEW Carole Sanford X GS FILE HOME INSERT PAGE LAYOUT Cut Arial 12 LE Copy Paste Format Painter BIU 3 A Clipboard Font x A Der Percent 4 AutoSum 7 Wrap Text Merge Center Currency $ Fill 3 , 8 98 I D Percent 3 Conditional Format as Normal Formatting Table Styles Bad Insert Delete Format Clear Sort Find Filter Select Editing Alignment Number Cells F15 I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG LOCKBOX ANALYSIS OF ACCOUNTS RECEIVABLE INSTRUCTIONS FOR EVALUATION OF A LOCKBOX SYSTEM Global Manufacturing is considering using a lockbox system to speed up collections of accounts receivable from its customers in 2020 They have two Lockbox proposals to consider one from U S Bank, and one from Well Fargo Bank The information you will need to evaluate each bank's proposal is presented on this worksheet SAMPLE PRACTICE PROBLEM This problem is similar to Problems 27 11 and 27 12 HELP Refer back to your own spreadsheet for Problems 27 11b and 27 12 Also see the file named 05 SAMPLE COMPANY LOCKBOX CALCULATION ANALYSIS pdf provided to help you setup your spreadsheet for these LOCKBOX calculations Wells Fargo 3 252 5 470 DATA INPUTS U S Bank Rate or money market intestants 3 253 Average Customer payment 5 470 Average number of payments per day 78 Augal fee 9,500 Cost per transaction $0 64 Redaction is collection time 21 $31,500 $0 41 NOTE Cells shaded In YELLOW are the calls you must provide the calculations or analyals The questions that the CEO needs you to anster are as follors U S Bank Wells Fargo fal What is the Net Present Value (NPY) of cach of the act lockbor system proposals fick dig at fized staras (Road to the nearest whole dollar) (b) Hor many customers are needed, on average each day to make each of the lockbo1 susteas break eten (Round to the nearest hole customer (c) Ylich is the Preferred Lockbor System for Global Hanufacturing Iac (U S Bank or Wells Fargo) (d) HOT much of a reduction in accounts receivable should Global expect from the Preferred Lockbor Syates if implemented in 2020 (Rond to the nearest whole dollar) TO CALCULATE THE REDUCTION IN ACCOUNTS RECEIVABLES USE THE REDUCTION IN COLLECTION TIME FOR THE SELECTED LOCKBOX SYSTEM FROM PART C USING THIS FORMULA Reduction in collection time x Average customer payment x Average number of payments per day FINANCIAL DATA COMMON SIZE 2018 COMMON SIZE 2019 CHANGE RATIOS 2018 RATIOS 2019 EVALUATION OF FINDINGS LOCKBOX E READY 8 0 57 'o Type here to search di e 9 U wi xi O 9 1 4 4 2020 Wells Fargo 3 25 $5,470 DATA INPUTS U S Bank 3 25 $5,470 78 $9,500 $0 64 2 Rate on money market investments Average Customer payment Average number of payments per day Annual fee Cost per transaction Reduction in collection time 78 $31,500 $0 41 NOTE Cells shaded in YELLOW are the cells you must provide the calculations or analysis The questions that the CEO needs you to answer are as follows U S Bank Wells Fargo (a) What is the Net Present Value (NPV) of each of the new lockbox system proposals (including the annual fixed charge) (Round to the nearest whole dollar) (b) How many customers are needed, on average each day, to make each of the lockbox systems break even (Round to the nearest whole customer) (e) Which is the Preferred Lockbox System for Global Manufacturing, Inc 7 (U S Bankor Wells Fargo) (d) How much of a reduction in accounts receivable should Global expect from the Preferred Lockbox System If Implemented in 20207 (Round to the nearest whole dollar) LOCKBOX ANALYSIS OF ACCOUNTS RECEIVABLE INSTRUCTIONS FOR EVALUATION OF A LOCKBOX SYSTEM Global Manufacturing is considering using a lockbox system to speed up collections of accounts receivable from its customers in 2020 They have two Lockbox proposals to consider one from U S Bank, and one from Well Fargo Bank The information you will need to evaluate each bank's proposal is presented on this worksheet SAMPLE PRACTICE PROBLEM This problem is similar to Problems 27 11b and 27 12 HELP Refer back to your own spreadsheet for Problems 27 11b and 27 12 Also see the file named 05 SAMPLE COMPANY LOCKBOX CALCULATION ANALYSIS pdf provided to help you setup your spreadsheet for these LOCKBOX calculations DATA INPUTS U S Bank Wells Fargo Rate on money market investments 3 25 3 25 Average Customer payment $5,470 $5,470 Average number of payments per day 7 8 78 Annual fee $9,500 $31,500 Cost per transaction $0 64 $0 41 Reduction in collection time 214 NOTE Cells shaded in YELLOW are the cells you must provide the calculations or analysis The questions that the CEO needs you to answer are as follows Wells Fargo (a) What is the Net Present Value (NPV) of each of the new lockbox system proposals (including the annual fixed charge) (Round to the nearest whole dollar) (b) How many customers are needed, on average each day, to make each of the lockbox systems break even (Round to the nearest whole customer) (c) Which is the Preferred Lockbox System for Global Manufacturing, Inc (U S Bank of Wells Fargo) (d) How much of a reduction in accounts receivable should Global expect from the Preferred Lockbox System if implemented in 2020 (Round to the nearest whole dollar) TO CALCULATE THE REDUCTION IN ACCOUNTS RECEIVABLES USE THE REGIOONIIN GOFRETTON TIME FOR THE SELECTED LOCKBOX SYSTEM FROM PART OF USING THIS FORMULA Reduction collection timex Average customer payment x Average number of payments per day CASE STUDY Excel FORMULAS DATA REVIEW VIEW Carole Sanford X GS FILE HOME INSERT PAGE LAYOUT Cut Arial 12 LE Copy Paste Format Painter BIU 3 A Clipboard Font x A Der Percent 4 AutoSum 7 Wrap Text Merge Center Currency $ Fill 3 , 8 98 I D Percent 3 Conditional Format as Normal Formatting Table Styles Bad Insert Delete Format Clear Sort Find Filter Select Editing Alignment Number Cells F15 I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG LOCKBOX ANALYSIS OF ACCOUNTS RECEIVABLE INSTRUCTIONS FOR EVALUATION OF A LOCKBOX SYSTEM Global Manufacturing is considering using a lockbox system to speed up collections of accounts receivable from its customers in 2020 They have two Lockbox proposals to consider one from U S Bank, and one from Well Fargo Bank The information you will need to evaluate each bank's proposal is presented on this worksheet SAMPLE PRACTICE PROBLEM This problem is similar to Problems 27 11 and 27 12 HELP Refer back to your own spreadsheet for Problems 27 11b and 27 12 Also see the file named 05 SAMPLE COMPANY LOCKBOX CALCULATION ANALYSIS pdf provided to help you setup your spreadsheet for these LOCKBOX calculations Wells Fargo 3 252 5 470 DATA INPUTS U S Bank Rate or money market intestants 3 253 Average Customer payment 5 470 Average number of payments per day 78 Augal fee 9,500 Cost per transaction $0 64 Redaction is collection time 21 $31,500 $0 41 NOTE Cells shaded In YELLOW are the calls you must provide the calculations or analyals The questions that the CEO needs you to anster are as follors U S Bank Wells Fargo fal What is the Net Present Value (NPY) of cach of the act lockbor system proposals fick dig at fized staras (Road to the nearest whole dollar) (b) Hor many customers are needed, on average each day to make each of the lockbo1 susteas break eten (Round to the nearest hole customer (c) Ylich is the Preferred Lockbor System for Global Hanufacturing Iac (U S Bank or Wells Fargo) (d) HOT much of a reduction in accounts receivable should Global expect from the Preferred Lockbor Syates if implemented in 2020 (Rond to the nearest whole dollar) TO CALCULATE THE REDUCTION IN ACCOUNTS RECEIVABLES USE THE REDUCTION IN COLLECTION TIME FOR THE SELECTED LOCKBOX SYSTEM FROM PART C USING THIS FORMULA Reduction in collection time x Average customer payment x Average number of payments per day FINANCIAL DATA COMMON SIZE 2018 COMMON SIZE 2019 CHANGE RATIOS 2018 RATIOS 2019 EVALUATION OF FINDINGS LOCKBOX E READY 8 0 57 'o Type here to search di e 9 U wi xi O 9 1 4 4 2020 Wells Fargo 3 25 $5,470 DATA INPUTS U S Bank 3 25 $5,470 78 $9,500 $0 64 2 Rate on money market investments Average Customer payment Average number of payments per day Annual fee Cost per transaction Reduction in collection time 78 $31,500 $0 41 NOTE Cells shaded in YELLOW are the cells you must provide the calculations or analysis The questions that the CEO needs you to answer are as follows U S Bank Wells Fargo (a) What is the Net Present Value (NPV) of each of the new lockbox system proposals (including the annual fixed charge) (Round to the nearest whole dollar) (b) How many customers are needed, on average each day, to make each of the lockbox systems break even (Round to the nearest whole customer) (e) Which is the Preferred Lockbox System for Global Manufacturing, Inc 7 (U S Bankor Wells Fargo) (d) How much of a reduction in accounts receivable should Global expect from the Preferred Lockbox System If Implemented in 20207 (Round to the nearest whole dollar) LOCKBOX ANALYSIS OF ACCOUNTS RECEIVABLE INSTRUCTIONS FOR EVALUATION OF A LOCKBOX SYSTEM Global Manufacturing is considering using a lockbox system to speed up collections of accounts receivable from its customers in 2020 They have two Lockbox proposals to consider one from U S Bank, and one from Well Fargo Bank The information you will need to evaluate each bank's proposal is presented on this worksheet SAMPLE PRACTICE PROBLEM This problem is similar to Problems 27 11b and 27 12 HELP Refer back to your own spreadsheet for Problems 27 11b and 27 12 Also see the file named 05 SAMPLE COMPANY LOCKBOX CALCULATION ANALYSIS pdf provided to help you setup your spreadsheet for these LOCKBOX calculations DATA INPUTS U S Bank Wells Fargo Rate on money market investments 3 25 3 25 Average Customer payment $5,470 $5,470 Average number of payments per day 7 8 78 Annual fee $9,500 $31,500 Cost per transaction $0 64 $0 41 Reduction in collection time 214 NOTE Cells shaded in YELLOW are the cells you must provide the calculations or analysis The questions that the CEO needs you to answer are as follows Wells Fargo (a) What is the Net Present Value (NPV) of each of the new lockbox system proposals (including the annual fixed charge) (Round to the nearest whole dollar) (b) How many customers are needed, on average each day, to make each of the lockbox systems break even (Round to the nearest whole customer) (c) Which is the Preferred Lockbox System for Global Manufacturing, Inc (U S Bank of Wells Fargo) (d) How much of a reduction in accounts receivable should Global expect from the Preferred Lockbox System if implemented in 2020 (Round to the nearest whole dollar) TO CALCULATE THE REDUCTION IN ACCOUNTS RECEIVABLES USE THE REGIOONIIN GOFRETTON TIME FOR THE SELECTED LOCKBOX SYSTEM FROM PART OF USING THIS FORMULA Reduction collection timex Average customer payment x Average number of payments per day

Question

CASE STUDY   Excel FORMULAS DATA REVIEW VIEW Carole Sanford   X GS FILE HOME INSERT PAGE LAYOUT   Cut Arial  12 LE Copy Paste Format Painter BIU  3  A Clipboard Font x A     Der Percent 4 AutoSum   7 Wrap Text Merge   Center   Currency $     Fill  3 , 8 98 I D Percent 3 Conditional Format as Normal Formatting Table Styles Bad Insert Delete Format Clear Sort   Find   Filter   Select Editing Alignment Number Cells F15 I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG LOCKBOX ANALYSIS OF ACCOUNTS RECEIVABLE INSTRUCTIONS FOR EVALUATION OF A LOCKBOX SYSTEM  Global Manufacturing is considering using a lockbox system to speed up collections of accounts receivable from its customers in 2020  They have two Lockbox proposals to consider one from U S  Bank, and one from Well Fargo Bank  The information you will need to evaluate each bank's proposal is presented on this worksheet  SAMPLE PRACTICE PROBLEM  This problem is similar to Problems 27 11 and 27 12 HELP  Refer back to your own spreadsheet for Problems 27 11b and 27 12  Also see the file named 05 SAMPLE COMPANY LOCKBOX CALCULATION   ANALYSIS pdf provided to help you setup your spreadsheet for these LOCKBOX calculations  Wells Fargo 3 252 5 470 DATA INPUTS U  S  Bank Rate or money market intestants 3 253 Average Customer payment 5 470 Average number of payments per day 78 Augal fee 9,500 Cost per transaction $0 64 Redaction is collection time 21 $31,500 $0 41 NOTE  Cells shaded In YELLOW are the calls you must provide the calculations or analyals  The questions that the CEO needs you to anster are as follors  U  S  Bank Wells Fargo fal What is the Net Present Value (NPY) of cach of the act lockbor system proposals fick dig at fized staras  (Road to the nearest whole dollar) (b) Hor many customers are needed, on average each day  to make each of the lockbo1 susteas break eten  (Round to the nearest hole customer (c) Ylich is the Preferred Lockbor System for Global Hanufacturing  Iac   (U S  Bank or Wells Fargo) (d) HOT much of a reduction in accounts receivable should Global expect from the Preferred Lockbor Syates if implemented in 2020  (Rond to the nearest whole dollar) TO CALCULATE THE REDUCTION IN ACCOUNTS RECEIVABLES USE THE REDUCTION IN COLLECTION TIME FOR THE SELECTED LOCKBOX SYSTEM FROM PART  C  USING THIS FORMULA    Reduction in collection time x Average customer payment x Average number of payments per day     FINANCIAL DATA COMMON SIZE 2018 COMMON SIZE 2019   CHANGE RATIOS 2018 RATIOS 2019 EVALUATION OF FINDINGS LOCKBOX E      READY 8 0   57  'o Type here to search di e 9 U    wi xi O   9 1 4 4 2020 Wells Fargo 3 25  $5,470 DATA INPUTS U S  Bank 3 25  $5,470 78 $9,500 $0 64 2 Rate on money market investments Average Customer payment Average number of payments per day Annual fee Cost per transaction Reduction in collection time 78 $31,500 $0 41 NOTE  Cells shaded in YELLOW are the cells you must provide the calculations or analysis  The questions that the CEO needs you to answer are as follows  U S  Bank Wells Fargo (a) What is the Net Present Value (NPV) of each of the new lockbox system proposals (including the annual fixed charge)   (Round to the nearest whole dollar) (b) How many customers are needed, on average each day, to make each of the lockbox systems break even  (Round to the nearest whole customer) (e) Which is the Preferred Lockbox System for Global Manufacturing, Inc 7 (U S  Bankor Wells Fargo) (d) How much of a reduction in accounts receivable should Global expect from the Preferred Lockbox System If Implemented in 20207 (Round to the nearest whole dollar) LOCKBOX ANALYSIS OF ACCOUNTS RECEIVABLE INSTRUCTIONS FOR EVALUATION OF A LOCKBOX SYSTEM  Global Manufacturing is considering using a lockbox system to speed up collections of accounts receivable from its customers in 2020  They have two Lockbox proposals to consider one from U S  Bank, and one from Well Fargo Bank  The information you will need to evaluate each bank's proposal is presented on this worksheet  SAMPLE PRACTICE PROBLEM  This problem is similar to Problems 27 11b and 27 12 HELP  Refer back to your own spreadsheet for Problems 27 11b and 27 12  Also see the file named 05  SAMPLE COMPANY LOCKBOX CALCULATION   ANALYSIS pdf  provided to help you setup your spreadsheet for these LOCKBOX calculations  DATA INPUTS U  S  Bank Wells Fargo Rate on money market investments 3 25  3 25  Average Customer payment $5,470 $5,470 Average number of payments per day 7 8 78 Annual fee $9,500 $31,500 Cost per transaction $0 64   $0 41  Reduction in collection time 214 NOTE  Cells shaded in YELLOW are the cells you must provide the calculations or analysis The questions that the CEO needs you to answer are as follows  Wells Fargo (a) What is the Net Present Value (NPV) of each of the new lockbox system proposals (including the annual fixed charge)   (Round to the nearest whole dollar) (b) How many customers are needed, on average each day, to make each of the lockbox systems break even  (Round to the nearest whole customer) (c) Which is the Preferred Lockbox System for Global Manufacturing, Inc   (U S  Bank of Wells Fargo) (d) How much of a reduction in accounts receivable should Global expect from the Preferred Lockbox System if implemented in 2020  (Round to the nearest whole dollar) TO CALCULATE THE REDUCTION IN ACCOUNTS RECEIVABLES USE THE REGIOONIIN GOFRETTON TIME FOR THE SELECTED LOCKBOX SYSTEM FROM PART OF USING THIS FORMULA    Reduction collection timex Average customer payment x Average number of payments per day CASE STUDY   Excel FORMULAS DATA REVIEW VIEW Carole Sanford   X GS FILE HOME INSERT PAGE LAYOUT   Cut Arial  12 LE Copy Paste Format Painter BIU  3  A Clipboard Font x A     Der Percent 4 AutoSum   7 Wrap Text Merge   Center   Currency $     Fill  3 , 8 98 I D Percent 3 Conditional Format as Normal Formatting Table Styles Bad Insert Delete Format Clear Sort   Find   Filter   Select Editing Alignment Number Cells F15 I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG LOCKBOX ANALYSIS OF ACCOUNTS RECEIVABLE INSTRUCTIONS FOR EVALUATION OF A LOCKBOX SYSTEM  Global Manufacturing is considering using a lockbox system to speed up collections of accounts receivable from its customers in 2020  They have two Lockbox proposals to consider one from U S  Bank, and one from Well Fargo Bank  The information you will need to evaluate each bank's proposal is presented on this worksheet  SAMPLE PRACTICE PROBLEM  This problem is similar to Problems 27 11 and 27 12 HELP  Refer back to your own spreadsheet for Problems 27 11b and 27 12  Also see the file named 05 SAMPLE COMPANY LOCKBOX CALCULATION   ANALYSIS pdf provided to help you setup your spreadsheet for these LOCKBOX calculations  Wells Fargo 3 252 5 470 DATA INPUTS U  S  Bank Rate or money market intestants 3 253 Average Customer payment 5 470 Average number of payments per day 78 Augal fee 9,500 Cost per transaction $0 64 Redaction is collection time 21 $31,500 $0 41 NOTE  Cells shaded In YELLOW are the calls you must provide the calculations or analyals  The questions that the CEO needs you to anster are as follors  U  S  Bank Wells Fargo fal What is the Net Present Value (NPY) of cach of the act lockbor system proposals fick dig at fized staras  (Road to the nearest whole dollar) (b) Hor many customers are needed, on average each day  to make each of the lockbo1 susteas break eten  (Round to the nearest hole customer (c) Ylich is the Preferred Lockbor System for Global Hanufacturing  Iac   (U S  Bank or Wells Fargo) (d) HOT much of a reduction in accounts receivable should Global expect from the Preferred Lockbor Syates if implemented in 2020  (Rond to the nearest whole dollar) TO CALCULATE THE REDUCTION IN ACCOUNTS RECEIVABLES USE THE REDUCTION IN COLLECTION TIME FOR THE SELECTED LOCKBOX SYSTEM FROM PART  C  USING THIS FORMULA    Reduction in collection time x Average customer payment x Average number of payments per day     FINANCIAL DATA COMMON SIZE 2018 COMMON SIZE 2019   CHANGE RATIOS 2018 RATIOS 2019 EVALUATION OF FINDINGS LOCKBOX E      READY 8 0   57  'o Type here to search di e 9 U    wi xi O   9 1 4 4 2020 Wells Fargo 3 25  $5,470 DATA INPUTS U S  Bank 3 25  $5,470 78 $9,500 $0 64 2 Rate on money market investments Average Customer payment Average number of payments per day Annual fee Cost per transaction Reduction in collection time 78 $31,500 $0 41 NOTE  Cells shaded in YELLOW are the cells you must provide the calculations or analysis  The questions that the CEO needs you to answer are as follows  U S  Bank Wells Fargo (a) What is the Net Present Value (NPV) of each of the new lockbox system proposals (including the annual fixed charge)   (Round to the nearest whole dollar) (b) How many customers are needed, on average each day, to make each of the lockbox systems break even  (Round to the nearest whole customer) (e) Which is the Preferred Lockbox System for Global Manufacturing, Inc 7 (U S  Bankor Wells Fargo) (d) How much of a reduction in accounts receivable should Global expect from the Preferred Lockbox System If Implemented in 20207 (Round to the nearest whole dollar) LOCKBOX ANALYSIS OF ACCOUNTS RECEIVABLE INSTRUCTIONS FOR EVALUATION OF A LOCKBOX SYSTEM  Global Manufacturing is considering using a lockbox system to speed up collections of accounts receivable from its customers in 2020  They have two Lockbox proposals to consider one from U S  Bank, and one from Well Fargo Bank  The information you will need to evaluate each bank's proposal is presented on this worksheet  SAMPLE PRACTICE PROBLEM  This problem is similar to Problems 27 11b and 27 12 HELP  Refer back to your own spreadsheet for Problems 27 11b and 27 12  Also see the file named 05  SAMPLE COMPANY LOCKBOX CALCULATION   ANALYSIS pdf  provided to help you setup your spreadsheet for these LOCKBOX calculations  DATA INPUTS U  S  Bank Wells Fargo Rate on money market investments 3 25  3 25  Average Customer payment $5,470 $5,470 Average number of payments per day 7 8 78 Annual fee $9,500 $31,500 Cost per transaction $0 64   $0 41  Reduction in collection time 214 NOTE  Cells shaded in YELLOW are the cells you must provide the calculations or analysis The questions that the CEO needs you to answer are as follows  Wells Fargo (a) What is the Net Present Value (NPV) of each of the new lockbox system proposals (including the annual fixed charge)   (Round to the nearest whole dollar) (b) How many customers are needed, on average each day, to make each of the lockbox systems break even  (Round to the nearest whole customer) (c) Which is the Preferred Lockbox System for Global Manufacturing, Inc   (U S  Bank of Wells Fargo) (d) How much of a reduction in accounts receivable should Global expect from the Preferred Lockbox System if implemented in 2020  (Round to the nearest whole dollar) TO CALCULATE THE REDUCTION IN ACCOUNTS RECEIVABLES USE THE REGIOONIIN GOFRETTON TIME FOR THE SELECTED LOCKBOX SYSTEM FROM PART OF USING THIS FORMULA    Reduction collection timex Average customer payment x Average number of payments per day

Accepted Answer

The Answer is in the image, click to view ...

Question

CASE STUDY - Excel FORMULAS DATA REVIEW VIEW Carole Sanford - X GS FILE HOME INSERT PAGE LAYOUT % Cut Arial -12 LE Copy Paste

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Cryptocurrency And Public Policy

Students also viewed these Finance questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question