Please build a spreadsheet similar to Table 6.1 to compute the maximum overdraft for a project with the following characteristics:

Total Direct Costs: $1,245,763

Total Indirect Costs: $224,500 Markup: 12%

Retainage held by Owner: 10%

Interest on Overdraft Balance: 1.5%

Project Duration: 8 months

Payments to be made by Owner at the end of the 2nd month after cost incurred (same as Table 6.1) Retainage to be paid by Owner 60 days after project completion (after month 10) Your company is not holding retainage on any subcontractors or vendors and payments are made for all costs in the same month (identical to the model)

Cost schedule over the 8-month construction period by percentage of cost incurred (for both direct and indirect costs):

Month 1: 5%

Month 5: 23%

Month 2: 8%

Month 6: 15%

TABLE 6.1 Overdraft Calculations Month 1 2 3 4 Direct cost Indirect cost Subtotal Markup Total Billed Retainage withheld $25,000 5,000 30,000 1,500 31,500 3,150 $65.000 5.000 70,000 3,500 73,500 7.350 $75,000 5,000 80,000 4,000 84,000 0 $15,000 5,000 20,000 1,000 21,000 0 $28,350 $66,150 884,000 $31,500 30,000 31,500 100.000 105.000 180,000 189,000 200,000 210,000 200,000 210,000 Payment Received Total cost to date Total amount billed to date Total paid to date Overdraft end of month Interest on overdraft balance Total amount financed 28,350 94,500 178,500 210,000 30,000 300 $30,300 100,300 1.003 $101.303 152,953 1,530 $154,483 108,333 1,083 $109,416 25,416 254 S25,670 (-)5830 0 TABLE 6.1 Overdraft Calculations Month 1 2 3 4 Direct cost Indirect cost Subtotal Markup Total Billed Retainage withheld $25,000 5,000 30,000 1,500 31,500 3,150 $65.000 5.000 70,000 3,500 73,500 7.350 $75,000 5,000 80,000 4,000 84,000 0 $15,000 5,000 20,000 1,000 21,000 0 $28,350 $66,150 884,000 $31,500 30,000 31,500 100.000 105.000 180,000 189,000 200,000 210,000 200,000 210,000 Payment Received Total cost to date Total amount billed to date Total paid to date Overdraft end of month Interest on overdraft balance Total amount financed 28,350 94,500 178,500 210,000 30,000 300 $30,300 100,300 1.003 $101.303 152,953 1,530 $154,483 108,333 1,083 $109,416 25,416 254 S25,670 (-)5830 0