Excel 2010

Read all questions and instructions carefully.

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)

Submission Instructions:

· Submit your homework in one Excel file into Blackboard (http://mymason.gmu.edu/) by Mon, Nov 9th, 7:19PM (Eastern Standard Time). Use 3 different sheets to respond to the different scenarios in Q1, Q3 & Q4.

· For each question, please document your steps elaborately.

· Late submission is allowed, but there will be 10% penalty per day of delayed submission.