Excel MIS 301

MIS 301 – Introduction to Business Information Systems – Spring 2014

Due on Tuesday, March 31st, 11:59 PM EST

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 Tue, Mar 31st, 11:59PM (Eastern Standard Time). Use 3 different sheets to respond to the different scenarios in Q1, Q3 & Q4.

· This is an individual assignment. However, if you’d like, you may collaborate with no more than one classmate. Still, each should create and submit a separate file individually. In addition, both of the two students must mention whom they work with in the file. Otherwise, it will be considered an Honor Code violation and reported to Office for Academic Integrity immediately.

· For each question, please document your steps elaborately.

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

· This is based on the coffee cup example demonstrated in class