MBA Program

Information Technology for Management Support

Instructor Vagif Salimov

Assignment 2.

Objectives : The exploration of methods of development and using DSS and ESS.

You have to develop simulating models for DSS and ESS systems .

To perform this assignment you should use MS EXCEL software.

Assume you work as a senior manager at a production firm, which manufactures 3 products. You should determine the forecast of demand potential for each product using statistical methods (we suppose that the market is being under stationary conditions and we can use statistical methods !!! ). Use the forecast functions embedded to MS Excel.

You have the demand’s statistical data for the last 5 years (fill in the table with your own data)

1.  Using WORKSHEET1 develop the Executive support system ( ESS) for forecasting estimated demand potential of each product.

Years / Products
A / B / C
2003
2004
2005
2006
2007
2008
2009 / ? / ? / ? / ? / ?

Calculate the forecast of demand potential for each product for 2012 year, using all forecasting methods embedded in MS Excel . Create graphs of statistical data and the trend using feature from Chart Wizard subsystem. The calculated forecast values will be used in the second part of this assignment.

2.  Using WORKSHEET2 develop the Decision support system ( DSS) for performance profitability analysis .

Assume you work as a middle manager in this firm. One of your major functions is the profitability analysis ( the determining of the optimal selling price, it means that the profit get to maximum value)

Suppose the production process have 2 items of fixed cost and 4 items of variable cost ( the values of each sensitivities set yourselves !) . At first calculate the actual demand DEMANDI =DEMAND POTENTIALi –DEMAND_SENSITIVITYi *SELLING_PRICEi (set the value of selling price yourselves) for each product. Calculate the variable cost using formula VCk=DEMANDI* KSi , where KSi – sensitivity for each resource. Similarly calculate the revenues for each product:

REVi=DEMANDI* SELLING _PRICEi

Calculate the TOTAL COST, TOTAL REVENUE and PROFIT.

Profit=Total Revenue - Total Cost

a) Using the Data Table feature of MS EXCEL determine the optimal selling price, which provides the MAX profit.

b) Use the IF instruction for solving indicated above problem.

Create the graph profit=f(selling price)

c) Make the breakeven analysis ( determine the selling price when PROFIT=0)

Recommendation : Use GOAL SEEK or SOLVER feature (MS EXCEL)

Send me results in e-version (xls file)

under name lastname_firstname_assign2.xls (xlsx)

Assignments must be turned in due date – 18,04.2016