The CEO of Burke Electronics, a well-establishedsingle store small business in Northern Virginia, is implementing a program to increase its overall top line and enhance the profitabilityof the concern. BE (Burke electronics) has hired you as a consultant for your expertise in managing multi-product companies and solving their issues through the use of linear programming and related Excel tools.
BE currently carries 4 product lines:
• PCs (Sony, Toshiba, HP etc.),
• Tablets (Ipad, Nexus etc.)
• Phones (Both Ios and Android)
• Chargers & Interface devices
BE's unique business model allows it to sell all products in each product lines at a fixed rate. It sells all PC's at an unit price of $ 450, Tablets at an unit price of $ 540, Phones at an unit price of $ 400 and Chargers & Interface devices at $ 35 apiece.
As with any small business, BE has some real constraints as well. BE cannot offermore than 900 Tablets and Phones (together);BE can only offer a maximum of 500 PCs. Additionally, BE has to offer a minimum of 1200 Chargers and Interface devices. The maximum and minimum total products that the store can carry are: 5000 and 2300 respectively. It also has to offer a minimum of 250 phones in its store.
1. Setup a solving structure in MS Excel for BE to get the correct product mix such that BE can maximize its revenue
2. To make it a sustainable and meaningful solution, what additional constraints would you add.
BE has also recently established that the average costs for each product line are as follows:
• PCs (Sony, Toshiba, HP etc.) - $ 375
• Tablets (Ipad, Nexus etc.) - $ 250
• Phones (Both Ios and Android) - $ 235
• Chargers & Interface devices - $ 30
Using the structure you have created, the existing constraints and the MS Excel tools available, can you propose a new product mix that programmatically:
3. Maximizes overall profits
4. Reduces overall cost (total cost of products in inventory).