To define an optimization model in Excel you’ll follow these essential steps:

Organize the data for your problem in the spreadsheet in a logical manner.
Choose a spreadsheet cell to hold the value of each decision variable in your model.
Create a spreadsheet formula in a cell that calculates the objective function for your model.
Create a formulas in cells to calculate the left hand sides of each constraint.
Use the dialogs in Excel to tell the Solver about your decision variables, the objective, constraints, and desired bounds on constraints and variables.
Run the Solver to find the optimal solution.

Optimal use of Limited Resources for Managerial Accounting Mcom Delhi University

Companies usually have limited resources, such as limits on space, on the number of workers, or even on the machine capacity needed to produce goods. This reality means that in order to best use limited production capabilities, managers must choose which products to make and sell.

Managerial accountants use a simple technique of dividing contribution margin by a measure of the constrained resource to indicate which products squeeze the most profitability out of constrained resources.

Suppose Charlie’s Burger Restaurant is constrained by the size of its 1,000-square-inch cooking grill. Because the restaurant is open eight hours a day, Charlie’s has a maximum of 8,000 square-inch-hours of grill time available per day (1,000 square inches times eight hours). To keep things simple, call these units of grill time.

Suppose that one medium-cooked Deluxe Burger requires eight units of grill time. Charlie’s maximum capacity of medium-cooked burgers, therefore, equals the total capacity of 8,000 units of grill time divided by the 8 units needed for each burger. Therefore, in theory at least, Charlie’s can produce a maximum of 1,000 medium-cooked Deluxe Burgers a day.

As long as Charlie’s customers demand fewer than 1,000 Deluxe Burgers a day, capacity is no problem. Charlie’s cook can make as many burgers as customers order.

However, when a company doesn’t have enough capacity to meet its needs, it must carefully consider the best way to use its constrained resource. Here, if Charlie’s customers demand more than 1,000 burgers a day (or, for that matter, more food at any one time than can fit on the cooking grill during that time span), managers must decide how to allocate the restaurant’s limited cooking space.

Optimal use of Limited Resources for Managerial Accounting Mcom Delhi University

Following this technique helps you choose which products deliver the most profitability.

Say that Charlie’s Burger Restaurant has four total items on the menu, with the following contribution margins and grill time requirements per order:

First, find each product’s contribution margin per unit of constrained resource by dividing each product’s contribution margin per unit by the amount of constrained resource needed to make it. For example, divide the contribution margin of each product by the number of units of grill time needed.

If Charlie’s cook is making Juicy Grilled Chicken, each unit of grill time yields only $0.33 worth of contribution margin. When he’s cooking Puffy Hot Dogs, each unit of grill time yields $0.67. Cooking Deluxe Burgers results in $0.56 of contribution margin per unit of grill time.

Therefore, if grill time is constrained, Charlie’s wants to sell as many Puffy Hot Dogs as possible because they have the highest contribution margin per unit of constrained resource (a staggering $0.67 per unit of grill time).

The cook should then dedicate any remaining grill space to Deluxe Burgers ($0.56 per unit of grill time). If the restaurant can meet the demand for hot dogs and deluxe burgers, the cook can make room for the Juicy Grilled Chicken. (If not, he may just want to pretend he’s run out of chicken.)

Now don’t forget the Vegetarian Pasta Primavera. Because this dish doesn’t require any time on the grill at all, it offers the most effective way around the constraint. Even though the grill limits your production and sales of hamburgers, chicken, and hot dogs, it can’t limit your production and sales of Vegetarian Pasta Primavera. It’s the perfect candidate for Special of the Day.