Solving for unknowns

[Peter]

Is there an excel function that will solve the following

A = Sales (Max Billable Days * Utilization * Per Diem)
B = Cost - Fixed plus perentage of Sales
D = Rate of Return (-15,-10,-5,0,5,10, 15)

What I am trying to solve for is the Per Diem at various rates of
utilization for the Rates of Return above.

Thanks,
Peter

 

[Kevin Stecyk]

Peter,

I will approach your problem from a different angle.

You two key items are: Per Diem rates and Utililization Rates.

I would simply create a 2 dimensional data table with Per Diem Rates on one
axis and Costs one the other.

I would use Profit as the output. (In order to use rate of return, you need
to make investments and there has to be cash flows over some time interval.
I don't see that information in your question.)

Have a look at XL's help for Data Table. And then just set up a 2D data
table and you should have lots of great information. If you graph it, it
will be helpful too because you can look at the "surface" map.

Hope that helps.

Regards,
Kevin

 

[Peter]

Kevin,

Thanks for the pointer to Data Tables. These look interesting.

Re: Your question on the investment. You are correct. My question should
have read Sales Margin not Rate of Return.

Thanks for the help.

Peter

 

[Kevin Stecyk]

Peter,

My pleasure. Thank you for letting me know that the assistance was of
value.

If you have any difficulty with the data table, please come back and someone
will assist.

They really are kind of cool when you first set them up.

Regards,
Kevin