- Prepare an original Excel spreadsheet " Cash Flow Analysis" incorporating data and formulas on the attached information sheets.
- Attachment "A" contains monthly and total costs for the project. These costs are to be incorporated into your Cash Flow Work Sheet.
- Attachment "B" PDF shows the layout of the Cash Flow spreadsheet. As part of the exercise, insert monthly and total costs. The lower portion of the attachment contains formulas to insert into your spreadsheet.
- In addition to submitting your original Excel spreadsheet, you must also include an "S" curve chart showing : Cumulative Receivables and Cumulative Gross Pay Request to Owner.
Using the cost loaded schedule below, prepare a Cash Flow for the project. Total Estimate / Contract Cost: Retainage: $ 9,800,840 10% Direct Cost Billed to Month Subcontractor Equipment Labor S Materials Owner (Includes Overhead & Profit) 588,050 784,067 1 6,420 8,560 2 3 10,700 980,084 4 1,176,101 5 15,300 20,400 25,500 30,600 35,700 38,250 30,600 25,500 20,400 12,750 106,020 141,360 176,700 212,040 247,380 265,050 212,040 176,700 141,360 88,350 379,200 505,600 632,000 758,400 884,800 948,000 758,400 632,000 505,600 316,000 12.840 14.980 16.050 12.840 10.700 1,372,118 1,470,126 6 7 1,176,101 980,084 8 9 8,560 784,067 10 5,350 490,042 Totals 255,000 1,767,000 6,320,000 107.000 9,800,840 A B D E H Attachment B Retention rate: 0.1 0.1 0.1 0.1 0.1 Month Gross Pay Request to Owner 1 588050 2 784067 3 980084 4 1176101 5 1372118 Labor Material Subcontractor Equipment 15300 106020 379200 6420 ESUM D8:011) 20400 141360 505600 8560 ESUM E8:E11) 25500 176700 632000 10700 =SUMIFB:F11) 30600 212040 758400 12840 =SUMIGR:611) 35700 247380 884800 14980 ESUM H8:H11) Total L,M,S,E Costs P&O (16% of L,S,M and E above)) EROUNDID12*0.16.0) =ROUND(E12*0.16.0) =ROUND(F12*0.16,0) =ROUND/G12*0.16.0) =ROUNDTH12*0.16,0) 116 517 Retainage Held by Owner =D5*0.1 =E5*0.1 EF5*0.1 EGS *0.1 =H5*0.1 Receivables from Owner 0 E/D5-D17) E E5-E171 =[F5-F17) =IG5-G17) TECEEEEEEEEEEEEEEEEEEEEEEEEEEE Payment Outs by CM Labor Material Subcontractor Equipment P&O Recognized 208 =09 0 =D11 24 25 L26 127 28 29 30 =E8 -E9 =D10*0.9 -E11 D14 0.9 ESUM(E23:E27) =F8 =F9 =E10*0.9 -F11 -E14*0.9 =SUM(F23:F27) =G8 =G9 =F10*0.9 =G11 F140.9 =SUM(G23:627) H8 =H9 =G10*0.9 =H11 =G14*0.9 ESUM(H23:H27) Total Pay Outs =SUM(D23:D27) Net Cash Flow (After payment by owner of prior month) =D20-028 =E20-E28 =F20-F28 =G20-G28 EH20-H28 Cumulative Cash Flow =D32 =E324034 -F32+E34 =G32+F34 =H32+G34 35 20 A H Attachment B Retention rate: 10.1 10.1 10.1 0.1 0.1 0.1 0.1 Month Gross Pay Request to Owner 11 SBBOSO 2 784057 13 980084 14 1175101 5 1372118 6 1470126 7 1176101 1 2 21 3 4 51 6 7 B 91 9 10 11 12 13 14 15 16 17 18 191 Labor Material Subcontractor Equipment 15300 106020 379200 5420 SUMD8:011) 120400 141350 SO5600 8560 SUM[E8.011) 25500 176700 1632000 10700 |-SUM F8.F11] 30500 212040 758400 12840 -SUMIGR:G11 35700 247380 884800 14980 SUM(H8:11) 38250 |265050 948000 16050 -SUM (18:111) 20600 212040 758400 12840 -SUM8:11) Total L.MS, E Costs P&O (16 % of L.S.M and E above) -ROUND D12*0.160 ROUND E12*0.16 01 EROUND F12*0.16.00 EROUNDIG120.160 -ROUNDH120.16,0) -ROUND 1120.16,0) LROUND 1120.160 Retainage Held by Owner -50.1 * ES*0.1 -F5*0.1 -G5*0.1 =H5*0.1 -15*0.1 -15*0.1 20 Receivables from Owner =(D5-D17) -IES E17) {F5-F17) -G5 G17 -H5 H17) (15-117 Payment Outs by CM 21 22 23 24 25 26 27 28 29 30 Labor Material Subcontractor Equipment P&O Recognized -DS -D9 10 1-011 -EX -E9 -D10*0.9 -E11 ED14 0.9 -SUME 23:E271 =F8 -F9 -E10*0.9 -F11 -E1490.9 SUM/F23:F27) -G8 -69 -F10*0.9 -611 -F14*0.9 ESUMIG23:G27) -HS |--19 -610*0.9 -H11 G14*0.9 SLIMH23:27 18 -19 -H10*0.9 =111 1-18 -19 -110'0.9 11 11409 SUM(J23:27) -H14*0.9 SUM|123:127) Total Pay Outs SUMD23:D27) 31 Net Cash Flow (After payment by owner of prior month) 32 33 -D20-028 -E20 E28 -F20-F2R =G20 G2 H20-H2 -120-28 -120-128 341 Cumulative Cash Flow -D32 =E32+034 F32E34 -632F34 |-H32+634 132+H34 -J32+134 35 36 37 38 39 40 41 Guide to Setting Up Chart as "S* Curve 42 Month Gross Pay Request to Owner Receivables from Owner 5RROSO 784067 529245 980084 705660.3 1176101 882075.6 1372118 1058490.9 1470126 1234906.2 1176101 13231134 Convert above to cumulative 431 441 45 46 471 48 49 50 51 52 53 54 IC Cumulative Receivables from Owner Cumulative Gross Pay Request to Owner |-049+E45 D50-44 E49+F45 ener -E5D+F44 -F49+G4S =F506144 -649-4S -650H4 H49+145 -H50-144 =49145 -150+144 -D44 K M N 0 P 9 10.1 10.1 Check 784057 10 490042 -DS+ES+FS+G5+HS+15+15+5+5+M5 20.1 3 A 18 5 980084 6 17 8 25500 9176700 10632000 11 10700 12 -SUM(K8:11) 13 14 =ROUND K12*0.16,0) 15 16 17=K5*0.1 KS 18 20400 141300 505600 8560 -SUML8:11 12750 88350 316000 5350 SUMM:M11) -D8E8FGHAIN:8KB+8+MB -D9+E9F9+G9+H9+19+9+9+9+M9 -D10+E10+F10+G10+H10+120+JIO+K10+L10MIO -D11-E11+F11+G11+H11+11+11+11+L11+M11 -D12+12+F12+G12 H12+12+12+12+12M2 -ROUND(L12*0.16,0) =ROUND M12*0.16,0) -D14+E14+F14+G14+H34+14+J14+14+M14+L14 -08+09+010+011+014 --L5*0.1 SMS 0.1 SUMD17:N17) 20-05 217) - ) IEKS K17 (15-17) LIMS MITI 980084 =D20+20+F20+20+20+20+20+20+L20+M20+N20+P20 Retainage Payment 21 22 231-KR 241-9 -Q100.1 -L8 1-19 -K10*0.9 * |--11 =K14*0.9 SUML23:1271 251-110"0.9 26-K11 27-1140.9 28 SUM(K23:27) 29 30 -MA T-M EL100.9 |-MIL |=L14*0.9 -SUMM23:M27) -N8 0 -M10*0.9 ENAI PP14 0.9 -SUMN23-N27 EXD23+23+F23.62 3+H2 3+12 3+23+23+23+M23+N23) -D24+24+F24+G24+H24+24+J24+K24+24+24+N24 ++ ED25+25+F25+G25+H25+25+25+25+25+25+N25 -D25+25+F26+G26+H26+126+126+26+L25-M26+N25 -D27-E27-F27+G27+H27+127+12 7+27+L27-M27+N27 -SUMQ23:027 -Q1490.1 980084 Retainage Held by Less Retainage In CM Payments to L.M.E.Overhead and Profit 31 -212900 L2O-L28 MZO-M28 I-N20 N28 EP17./P25+P27 SUM(032-P32) L32+K34 -M32+34 -N32-M34 =P32+034 =928+030031 This is included in the final payment of retainage. It represents the fact that the owner held retainage on labor, material and equipment, but you had to pay out the full amount. 32 -K20-K28 33 34-K32+134 35 36 37 38 39 40 41 42 438 44980084 451058490.9 46 1471 47 4R 49149.45 50350-44 51 11 12 13 19 784057 882075.6 10 490042 705660.3 441037.8 1980084 9800840 9800840 1-M49.N45 -N.49+045 -049+45 |--49M45 L50+144 -K50+44 1 Using the cost loaded schedule below, prepare a Cash Flow for the project. Total Estimate / Contract Cost: Retainage: $ 9,800,840 10% Direct Cost Billed to Month Subcontractor Equipment Labor S Materials Owner (Includes Overhead & Profit) 588,050 784,067 1 6,420 8,560 2 3 10,700 980,084 4 1,176,101 5 15,300 20,400 25,500 30,600 35,700 38,250 30,600 25,500 20,400 12,750 106,020 141,360 176,700 212,040 247,380 265,050 212,040 176,700 141,360 88,350 379,200 505,600 632,000 758,400 884,800 948,000 758,400 632,000 505,600 316,000 12.840 14.980 16.050 12.840 10.700 1,372,118 1,470,126 6 7 1,176,101 980,084 8 9 8,560 784,067 10 5,350 490,042 Totals 255,000 1,767,000 6,320,000 107.000 9,800,840 A B D E H Attachment B Retention rate: 0.1 0.1 0.1 0.1 0.1 Month Gross Pay Request to Owner 1 588050 2 784067 3 980084 4 1176101 5 1372118 Labor Material Subcontractor Equipment 15300 106020 379200 6420 ESUM D8:011) 20400 141360 505600 8560 ESUM E8:E11) 25500 176700 632000 10700 =SUMIFB:F11) 30600 212040 758400 12840 =SUMIGR:611) 35700 247380 884800 14980 ESUM H8:H11) Total L,M,S,E Costs P&O (16% of L,S,M and E above)) EROUNDID12*0.16.0) =ROUND(E12*0.16.0) =ROUND(F12*0.16,0) =ROUND/G12*0.16.0) =ROUNDTH12*0.16,0) 116 517 Retainage Held by Owner =D5*0.1 =E5*0.1 EF5*0.1 EGS *0.1 =H5*0.1 Receivables from Owner 0 E/D5-D17) E E5-E171 =[F5-F17) =IG5-G17) TECEEEEEEEEEEEEEEEEEEEEEEEEEEE Payment Outs by CM Labor Material Subcontractor Equipment P&O Recognized 208 =09 0 =D11 24 25 L26 127 28 29 30 =E8 -E9 =D10*0.9 -E11 D14 0.9 ESUM(E23:E27) =F8 =F9 =E10*0.9 -F11 -E14*0.9 =SUM(F23:F27) =G8 =G9 =F10*0.9 =G11 F140.9 =SUM(G23:627) H8 =H9 =G10*0.9 =H11 =G14*0.9 ESUM(H23:H27) Total Pay Outs =SUM(D23:D27) Net Cash Flow (After payment by owner of prior month) =D20-028 =E20-E28 =F20-F28 =G20-G28 EH20-H28 Cumulative Cash Flow =D32 =E324034 -F32+E34 =G32+F34 =H32+G34 35 20 A H Attachment B Retention rate: 10.1 10.1 10.1 0.1 0.1 0.1 0.1 Month Gross Pay Request to Owner 11 SBBOSO 2 784057 13 980084 14 1175101 5 1372118 6 1470126 7 1176101 1 2 21 3 4 51 6 7 B 91 9 10 11 12 13 14 15 16 17 18 191 Labor Material Subcontractor Equipment 15300 106020 379200 5420 SUMD8:011) 120400 141350 SO5600 8560 SUM[E8.011) 25500 176700 1632000 10700 |-SUM F8.F11] 30500 212040 758400 12840 -SUMIGR:G11 35700 247380 884800 14980 SUM(H8:11) 38250 |265050 948000 16050 -SUM (18:111) 20600 212040 758400 12840 -SUM8:11) Total L.MS, E Costs P&O (16 % of L.S.M and E above) -ROUND D12*0.160 ROUND E12*0.16 01 EROUND F12*0.16.00 EROUNDIG120.160 -ROUNDH120.16,0) -ROUND 1120.16,0) LROUND 1120.160 Retainage Held by Owner -50.1 * ES*0.1 -F5*0.1 -G5*0.1 =H5*0.1 -15*0.1 -15*0.1 20 Receivables from Owner =(D5-D17) -IES E17) {F5-F17) -G5 G17 -H5 H17) (15-117 Payment Outs by CM 21 22 23 24 25 26 27 28 29 30 Labor Material Subcontractor Equipment P&O Recognized -DS -D9 10 1-011 -EX -E9 -D10*0.9 -E11 ED14 0.9 -SUME 23:E271 =F8 -F9 -E10*0.9 -F11 -E1490.9 SUM/F23:F27) -G8 -69 -F10*0.9 -611 -F14*0.9 ESUMIG23:G27) -HS |--19 -610*0.9 -H11 G14*0.9 SLIMH23:27 18 -19 -H10*0.9 =111 1-18 -19 -110'0.9 11 11409 SUM(J23:27) -H14*0.9 SUM|123:127) Total Pay Outs SUMD23:D27) 31 Net Cash Flow (After payment by owner of prior month) 32 33 -D20-028 -E20 E28 -F20-F2R =G20 G2 H20-H2 -120-28 -120-128 341 Cumulative Cash Flow -D32 =E32+034 F32E34 -632F34 |-H32+634 132+H34 -J32+134 35 36 37 38 39 40 41 Guide to Setting Up Chart as "S* Curve 42 Month Gross Pay Request to Owner Receivables from Owner 5RROSO 784067 529245 980084 705660.3 1176101 882075.6 1372118 1058490.9 1470126 1234906.2 1176101 13231134 Convert above to cumulative 431 441 45 46 471 48 49 50 51 52 53 54 IC Cumulative Receivables from Owner Cumulative Gross Pay Request to Owner |-049+E45 D50-44 E49+F45 ener -E5D+F44 -F49+G4S =F506144 -649-4S -650H4 H49+145 -H50-144 =49145 -150+144 -D44 K M N 0 P 9 10.1 10.1 Check 784057 10 490042 -DS+ES+FS+G5+HS+15+15+5+5+M5 20.1 3 A 18 5 980084 6 17 8 25500 9176700 10632000 11 10700 12 -SUM(K8:11) 13 14 =ROUND K12*0.16,0) 15 16 17=K5*0.1 KS 18 20400 141300 505600 8560 -SUML8:11 12750 88350 316000 5350 SUMM:M11) -D8E8FGHAIN:8KB+8+MB -D9+E9F9+G9+H9+19+9+9+9+M9 -D10+E10+F10+G10+H10+120+JIO+K10+L10MIO -D11-E11+F11+G11+H11+11+11+11+L11+M11 -D12+12+F12+G12 H12+12+12+12+12M2 -ROUND(L12*0.16,0) =ROUND M12*0.16,0) -D14+E14+F14+G14+H34+14+J14+14+M14+L14 -08+09+010+011+014 --L5*0.1 SMS 0.1 SUMD17:N17) 20-05 217) - ) IEKS K17 (15-17) LIMS MITI 980084 =D20+20+F20+20+20+20+20+20+L20+M20+N20+P20 Retainage Payment 21 22 231-KR 241-9 -Q100.1 -L8 1-19 -K10*0.9 * |--11 =K14*0.9 SUML23:1271 251-110"0.9 26-K11 27-1140.9 28 SUM(K23:27) 29 30 -MA T-M EL100.9 |-MIL |=L14*0.9 -SUMM23:M27) -N8 0 -M10*0.9 ENAI PP14 0.9 -SUMN23-N27 EXD23+23+F23.62 3+H2 3+12 3+23+23+23+M23+N23) -D24+24+F24+G24+H24+24+J24+K24+24+24+N24 ++ ED25+25+F25+G25+H25+25+25+25+25+25+N25 -D25+25+F26+G26+H26+126+126+26+L25-M26+N25 -D27-E27-F27+G27+H27+127+12 7+27+L27-M27+N27 -SUMQ23:027 -Q1490.1 980084 Retainage Held by Less Retainage In CM Payments to L.M.E.Overhead and Profit 31 -212900 L2O-L28 MZO-M28 I-N20 N28 EP17./P25+P27 SUM(032-P32) L32+K34 -M32+34 -N32-M34 =P32+034 =928+030031 This is included in the final payment of retainage. It represents the fact that the owner held retainage on labor, material and equipment, but you had to pay out the full amount. 32 -K20-K28 33 34-K32+134 35 36 37 38 39 40 41 42 438 44980084 451058490.9 46 1471 47 4R 49149.45 50350-44 51 11 12 13 19 784057 882075.6 10 490042 705660.3 441037.8 1980084 9800840 9800840 1-M49.N45 -N.49+045 -049+45 |--49M45 L50+144 -K50+44 1
