Check out our brand new Local Poker Communities! Get updates and interact with poker players in your area.
Visit the United States Poker Community | Visit the California Poker Community | Read more about the Launch of P5s Local

MS excel, econ people...please help (profit maximization)

Reply to Thread
  1. amac1212 amac1212 is offline

    Peoria, IL
    United States

    Posts: 94
    Joined: Sep 06

    Mar 10th, 2009 (3/10/2009 11:48pm)

    Will compensate for whoever can help me/give me answer, I dont know if mine is right. Money for right answer and explanation thats satisfies me. Need by 8am tomorrow. thanks

    <SPAN>This project involves packaging nails so that you make the most profit possible (this is a profit maximizing problem).<SPAN> </SPAN>Say you have six types of nails and can make as many as you need of each.<SPAN> </SPAN>These are:</SPAN>

    <SPAN>4-inch nails</SPAN>

    <SPAN>3.5-inch nails</SPAN>

    <SPAN>3-inch nails</SPAN>

    <SPAN>2.5-inch nails</SPAN>

    <SPAN>2-inch nails</SPAN>

    <SPAN>1.5-inch nails</SPAN>

    <SPAN>The cost of making each type of nail depends on how big a nail it is.<SPAN> </SPAN>Those costs and selling prices are listed in the table below along with the weights.<SPAN> </SPAN>The nails will be sold in boxes of up to 30 nails.<SPAN> </SPAN>There must be no more than 10, but no less than 5, of each of at least three types of nails in each box.<SPAN> </SPAN>The nails in each box should weigh no more than 20 ounces.<SPAN> </SPAN>Youre looking for the combination with the highest profit using a trial-and-error method.<SPAN> </SPAN>Use a spreadsheet to complete this project.<SPAN> </SPAN>Youll most likely find that you identify some promising paths to follow right away and will concentrate on those to reach the best one.</SPAN><SPAN> When you are satisfied you have maximized your profit, highlight the "winning" assortment of nails in the spreadsheet. Then save the spreadsheet as "YourLastNameHW2.xls" and send it as an attachment to this assignment.</SPAN>
    <TABLE cellSpacing=0 cellPadding=0 border=1><TBODY><TR><TD vAlign=top width=160><SPAN>NAIL</SPAN>
    </TD><TD vAlign=top width=160><SPAN>WEIGHT (in ounces)</SPAN>
    </TD><TD vAlign=top width=160><SPAN>COST (in cents)</SPAN>
    </TD><TD vAlign=top width=160><SPAN>SELLING PRICE (cents)</SPAN>
    </TD></TR><TR><TD vAlign=top width=160><SPAN>4-inch</SPAN>
    </TD><TD vAlign=top width=160><SPAN>1.00</SPAN>
    </TD><TD vAlign=top width=160><SPAN>4.0</SPAN>
    </TD><TD vAlign=top width=160><SPAN>8</SPAN>
    </TD></TR><TR><TD vAlign=top width=160><SPAN>3.5-inch</SPAN>
    </TD><TD vAlign=top width=160><SPAN>0.85</SPAN>
    </TD><TD vAlign=top width=160><SPAN>3.5</SPAN>
    </TD><TD vAlign=top width=160><SPAN>6</SPAN>
    </TD></TR><TR><TD vAlign=top width=160><SPAN>3-inch</SPAN>
    </TD><TD vAlign=top width=160><SPAN>0.70</SPAN>
    </TD><TD vAlign=top width=160><SPAN>3.0</SPAN>
    </TD><TD vAlign=top width=160><SPAN>5</SPAN>
    </TD></TR><TR><TD vAlign=top width=160><SPAN>2.5-inch</SPAN>
    </TD><TD vAlign=top width=160><SPAN>0.50</SPAN>
    </TD><TD vAlign=top width=160><SPAN>2.5</SPAN>
    </TD><TD vAlign=top width=160><SPAN>4</SPAN>
    </TD></TR><TR><TD vAlign=top width=160><SPAN>2-inch</SPAN>
    </TD><TD vAlign=top width=160><SPAN>0.25</SPAN>
    </TD><TD vAlign=top width=160><SPAN>2.0</SPAN>
    </TD><TD vAlign=top width=160><SPAN>3</SPAN>
    </TD></TR><TR><TD vAlign=top width=160><SPAN>1.5-inch</SPAN>
    </TD><TD vAlign=top width=160><SPAN>0.10</SPAN>
    </TD><TD vAlign=top width=160><SPAN>1.5</SPAN>
    </TD><TD vAlign=top width=160><SPAN>2</SPAN>
    </TD></TR></TBODY></TABLE>
  2. 360FLIP 360FLIP is offline

    Posts: 106
    Joined: Feb 09

    Mar 10th, 2009 (3/10/2009 11:48pm) in reply to amac1212

    screws > nails.

    /thread
  3. kmax9 kmax9 is offline

    South Bend, IN
    United States

    Posts: 343
    Joined: Feb 07

    Mar 11th, 2009 (3/11/2009 12:49am) in reply to 360FLIP

    i got 26 nails profit of 71 cents per box using 10 - 1 oz 10 - .85 oz and 6 of the .25 oz..
    Let me know if you wanna see my excel sheet.

    <table x:str="" border="0" cellpadding="0" cellspacing="0" width="130"><col width="130"> <tbody><tr height="21"><td height="21" width="130">Profit per ounce</td></tr><tr height="21"><td x:num="" align="right" height="21">4</td></tr><tr height="21"><td x:num="2.9411764705882355" align="right" height="21">2.941176471</td></tr><tr height="21"><td x:num="2.8571428571428572" align="right" height="21">2.857142857</td></tr><tr height="21"><td x:num="" align="right" height="21">3</td></tr><tr height="21"><td x:num="" align="right" height="21">4</td></tr><tr height="21"><td x:num="" align="right" height="21">5</td></tr></tbody></table>
  4. Leet8s Leet8s is offline usausausausausa

    New York City, NY
    United States

    Posts: 8,036
    Joined: Aug 06

    Mar 11th, 2009 (3/11/2009 1:14am) in reply to kmax9

    Lol i just did this stuff in competitive advantages where you just input stuff, run a solver and it does it for you. pure awesomeness. don't know how to do it btw, sorry.
     
  5. amac1212 amac1212 is offline

    Peoria, IL
    United States

    Posts: 94
    Joined: Sep 06

    Mar 11th, 2009 (3/11/2009 1:35am) in reply to Leet8s

    I got a better profit, of 71.5 cents. 10 at 4 inches 9 at 3.5 and 9 at 2inches. But what did u put in ur spreadsheet. Like i dont know what my professor is looking for in the excel spreadsheet.
    Thread Starter
  6. menticide menticide is offline

    Atlanta, GA
    United States

    Posts: 135
    Joined: Jan 06

    Mar 11th, 2009 (3/11/2009 1:44am) in reply to amac1212

    I could find the optimal solution for you using linear programming and solver in excel... would take a couple of minutes, but unfortunately i just reformatted and I havent installed it yet.

    Man I'm useful :\
  7. amac1212 amac1212 is offline

    Peoria, IL
    United States

    Posts: 94
    Joined: Sep 06

    Mar 11th, 2009 (3/11/2009 2:00am) in reply to menticide

    Wish i knew how to use those functions, let alone the basic sum function lol jk. But i dont know much. I think I got the answer but i dunno.
    Thread Starter
  8. menticide menticide is offline

    Atlanta, GA
    United States

    Posts: 135
    Joined: Jan 06

    Mar 11th, 2009 (3/11/2009 3:42am) in reply to amac1212

    Ok I ran it through excel and I came up with several optimal solutions:

    (In order from 4 inches to 1.5 inches):
    10 5 5 0 9 0
    10 9 0 0 9 0
    10 10 0 0 4 5
    10 6 0 5 9 0

    All of these solutions are optimal and lead to the same max. profit of 71.50 cents per box, so the solution that you came up with is optimal as well.

    I basically set an objective function to maximize profit represented by z = 4x_1 + 2.5x_2 + 2x_3 + 1.5x_4 + x_5 + 0.5x_6 subject to the weight and total # of nails per box constraints, using solver to put it all together and figure it out.

    I won't go into the technical details on how I set this up in excel since your teacher only wanted you to do trial and error, but I guess you could set up rows saying "Trial 1, Trial 2, Trial 3" etc. with random values for each size of nail, with the spreadsheet calculating the profit for each trial as you go, while changing the values that increase the profits the most for each successive trial or something like that.

    *shrug* :D
  9. Leet8s Leet8s is offline usausausausausa

    New York City, NY
    United States

    Posts: 8,036
    Joined: Aug 06

    Mar 11th, 2009 (3/11/2009 3:58am) in reply to menticide

     
    Originally Posted by menticide View Post

    I could find the optimal solution for you using linear programming and solver in excel...

    Obv this is how you do it.
     
  10. amac1212 amac1212 is offline

    Peoria, IL
    United States

    Posts: 94
    Joined: Sep 06

    Mar 11th, 2009 (3/11/2009 6:48am) in reply to menticide

    Menticide, thanks for the help! I really appreciate you taking out the time to do so.
    Thread Starter
  11. menticide menticide is offline

    Atlanta, GA
    United States

    Posts: 135
    Joined: Jan 06

    Mar 11th, 2009 (3/11/2009 11:47am) in reply to amac1212

     
    Originally Posted by amac1212 View Post

    Menticide, thanks for the help! I really appreciate you taking out the time to do so.

    check your pms please :)
Reply to Thread

Similar Threads