in excel, please explain steps

Currently the plant operates for 40 hours per week.

Problem 1: Resource Allocation Currently the company produces 25 cases of each peanut butter type per week. The plant manager, Walter Sobchak, wants to know if this production plan is the most profitable. He has hired you to analyze his operations and determine what the optimal production plan should be.

Problem 1.0: Calculate the profit and machine utilization %s based on the current plan of 25 units of each type of peanut butter per week. (SET UP THE CALCULATIONS YOU WOULD NEED TO SOLVE THE PROBLEM BUT THEN JUST PLUG IN 25 FOR EACH PRODUCT'S PRODUCTION LEVEL.)

Problem 1.1: Use solver to determine the optimal weekly production plan for the Lebowski Peanut Butter Company. How does this plan to the current production plan (25 cases of each type per week)? In your setup include the machine utilization percentages.

Problem 1.2: Walter spots two potential problems in your plan, there is no way he can sell more than 100 cases of regular per week. Also, the firm must produce at least 20 units of low fat per week to fulfill contractual obligations. Redo your analysis with these two additional constraints.

Problem 1.3: Mr. Sobchak looks over the findings and tells you that it is not possible to sell fractions of a case of peanut butter. He asks if you can resolve the problem but this time make sure the production levels are integer values. Keep the constraints from problem 1.2 (this problem may take a while for the computer to solve).

Problem 2: Value of Machines A sales associate from Musshafen Machinery stops by the factory with some refurbished machines, which they are trying to lease on a weekly basis. LPB can only add one machine to their factory due to limited space. LPB will only lease a new machine if it is profitable to do so.

You can rent a crusher for $125 per week. You can rent a mixer for $125 per week. You can rent a separator for $300 per week. You can rent an extruder for $250 per week.

You will need to resolve problem 1.1 four more times; 2.1 add one crusher (dont change anything else from the 1.1 table) 2.2 add one mixer (dont change anything else from the 1.1 table) 2.3 add on separator (dont change anything else from the 1.1 table 2.4 add one extruder (dont change anything else from the 1.1 table)

Summary Tables In a table compare your findings from Problems 1.0, 1.1, 1.2, and 1.3. The most interesting data for Walter are the production plans themselves, and the weekly profit the company would make from each plan. In a separate table show how efficiently the company is using their resources in 1.0, 1.1, 1.2, and 1.3 (compute the percentage of time each machine is being used).

Summarize the scenarios from Problem 2 in their own tables. Compare the production plans and profit levels from the four scenarios to the optimal solution from Problem 1.1. Also, create a machine utilization table for the scenarios from Problem 2. Should Lebowski rent another machine? If so which one? Insert a text box to state and justify your choice, also be sure to discuss this issue in the memo.

Problem 3: Economic Order Quantity In a new worksheet of your own design, create visual basic macros for calculating economic order quantity, orders per year, and total stocking cost. The total stocking cost macro should take arguments k, d, h, eoq, and opy. LPB estimates their annual demand (D) for peanuts is 168,120 TONS. Determine LPBs EOQ and OPY values for ordering peanuts if inventory costs are $0.04 per TON per year. LPBs current ordering system results in a charge of $20 per order but they are considering a new ordering system that would lower ordering costs to $5 per order, the new system will cost $400.00 per year to implement and operate. Set up a small table comparing EOQ, OPY, and total stocking cost for the existing ordering system versus the new stocking system. Create a bar or column chart that compares the EOQ from the old ordering system to the new ordering system, make a second chart that compares OPY from the two systems, make a third chart comparing total stocking cost for the two systems. In the memo discuss how much the new ordering system would change the EOQ, OPY, and total stocking cost values. Should they adopt the new ordering system?

type of peaunut butter
machine type	number of machines	regular	organic	low fat	crunchy
crusher	5	0.60	0.65	0.00	0.25
mixer	5	0.35	1.10	0.25	0.50
sep

In the memo be sure to discuss from which state does LPB acquire most of its peanuts? In which state are peanuts cheaper?

erator	3	0.45	0.35	0.50	0.40
extruder	7	0.70	1.40	0.60	0.30
profit per unit	NA	$18.00	$25	$12.00	$15.00