The magazine is priced at $5 and production costs per magazine is $2. Also, if some magazines remain unsold, they have to be disposed off at $0.50. Based on these assumptions and feedback from the marketing department we wish to decide how many magazines should the publisher print to make an optimal profit.
Let's enter the relevant data into our Excel worksheet. Range names have been assigned to B1:B10:
B1=production_qty
B2=randno
B3=demand
B4=Unit_production_cost
B5=unit_sales_price_price
B6=Unit_disposal_cost
B7=revenue
B8=tot_prod_cost
B9=total_disp_cost
B10=profit
Range D2:E5 has been assigned the name 'data'.
In the Excel worksheet cell range A15:A1014 we enter the numbers 1 to 1000 using the fill feature. In the home tab look for the 'Editing' group, click on 'Fill' and choose 'series'. In the series dialog box, enter a step value of 1 and a stop value of 1000. In the 'Series In' area select the 'columns' option and in the 'Type' select 'Linear' and finally click 'OK'. The numbers are automatically filled.
Next we enter our possible production quantities 10000, 20000,40000, 50000 and 60000 in Excel cells B14:G14. We now wish to calculate the profit for each iteration and each production amount. In the cell A15 we place the formula '=B10' which represents the profit.
Now using a two-way table as shown ealier also, we select A15 to G1014, click on data tab, select what-if from the Data Tools group, then 'Data Table...' and our production quantity 'B1' as the row input cell and we select 'J15' as the column input cell. This column input cell is, of course, blank. After you click 'OK', Excel simulates the 1000 demand values for each order quantity.
How does this happen? Let's take the example of the cell range C15:C1014. For every cell, Excel uses the value of 20000 in cell C14. In C15 the column input cell value of 1 is placed in the blank cell and the random number in cell b2 recalculates. Now the profit calculation takes place according to our formulas. This profit is then placed in C15. Next the column input value of 2 is placed in the blank cell, the random number in B2 and the profit in b10 recalculate. The corresponding profit from b10 is placed in cell C16. And so on.
We can also compute the mean profit in cell B12 by using the formula '=AVERAGE(B15:B1014)'. We can also copy the formula from B12 to C12:G12 and find out the mean profit for each produced quantity.
Next we can find out the standard deviation of our simulated profits using the formula '=STDEV(B15:B1014)' in cell B13 and using the copy function we can compute the standard deviations for all our simulated profits.
When you press F9 from the keyboard all the 1000 iterations are again simulated and younotice in our example in the mean values that production of 40000 units gives us maximum profit. |