MIS 301 Introduction To Business Information Systems Spring

Mis 301 Introduction To Business Information Systems Spring 2014du

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. 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 (.

Reduces overall cost (total cost of products in inventory) (15) Submission Instructions: · Submit your homework in one Excel file into Blackboard ( 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 Find Salt TO: Bank of America FROM: Hind David DATE: March 16, 2016 SUBJECT: Bank loan Topic and Purpose “Find Salt†is a food truck in appearance, but it is without wheels so it is a kiosk. This proposal acknowledges an issue related to the limited menu of the kiosk, and it proposes a document that will suggest and recommend food and beverages to add to the menu. And since this food truck does not move and it is located in United Arab Emirates where is weather is very warm and hot.

In this proposal, I am asking for bank loan to be a franchiser for this food truck, but at in-door location and diverse menu. Scope This proposal will outline and justify the forthcoming document generally. First the general nature of the document will be discussed; then the subsequent proposal will be outlined. Proposal The proposed document will describe various types of food that is recommend to be added to the menu. Identifying the franchised restaurant, its location, design, atmosphere and location.

Methodology Information about street food concept will be collected from existing sources like management journals and scholarship. Also, Find Salt fans will be surveyed to assess what food is their favorite and what is not. Also to see if they will visit if it was a restaurant in-door rather than just grab the food and go. Document Outline The proposed document will include the following specific sections and subsections. This outline is preliminary in nature and may be adjusted as information is gathered.

Introduction · Background about “Find Salt†· Current menu Franchise · The new menu · Location · Design · Atmosphere Conclusion · Amount of money needed · Suggestions Benefits The owner of Find Salt and the franchiser will benefit from the new adjusted menu and from the new concept of the food truck that is an in-door restaurant. Also, bank will benefit from the loan when they will be paid.

Paper For Above instruction

The assignment at hand involves creating an analytical and managerial framework for Burke Electronics (BE) using linear programming techniques in Excel to optimize product mix, revenue, and costs. The goal is twofold: first, to determine the optimal product assortment that maximizes revenue under given constraints; second, to identify a product mix that minimizes total inventory costs while maximizing profits. This necessitates setting up a structured model that accounts for revenue, costs, production limits, and demand constraints, with future considerations added for broader operational sustainability.

Introduction and Background

Burke Electronics operates as a small, single-store business in Northern Virginia, managing four primary product lines: PCs, Tablets, Phones, and Chargers & Interface devices. The company’s unique pricing model fixes the unit prices of each product line, simplifying revenue calculation but requiring strategic planning to maximize profitability within operational limits. Current constraints include maximum and minimum inventory levels, product line capacities, and overall total product offerings. This environment demands a systematic approach using linear programming to determine the optimal product mix for maximizing revenue and minimizing costs.

Formulating the Model in Excel

To establish the linear programming model, Excel’s Solver tool serves effectively. The decision variables represent the quantity of each product line to stock: P for PCs, T for Tablets, Ph for Phones, and C for Chargers & Interface devices. The objective function for revenue maximization is defined as:

• Maximize Z = 450P + 540T + 400Ph + 35C

Subject to the constraints:

• Pc capacity: P ≤ 500
• Tablets and Phones combined: T + Ph ≤ 900
• Minimum Chargers & Interface: C ≥ 1200
• Maximum total products: P + T + Ph + C ≤ 5000
• Minimum total products: P + T + Ph + C ≥ 2300
• Minimum Phones: Ph ≥ 250

Additional logical constraints ensure non-negativity:

• P, T, Ph, C ≥ 0

Additional Constraints for Sustainability

To craft a sustainable and practical plan, further constraints could include inventory turnover rates to prevent overstocking, seasonal demand considerations, supplier lead times, and possible storage limitations. Incorporating minimum profit margins per product line might also prevent low-margin products from dominating the mix. Constraints about cash flow and liquidity could help prevent over-investment in inventory, ensuring operational stability.

Cost and Profit Optimization

Using the established model, the primary goal is to maximize profit, taking into account product costs:

• Cost per Product Line: PCs - $375, Tablets - $250, Phones - $235, Chargers - $30

Profit per unit is calculated as selling price minus cost:

• PCs: $450 - $375 = $75
• Tablets: $540 - $250 = $290
• Phones: $400 - $235 = $165
• Chargers: $35 - $30 = $5

The objective function for profit maximization becomes:

• Maximize Z = 75P + 290T + 165Ph + 5C

Running Solver with this objective under the same constraints yields an optimal product mix. Generally, the model suggests prioritizing high-margin items such as Tablets and Phones, while carefully balancing the inventory of PCs and Chargers to maximize profit margins without breaching constraints.

Cost Reduction Strategy

To minimize the total inventory cost, the model can be adapted by focusing on product costs rather than revenues. This involves configuring decision variables to minimize:

• Total Inventory Cost = 375P + 250T + 235Ph + 30C

The challenge is to satisfy demand and operational constraints while reducing costs. This approach is particularly relevant when inventory holding costs, warehousing limitations, or cash flow considerations are critical. The solution prioritizes stocking fewer high-cost items or adjusting quantities to keep overall inventory costs within budget while meeting minimum sales constraints.

Summary and Recommendations

The use of linear programming in Excel enables Burke Electronics to determine optimal product mixes aligned with strategic objectives—whether maximizing revenue, profit, or minimizing costs. The model systematically incorporates key constraints, including capacity limits, minimum and maximum inventory levels, and sales targets. The future extension of this model could include dynamic constraints such as seasonal fluctuations, supply chain disruptions, and market trends. Regular updates and scenario analyses using Excel’s Solver can help BE adapt in real-time to changing business environments.

In conclusion, employing mathematical modeling and strategic constraints provides a structured methodology for small retail businesses like Burke Electronics to optimize operational decisions, ultimately supporting sustainable growth and profitability.

References

• Hillier, F. S., & Lieberman, G. J. (2010). Introduction to Operations Research. McGraw-Hill Education.
• Bertsimas, D., & Tsitsiklis, J. N. (1997). Introduction to Linear Optimization. Athena Scientific.