Excel Homework

The CEO of Burke Electronics, a well-established single store small business in Northern Virginia, is implementing a program to increase its overall top line and enhance the profitability of 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 offer more 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 (50)

2.     To make it a sustainable and meaningful solution, what additional constraints would you add. (10)

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 (25)

4.     Reduces overall cost (total cost of products in inventory) (15)