Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using solver to complete a table
I've been taking some econmics classes. Our teacher has showed us some problems using solver to complete tables. Wondering if anyone out there can help. We have a company who uses units of labor (L) and material (m) to produce a certain quantity of output (q). The price of labor is $1 per unit of L and the price of materials is also $1 per unti of M. The prduction function is Q=L(raised to 4th power)XM(raised to 6th power)...sorry don't know how to type in exponents. Anyway, we're basically given a table (spreadsheet) with Q as the first column..labled 1 down to 9. Then column 2 is L, Column 3 is M, and then the final column is Total Cost. Basically, I'm trying to find the cost minimizing quantities of L and M that should be used to produce each quantity of output. So far, I've had little luck using calculus. How can this be done using solver? Thanks in advance! -- justhrowit ------------------------------------------------------------------------ justhrowit's Profile: http://www.excelforum.com/member.php...o&userid=31372 View this thread: http://www.excelforum.com/showthread...hreadid=510645 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using solver to complete a table
I've been playing with this a bit. I guess I'm just unsure on how to enter the formulas into the cells. I'm sure there are many other things I'm failing to comprehend here. I just end up getting 1 for all the cells. J -- justhrowit ------------------------------------------------------------------------ justhrowit's Profile: http://www.excelforum.com/member.php...o&userid=31372 View this thread: http://www.excelforum.com/showthread...hreadid=510645 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using solver to complete a table
just want to bring this to the top! -- justhrowit ------------------------------------------------------------------------ justhrowit's Profile: http://www.excelforum.com/member.php...o&userid=31372 View this thread: http://www.excelforum.com/showthread...hreadid=510645 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using solver to complete a table
Patience, it sometimes takes a while for these people to come up with solutions. I didn't find the calculus of this problem to be that difficult: TC=$1*M+$1*L Q=L^4*M^6 L=(Q*M^-6)^1/4: Assume L must be 0 so that negative root is ignored. TC=$1*M+$1*(Q*M^-6)^1/4 Take derivative, set equal to 0, solve for M, then obtain L from 3rd eqn, then check to make sure this represents a minimum. Solver can obtain the same results, it's just a little more tedious, because you either have to manually call solver for each row, or you need to write a VBA Sub procedure that will loop through the rows and call solver for you. Either way, the basic setup is: L=(Q*M^-6)^1/4 M=initial guess for M TC=$1*M+$1*L Set solver to minimize TC by changing M. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=510645 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using solver to complete a table
HAHA...Patience has never been my strong suit! I'm going to hit this and see if I can get this to work! Your help is VERY MUCH Appreciated! Jay MrShorty Wrote: Patience, it sometimes takes a while for these people to come up with solutions. I didn't find the calculus of this problem to be that difficult: TC=$1*M+$1*L Q=L^4*M^6 L=(Q*M^-6)^1/4: Assume L must be 0 so that negative root is ignored. TC=$1*M+$1*(Q*M^-6)^1/4 Take derivative, set equal to 0, solve for M, then obtain L from 3rd eqn, then check to make sure this represents a minimum. Solver can obtain the same results, it's just a little more tedious, because you either have to manually call solver for each row, or you need to write a VBA Sub procedure that will loop through the rows and call solver for you. Either way, the basic setup is: L=(Q*M^-6)^1/4 M=initial guess for M TC=$1*M+$1*L Set solver to minimize TC by changing M. -- justhrowit ------------------------------------------------------------------------ justhrowit's Profile: http://www.excelforum.com/member.php...o&userid=31372 View this thread: http://www.excelforum.com/showthread...hreadid=510645 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
solver table | Excel Discussion (Misc queries) | |||
Please Help - Trying to use one table to complete another | Excel Discussion (Misc queries) | |||
Solver Table missing in Data Menu of Excel2003. Where is it? | Excel Worksheet Functions | |||
Pivot table : Excel cannot complete this task with available resou | Excel Worksheet Functions |