Evertop is a company that produces two types of metal stands for supermarkets. Model A is the standard version; model B is a heavy-duty version. Stands are manufactured through three major processes: process-1, process-2, and process-3. In the process-1, a large machine is used to cut standard sheets of metal into appropriate sizes. In the process-2, another machine bends the metal into shape. Process-3 involves joining the parts with a combination of soldering and riveting. Evertop's process-1 and process-2 are used for both models of stands. Separate process-3 departments are used for the final stage of production.
The file
Assignment 2-Data.xlsx
contains relevant data for Evertop. The production of one model A approximately requires 0.27 hour and 0.34 hour on the process-1 and process-2 machines, respectively. Similarly, the production of one model B requires 0.5 hours and 0.48 hour on the process-1 and process-2 machines, respectively.
Both the process-1 and process-2 machines can operate for 750 hours each month. Model A process-3 department has a monthly capacity of 1,500 units. The model B process-3 department has a monthly capacity of only 1,350 units.
Currently, Evertop is producing and selling 350 units of model A and 1,300 units of model B per month.
Model A stands are sold for $1,790, and model B stands are sold for $1,995. Evertop’s operation is fairly small in the industry, and management believes it cannot increase prices beyond these levels because of the competition. However, the marketing department believes that Evertop can sell as much as it can produce at these prices.
Notes on data calculations
The costs of production are summarized in the
Assignment 2-Data.xlsx
file. In this file, the data in the highlighted cells are given, whereas other values are calculated from these. The fixed overhead is distributed using activity-based costing principles. For instance, at current production levels, the process-2 machine spends 119 hours on model A stands and 624 hours on model B stands. The process-2 machine is used 743 hours of the month, of which 16.02% of the time is spent on model A stands, and 83.98% is spent on model B stands. The $94,000 of fixed overhead in the process-2 department is distributed as $15,058.80 (94,000×0.1602) to model A stands and $78,941.2 (94,000×0.8398) to model B stands. The fixed overhead per unit of output is allocated as $43.02 (15,058.80/350) for model A and $60.72 (78,941.2/1300) for model B.
In the calculation of the standard overhead cost, the fixed and variable costs are
added together, so that the overhead cost for the process-2 department allocated to a model A stand is $153 (43.02+110, rounded down to $153). Similarly, the overhead cost for the process-2 department allocated to a model B stand is $225.72 (60.72+165, rounded up to $226).
Develop an Excel spreadsheet linear programming model, as well as R codes for profit maximization. Compare the results from two software.