Question

A small construction firm, A&B Construction, is working on a house project for Mr. Long. The estimated building time is 8 months and the estimated costs to the firm (labor, materials, etc.) are $20,000 per month for 8 months (assume that the firm needs to pay the monthly costs at the end of each month). The contract indicates that the customer will pay a total of $200,000 for the project but it does not specify when the customer will pay the firm. To solve this problem, the firm offers to let the customer pay $50,000 in the end of each month for the first four months, but the customer offers to pay $23,000 in the end of each month for 8 months plus another payment of $16,000 when the project is completed. Assume that the firm would deposit all the earned money (profit) in a bank account earning a monthly interest rate of 0.476% (compounded monthly) 1(a) Conduct an analysis to estimate the difference in the firm's total profit (i.e., bank account balance) at the end of the project between the firm's offer and the customer's offer (you need to use the computer program in Excel). Note that the major objective of this project is to estimate what the difference between the two offers will be

Answer 1

H F20 fo =G19*$B$1 AB i Bank rate 0.476% Month Cost Outflow Firms Offer Net Payments to bank Interest Net Balance at the month end 1 $ 20,000.00 $ 50,000.00 $ 30,000.00 30,000.00 2 $ 20,000.00 $ 50,000.00 $ 30,000.00 $142.80 $ 60,142.80 3 $ 20,000.00 $ 50,000.00 $ 30,000.00 $286.28 $ 90,429.08 4 $ 20,000.00 $ 50,000.00 $ 30,000.00 $430.44 $ 120,859.52 5 $ 20,000.00 (20,000.00) $575.29 $ 101,434.81 6 $ 20,000.00 $ (20,000.00) $482.83 $ 81,917.64 7 $ 20,000.00 (20,000.00) $389.93 $ 62,307.57 8 $ 20,000.00 $ (20,000.00) $296.58 $ 42,604.16 Month Cost Outflow Customer Offer Net Payments to bank Interest Net Balance at the month end 1 $ 20,000.00 $ 23,000.00 $ 3,000.00 3,000.00 2 $ 20,000.00 $ 23,000.00 $ 3,000.00 $ 14.28 $ 6,014.28 3 $ 20,000.00 $ 23,000.00 $ 3,000.00 $ 28.63 $ 9,042.91 4 $ 20,000.00 $ 23,000.00 $ 3,000.00 $ 43.04 $ 12,085.95 5 $ 20,000.00 $ 23,000.00 $ 3,000.00 $ 57.53 $ 15,143.48 6 $ 20,000.00 $ 23,000.00 $ 3,000.00 $ 72.08 $ 18,215.56 7 $ 20,000.00 $ 23,000.00 $ 3,000.00 $ 86.71 $ 21,302.27 8 $ 20,000.00 $ 39,000.00 $ 19,000.00 $ 101.40 $ 40,403.67 19 21

A 1 Bank rate B 0.00476 Month Cost Outflow Firms Offer 20000 50000 20000 50000 20000 50000 20000 50000 20000 20000 7 20000 20000 Net Payments to bank Interest Net Balance at the month end =D3-C3 =E3+F3 =D4-C4 =G3*$B$1 =G3+E4+F4 =D5-C5 =G4*$B$1 =G4+ES+F5 =D6-C6 =G5*$B$1 =G5+E6+F6 =D7-C7 =G6*$B$1 =G6+E7+F7 =D8-C8 =G7*$B$1 =G7+E8+F8 =D9-C9 =G8*$B$1 =G8+E9+F9 =D10-C10 =G9*$B$1 =G9+E10+F10 Month Cost Outflow Customer Offer Net Payments to bank Interest Net Balance at the month end 20000 23000 =D13-C13 =E13+F13 20000 23000 =D14-C14 =G13*$B$1 =G13+E14+F14 20000 23000 =D15-C15 =G14*$B$1 =G14+E15+F15 20000 23000 =D16-C16 =G15*$B$1 =G15+16+F16 20000 23000 =D17-C17 =G16*$B$1 =G16+E17+F17 20000 23000 =D18-C18 =G17*$B$1 =G17+E18+F18 23000 =D19-C19 =G18"$B$1 =G18+E19+F19 8 20000 =23000+16000 =D20-C20 =G19*$B$1 =G19+E20+F20 20000

Firm offer:

During Month 1, there is outflow of $20,000 for cost, inflow of $50,000 as payment by customer. Since these payments are at the end of the month, there is no interest by bank. Net amount in bank at the end of month 1 is $50,000-$20,000 = $30,000

In Month 2, there is outflow of $20,000 for cost, inflow of $50,000 as payment by customer. Also there will be interest on last month end balance of $30,000. The interest will be $30,000*0.476% =$142.8

Thus net balance will be $30,000+$30,000+$142.8 = $60,142.8

Month 3, $60,142.8+$50,000-$20,000+$60,142.8*0.476% = $90,429.08

Month 4: $90,429.08++$50,000-$20,000+$90,429.08*0.476% = $120,859.52

Month 5: $120,859.52-$20,000+$120,859.52*0.476% = $101,434.81

Month 6: $101,434.81-$20,000+$101,434.81*0.476% = $81,917.64

Month 7: $81,917.64-$20,000+$81,917.64*0.476% = $62,307.57

Month 8: $62,307.57-$20,000+$62,307.57*0.476% = $42,604.16

Thus Bank Balance after 8 month will be $42,604.16

Customer Offer:

During Month 1, there is outflow of $20,000 for cost, inflow of $23,000 as payment by customer. Since these payments are at the end of the month, there is no interest by bank. Net amount in bank at the end of month 1 is $23,000-$20,000 = $3,000

In Month 2, there is outflow of $20,000 for cost, inflow of $23,000 as payment by customer. Also there will be interest on last month end balance of $3,000. The interest will be $3,000*0.476% =$14.28

Thus net balance will be $3,000+$3,000+$14.28 = $6014.28

Month 3: $6,014.28+$23,000-$20,000+$6,014.28*0.476% = $9,042.91

Month 4:$9,042.91+$23,000-$20,000+$9,042.91*0.476% = $12,085.95

Month 5:$12,085.95+$23,000-$20,000+$12,085.95*0.476% = $15,143.48

Month 6:$15,143.48+$23,000-$20,000+$15,143.48*0.476% = $18,215.56

Month 7:$18,215.56+$23,000-$20,000+$18,215.56*0.476% = $21,302.57

Month 8:$21,302.57+$23,000+$16,000-$20,000+$21,302.57*0.476% = $40,403.67

Thus bank balance would be more in Firms offer