Quantitative Decision Making – Lab1

Micromedia offers computer training seminars on a variety of topics. In the seminars each student works at a personal computer provided by Micromedia, practicing the particular activity that the instructor is presenting. Micromedia is currently planning a two-day seminar on the use of Microsoft Excel in statistical analysis. The projected fee for the seminar is only $600 per student. The cost for the conference room, instructor compensation, lab assistants, and promotion is $9600 for two days to be incurred by Micromedia. In addition, Micromedia rents computers for its seminars at a cost of $60 per computer per day. Each student uses one computer. So, the cost of renting a computer, incurred by Micromedia, will be $120 per student. You must find the breakeven point, the point when the cost is equal to revenue for Micromedia. In addition, you must resolve an additional situation of determining the number of students needed to be enrolled in order to make a profit of $9600.

When done, submit the report in Microsoft Word (no other format will be accepted) format using the Sample Report file (attached below in the attachements) as a template by the due date.

Please upload the Excel spreadsheet too.

Tasks to be completed:

1. Define the goal seeking problem to be solved in your own words in detail.

2. Develop a mathematical model for the total profit if x represents the number of students enrolled in the seminar.

3. Develop a Microsoft Excel spreadsheet to solve your problem.

4. Run the Excel’s Goal Seek tool to determine the breakeven point. Capture and include the part of the screen to explain your results.

5. Run the Excel’s Goal Seek tool to determine the number of students need to be enrolled in order to make a profit of $9600. Capture and include the part of the screen to explain your results.

6. Upload your Excel spreadsheet

4

 

DEPARTMENT OF TECHNOLOGY AND WORKFORCE LEARNING

ETECH 889-XX Quantitative Decision Making in Industry

 

 

 

 

 

 

 

 

 

Using Excel’s Goal Seek Tool

Report 1

 

 

By

Your Name

 

 

 

 

 

 

 

Date

 

(1) Problem Definition:

Lays Chips is in the business of making chips. The one-time setup cost of the required machinery is $3,000. The variable labor and material cost of producing a chip is $2. The selling price of each chip is $5. It is assumed that all the chips that produced will be solved. We have to determine the breakeven point, the number of chips to be produced that will generate the revenue equaling the total cost. Also, we have to determine the number of chips to be produced for a desired profit.

(2) Mathematical Model:

If the cost(cp) is a function of chips produced, then the cost-volume model for producing cp chips can be expressed by the equation:

cost(cp) = 3000 + 2*cp

If revenue(cp) represents a function of chips sold then the total revenue model can be represented by the equation:

revenue(cp) = 5*cp

Model for profit, if all the chips produced are sold, can be given by (cp stands for chips produced) the equation:

profit(cp) = revenue(cp) – cost(cp) = (5*cp) – (3000 + 2*cp) = –3000 + 3*cp

(3) Microsoft Excel Solution:

The Excel solution developed is given by the following screen captures:

 

(4) Excel’s Goal Seek:

Results from Excel’s Goal Seek Tool show that the breakeven point is achieved when 1000 chips are produced, as evident from the following screen captures:

  

(5) Excel’s Goal Seek:

Results from Excel’s Goal Seek Tool show that the profit of $1000 is achieved when 1333 chips are produced, as evident from the following screen captures:

  

Results from Excel’s Goal Seek Tool show that the profit of $5000 is achieved when 2667 chips are produced, as evident from the following screen captures:

  

Conclusion and Recommendations:

The breakeven point is met when 1000 chips are produced and sold.

A profit of $1000 is achieved when 1333 chips are produced and sold.

A profit of $5000 is achieved when 2667 chips are produced and sold.

(6) Please upload your spreadsheet file too.

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"