Could you please help me work through the calculation for this simple but tricky situation? As the below situation changes, I need to calculate BOND, which is 3% of the TOTAL BID PRICE (which includes the cost of the bond). A seemingly simple calculation that I can't work through without a circular reference. The situation I'm trying to solve for is if the Total Direct Costs and indirect costs increase - how do I calculate BOND on a spreadsheet? Note that the bond is calculated last, despite it being up earlier in the calculation.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Tricky Calc without Circular References
- Thread starter cryan
- Start date
Here is a little more info on how the calculation is usually structured or laid out in Excel along with my question - the Direct costs are $2,301.29, the Bond is $98.76 (or 3% of the total) for a Subtotal of $2,400.06. Then the Overhead is 20.55% or $472.98 of the Subtotal, and the Fee is 15.11% of the Subtotal + the Overhead equalling $419.10 for a Total of $3.292.14. How do I calculate the Bond if the direct costs increase?
Upvote
0
dreid1011
Well-known Member
- Joined
- Jun 4, 2015
- Messages
- 3,085
- Office Version
- 365
- Platform
- Windows
Perhaps this (formula in B8):
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 2 | Total Direct Cost | 1800 | 0.539455 | |||
| 3 | ||||||
| 4 | Total DC Adds/Cuts | 0 | 0 | |||
| 5 | ||||||
| 6 | Total Indirect Cost | 544.52 | 0.163191 | |||
| 7 | ||||||
| 8 | Total Bond | 100.101 | 0.03 | |||
| 9 | ||||||
| 10 | Total Overall Cost | 2444.621 | 0.732646 | |||
| 11 | ||||||
| 12 | Total Overhead | 472.98 | 0.141751 | |||
| 13 | ||||||
| 14 | Total Profit | 419.1 | 0.125603 | |||
| 15 | ||||||
| 16 | Total Margin | 892.08 | 0.267354 | |||
| 17 | ||||||
| 18 | Total Bid Price | 3336.701 | ||||
Sheet1 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2 | D2 | =B2/B18 |
| D4 | D4 | =B4/B18 |
| D6 | D6 | =B6/B18 |
| B8 | B8 | =(SUM(B2,B4,B6,B12,B14)/97)*3 |
| D8 | D8 | =B8/B18 |
| B10 | B10 | =SUM(B2:B8) |
| D10 | D10 | =B10/B18 |
| D12 | D12 | =B12/B18 |
| D14 | D14 | =B14/B18 |
| B16 | B16 | =SUM(B12:B14) |
| D16 | D16 | =B16/B18 |
| B18 | B18 | =SUM(B16,B10) |
Upvote
0
@d
This works great until I have to calculate Overhead and Profit. Overhead is 20% of the Total Overall Costs (which includes the bond) and Profit is 15% of Total Overall Cost + Overhead. That's where the circular reference starts to get me.
Upvote
0
No. Not the 20% and 15%.
Upvote
0
