Some more questions related to the draw schedule, which I'd like to create without using macros if possible.

1. how can I autopopulate the number of periods in (A17:A28) and (G4:15) based on result in cell C7?

2. can the percentages in column H autofill based on value in B13 and corresponding % table below (A16:E28)?

3. can the percentage in column J change to 0% for the next period (column G) when the cumulative pay amount in L becomes greater than 50% of I16?

1. how can I autopopulate the number of periods in (A17:A28) and (G4:15) based on result in cell C7?

2. can the percentages in column H autofill based on value in B13 and corresponding % table below (A16:E28)?

3. can the percentage in column J change to 0% for the next period (column G) when the cumulative pay amount in L becomes greater than 50% of I16?

Anticipated Draw Schedule - Copy.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | J | K | L | |||

1 | project info | Test Job - Draw Schedule Projection * | ||||||||||||

2 | name | Test Job | calculated cell | Period | Draw | Retainage | Pay Amount | |||||||

3 | number | 12345 | user defined | Percentage | Amount | Percentage | Withheld | |||||||

4 | 1 | 4.0% | $ 40,000 | 5.00% | $ 2,000 | $ 38,000 | ||||||||

5 | project start | 12/31/2019 | 2 | 8.0% | $ 80,000 | 5.00% | $ 4,000 | $ 76,000 | ||||||

6 | project finish | 11/1/2020 | 3 | 14.0% | $ 140,000 | 5.00% | $ 7,000 | $ 133,000 | ||||||

7 | pay periods | 12 | 4 | 16.0% | $ 160,000 | 5.00% | $ 8,000 | $ 152,000 | ||||||

8 | 5 | 14.0% | $ 140,000 | 5.00% | $ 7,000 | $ 133,000 | ||||||||

9 | contract total | 1,000,000 | numbers are good | 6 | 12.0% | $ 120,000 | 5.00% | $ 6,000 | $ 114,000 | |||||

10 | retainage percentage | 5.00% | 7 | 10.0% | $ 100,000 | 5.00% | $ 5,000 | $ 95,000 | ||||||

11 | reduce retainage at 50%? | 2.50% | 8 | 8.0% | $ 80,000 | 5.00% | $ 4,000 | $ 76,000 | ||||||

12 | 9 | 6.0% | $ 60,000 | 5.00% | $ 3,000 | $ 57,000 | ||||||||

13 | billing option (C, F, B, or T) | C | 10 | 4.0% | $ 40,000 | 5.00% | $ 2,000 | $ 38,000 | ||||||

14 | 11 | 2.0% | $ 20,000 | 5.00% | $ 1,000 | $ 19,000 | ||||||||

15 | custom | front | bell | tail | 12 | 2.0% | $ 20,000 | 5.00% | $ 1,000 | $ 19,000 | ||||

16 | period | C | F | B | T | RET | retainage release > | $ 50,000 | ||||||

17 | 1 | 4.0% | 4.0% | 4.0% | 4.0% | Totals | 1,000,000 | $ 1,000,000 | ||||||

18 | 2 | 8.0% | 8.0% | 8.0% | 8.0% | |||||||||

19 | 3 | 14.0% | 14.0% | 14.0% | 14.0% | * Projection based on 75% DD Budget | ||||||||

20 | 4 | 16.0% | 16.0% | 16.0% | 16.0% | |||||||||

21 | 5 | 14.0% | 14.0% | 14.0% | 14.0% | |||||||||

22 | 6 | 12.0% | 12.0% | 12.0% | 12.0% | |||||||||

23 | 7 | 10.0% | 10.0% | 10.0% | 10.0% | |||||||||

24 | 8 | 8.0% | 8.0% | 8.0% | 8.0% | |||||||||

25 | 9 | 6.0% | 6.0% | 6.0% | 6.0% | |||||||||

26 | 10 | 4.0% | 4.0% | 4.0% | 4.0% | |||||||||

27 | 11 | 2.0% | 2.0% | 2.0% | 2.0% | |||||||||

28 | 12 | 2.0% | 2.0% | 2.0% | 2.0% | |||||||||

29 | ||||||||||||||

30 | questions / notes | |||||||||||||

31 | do not want to use macros in workbook | |||||||||||||

32 | how can I autopopulate the number of periods in (A17:A28) and (G4:15) based on result in cell C7? | |||||||||||||

33 | can the percentages in column H autofill based on value in B13 and corresponding % table below (A16:E28)? | |||||||||||||

34 | can the percentage in column J change to 0% for the next period (column G) when the cumulative pay amount in L becomes greater than 50% of I16? | |||||||||||||

35 | ||||||||||||||

36 | ||||||||||||||

37 | ||||||||||||||

38 | ||||||||||||||

Sheet1 |

Cell Formulas | ||
---|---|---|

Range | Formula | |

G1 | G1 | =CONCATENATE(name," - Draw Schedule Projection *") |

H4:H15 | H4 | =B17 |

I4:I15 | I4 | =contract*H4 |

J4:J15 | J4 | =retainage |

K4:K15 | K4 | =I4*J4 |

B7 | B7 | =DATEDIF(EOMONTH(B5,-1)+1,EOMONTH(B6,0),"m")+1 |

C9 | C9 | =IF((contract<>I17)," numbers are off"," numbers are good") |

I17 | I17 | =SUM(I4:I15) |

L4:L15 | L4 | =I4-K4 |

L16 | L16 | =SUM(K4:K15) |

L17 | L17 | =SUM(L4:L16) |

A18:A28 | A18 | =IF((A17<$B$7),A17+1,) |

Named Ranges | ||
---|---|---|

Name | Refers To | Cells |

contract | =Sheet1!$B$9 | C9, I4:I15 |

retainage | =Sheet1!$B$10 | J4:J15 |