The below question is attached. I have also attempted the problem so that I can build template but getting stuck. Only the items written in white boxes that are in blue and bold were given as part of the problem. Everything else was written by me while attempting to answer and build template for reference.

According to the feedback 4,5,6,7, 9, 15 & 16 listed in the requirements at bottom of spreadsheet are wrong. The other answers are correct.

The Hand-to-Mouth Company needs a $10,000 loan for the next 30 days. It is trying to decide which of three alternatives to use:

Problem 27-6 The Hand-to-Mouth Company needs a $10,000 loan for the next 30 days. It is trying to deci Alternative A: Forgo the discount on its trade credit agreement that offers terms of 2/ Alternative B: Borrow the money from Bank A, which has offered to lend the firm $1 (no-interest) compensating balance of 5% of the face value of the loan Hand-to-Mouth must borrow even more than the $10,000. Alternative C: Borrow the money from Bank B, which has offered to lend the firm $1 origination fee. Which alternative is the cheapest source of financing for Hand-to-Mouth? Principal Term of loan $10,000 30 Alternative A: Forego trade discount Credit Terms 2.00% Additional days Interest rate per period Annual rate 20 2.000% 2.89% Alternative B: Borrow from Bank A APR 12.00% Compensating balance 5.00% Fee $100.00 Total borrowed Interest paid Interest & fee paid Periodic rate Annual rate $10,526.32 $105.26 $205.26 2.05% 24.63% Alternative C: Borrow from Bank B APR 15.00% Compensating balance 0.00% Origination fee 1.00% Fee Total borrowed Interest paid Interest & fee paid Periodic rate Annual rate $100.00 $10,100.00 $126.25 $226.25 2.26% 27.15% Cheapest loan cost This is: $205.26 Alternative B Requirements 1. In cell D16, by using cell references, calculate the additional days of credit (1 pt.). 2. In cell D17, by using cell references, calculate the implicit interest rate charged for the addit 3. In cell D18, by using cell references, calculate the annual cost of payables (1 pt.). 4. In cell D25, by using cell references, calculate the total amount to borrow (1 pt.). 5. In cell D26, by using cell references, calculate the interest paid (1 pt.). 6. In cell D27, by using cell references, calculate the interest & fee paid (1 pt.). 7. In cell D28, by using cell references, calculate the periodic rate by dividing the interest & fe 8. In cell D29, by using cell references, calculate the annual rate (1 pt.). 9. In cell D37, by using cell references, calculate the fee to be paid (1 pt.). 10. In cell D38, by using cell references, calculate the total amount to borrow (1 pt.). 11. In cell D39, by using cell references, calculate the interest paid (1 pt.). 12. In cell D40, by using cell references, calculate the interest & fee paid (1 pt.). 13. In cell D41, by using cell references, calculate the periodic rate (1 pt.). 14. In cell D42, by using cell references, calculate the annual rate (1 pt.). 15. You will find the cheapest loan cost by using the function MIN. In cell D44, by using the fu the cheapest loan cost (1 pt.). 16. In cell D45, identify the cheapest alternative by typing Alternative A, Alternative B or Alt he next 30 days. It is trying to decide which of three alternatives to use: it agreement that offers terms of 2/10, net 30. hich has offered to lend the firm $10,000 for 30 days at an APR of 12%. The bank will require a of 5% of the face value of the loan and will charge a $100 loan origination fee, which means more than the $10,000. hich has offered to lend the firm $10,000 for 30 days at an APR of 15%. The loan has a 1% loan Hand-to-Mouth? 10 =G14-E14 =D14 net 30 = net - days in credit term = what is given already in credit terms = 365/ Interest rate per period 2.892% 1.0204 18.25 1.44585292731249 0.44585292731249 =D10/(1-D22) =(D25*D21)/12 =D26+D23 =D27/D10 =D28*12 =Amount able to be used by firm/(1 - compensating balance) =(Total borrowed*APR)/12 (or number of periods annually) = Interest + Fee Paid = (Interest + Fee Paid)/ amount able to be used by firm = Periodic rate * 12 (or # of periods annually) =D35*D10 =D10+D37 =(D33*D38)/12 =D39+D37 =D40/D10 =D41*12 = origination fee % * Priciple = origination fee + Principle = (APR * Total borrowed)/12 (or # periods annually) = Interest + Fee Paid = Interest & Fee Paid/ Priciple = Periodic rate * 12 (or # periods annually) onal days of credit (1 pt.). it interest rate charged for the additional days of credit (1 pt.). cost of payables (1 pt.). mount to borrow (1 pt.). t paid (1 pt.). t & fee paid (1 pt.). ic rate by dividing the interest & fee paid (1 pt.). rate (1 pt.). be paid (1 pt.). mount to borrow (1 pt.). t paid (1 pt.). t & fee paid (1 pt.). ic rate (1 pt.). rate (1 pt.). n MIN. In cell D44, by using the function MIN and cell references, find lternative A, Alternative B or Alternative C (1 pt.). Problem 27-6 The Hand-to-Mouth Company needs a $10,000 loan for the next 30 days. It is trying to deci Alternative A: Forgo the discount on its trade credit agreement that offers terms of 2/ Alternative B: Borrow the money from Bank A, which has offered to lend the firm $1 (no-interest) compensating balance of 5% of the face value of the loan Hand-to-Mouth must borrow even more than the $10,000. Alternative C: Borrow the money from Bank B, which has offered to lend the firm $1 origination fee. Which alternative is the cheapest source of financing for Hand-to-Mouth? Principal Term of loan $10,000 30 Alternative A: Forego trade discount Credit Terms 2.00% Additional days Interest rate per period Annual rate 20 2.000% 24.00% Alternative B: Borrow from Bank A APR 12.00% Compensating balance 5.00% Fee $100.00 Total borrowed Interest paid Interest & fee paid Periodic rate Annual rate $10,631.58 $104.86 $204.86 2.05% 24.58% Alternative C: Borrow from Bank B APR 15.00% Compensating balance 0.00% Origination fee 1.00% Fee Total borrowed Interest paid Interest & fee paid Periodic rate Annual rate $100.00 $10,100.00 $126.25 $226.25 2.26% 27.15% Cheapest loan cost This is: 24.00% Alternative A Requirements 1. In cell D16, by using cell references, calculate the additional days of credit (1 pt.). 2. In cell D17, by using cell references, calculate the implicit interest rate charged for the addit 3. In cell D18, by using cell references, calculate the annual cost of payables (1 pt.). 4. In cell D25, by using cell references, calculate the total amount to borrow (1 pt.). 5. In cell D26, by using cell references, calculate the interest paid (1 pt.). 6. In cell D27, by using cell references, calculate the interest & fee paid (1 pt.). 7. In cell D28, by using cell references, calculate the periodic rate by dividing the interest & fe 8. In cell D29, by using cell references, calculate the annual rate (1 pt.). 9. In cell D37, by using cell references, calculate the fee to be paid (1 pt.). 10. In cell D38, by using cell references, calculate the total amount to borrow (1 pt.). 11. In cell D39, by using cell references, calculate the interest paid (1 pt.). 12. In cell D40, by using cell references, calculate the interest & fee paid (1 pt.). 13. In cell D41, by using cell references, calculate the periodic rate (1 pt.). 14. In cell D42, by using cell references, calculate the annual rate (1 pt.). 15. You will find the cheapest loan cost by using the function MIN. In cell D44, by using the fu the cheapest loan cost (1 pt.). 16. In cell D45, identify the cheapest alternative by typing Alternative A, Alternative B or Alt he next 30 days. It is trying to decide which of three alternatives to use: it agreement that offers terms of 2/10, net 30. hich has offered to lend the firm $10,000 for 30 days at an APR of 12%. The bank will require a of 5% of the face value of the loan and will charge a $100 loan origination fee, which means more than the $10,000. hich has offered to lend the firm $10,000 for 30 days at an APR of 15%. The loan has a 1% loan Hand-to-Mouth? 10 =G14-E14 =D14 net 30 = net - days in credit term = what is given already in credit terms = 365/ Interest rate per period 2.892% 1.0204 18.25 1.44585292731249 0.44585292731249 =D10/(1-D22) =(D25*D21)/12 =D26+D23 =D27/D10 =D28*12 =Amount able to be used by firm/(1 - compensating balance) =(Total borrowed*APR)/12 (or number of periods annually) = Interest + Fee Paid = (Interest + Fee Paid)/ amount able to be used by firm = Periodic rate * 12 (or # of periods annually) =D35*D10 =D10+D37 =(D33*D38)/12 =D39+D37 =D40/D10 =D41*12 = origination fee % * Priciple = origination fee + Principle = (APR * Total borrowed)/12 (or # periods annually) = Interest + Fee Paid = Interest & Fee Paid/ Priciple = Periodic rate * 12 (or # periods annually) onal days of credit (1 pt.). it interest rate charged for the additional days of credit (1 pt.). cost of payables (1 pt.). mount to borrow (1 pt.). t paid (1 pt.). t & fee paid (1 pt.). ic rate by dividing the interest & fee paid (1 pt.). rate (1 pt.). be paid (1 pt.). mount to borrow (1 pt.). t paid (1 pt.). t & fee paid (1 pt.). ic rate (1 pt.). rate (1 pt.). n MIN. In cell D44, by using the function MIN and cell references, find lternative A, Alternative B or Alternative C (1 pt.)