Complex Goal Seek or Solver Solution

ShanaVT

Board Regular
Joined
May 12, 2010
Messages
86
Hello, I need to solve for several cells to equal the same value based on changing a certain set of values. I have tried to use goal seek to do each one individually but I can't get it to work to solve for the whole group. Below is the data set that starts with P7 in cell B2 and DWC AR Billed in A2 and then that data set repeated to the right in red font showing the formulas. The values in blue font are what I need to solve for - I need all 4 of the DWC values to = 42.3 (P7, P8, P9 and Q3) and similarly I need all the DSO numbers to equal 66.0. The only values I can change to get the DWC and DSO to be 42.3 andf 66.0 respectively are the numbers in pink font. And there are ranges for what those numbers can be below. Is there anyway to use solver to get to an answer with this criteria or perhaps a macro I can could run? Thanks in advance for any help!

DWC AR BilledRange = 115,000 - 150,000
DWC AR UnbilledRange = 175,000 - 250,000
DWC Accounts PayableRange = 75,000 - 130,000

<colgroup><col><col></colgroup><tbody>
</tbody>
P7P8P9Q3
DWC AR Billed 125,000 121,825 126,363 126,363 125,000 121,825 126,363 =D2
DWC AR Unbilled 204,469 227,483 218,235 218,235 204,469 227,483 218,235 =D3
DWC AR Retention 2,091 1,916 1,848 1,848 2,091 1,916 1,848 =D4
DWC Accounts Receivable 331,560 351,224 346,446 346,446 =SUM(B2:B4) =SUM(C2:C4) =SUM(D2:D4) =D5
DWC Accounts Payable 103,459 110,520 108,804 108,804 103,459 110,520 108,804 =D6
DWC Def Rev 15,523 15,523 15,523 15,523 15,523 15,523 15,523 =D7
DWC Accr Res 79 79 79 79 79 79 79 =D8
DWC Accrued Liabilities 15,602 15,602 15,602 15,602 =B7+B8 =C7+C8 =D7+D8 =D9
Total Net Working Capital 212,500 225,103 222,041 222,041 =B5-B6-B9 =C5-C6-C9 =D5-D6-D9 D10
Prime Sales 175,827 149,004 152,844 477,676 175,827 149,004 152,844 =SUM(B11:D11)
DWC 42.3 42.3 40.7 42.3 =B10/B17 =C10/C17 =D10/D17 =E10/(SUM(B11:D11/SUM(B16:D16))
DSO 66.0 66.0 63.5 66.0 =B5/B17 =C5/C17 =D5/D17 =E5/(SUM(B11:D11/SUM(B16:D16))
Days in Period 35 28 28 35 28 28
Prime Sales/Days 5,024 5,024 5,024=B11/B16=C11/C16=D11/D16

<colgroup><col><col span="3"><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top