tailieunhanh - Microsoft Excel 2010: Data Analysis and Business Modeling phần 9

Bây giờ đi ô và thay đổi sản phẩm tháng hai bán hàng 263-363. Chú ý rằng trong bảng hợp nhất, các mục nhập cho tháng hai sản phẩm A doanh số bán hàng cũng đã tăng . Sự thay đổi này xảy ra bởi vì các nguồn dữ liệu | 556 Microsoft Excel 2010 Data Analysis and Business Modeling FIGURE 69-6 Using the Series dialog box to fill in the trial numbers 1 through 1 000. Next I enter the possible production quantities 10 000 20 000 40 000 69 000 in cells B15 E15. I want to calculate profit for each trial number 1 through 1 000 and each production quantity. I refer to the formula for profit calculated in cell C11 in the upper-left cell of the data table A15 by entering C11. You can now trick Excel into simulating one thousand iterations of demand for each production quantity. Select the table range A15 E1014 and then in the Data Tools group on the Data tab click What If Analysis and then select Data Table. To set up a two-way data table choose the production quantity cell C1 as the row input cell and select any blank cell I chose cell I14 as the column input cell. After you click OK Excel simulates one thousand demand values for each order quantity. To understand why this works consider the values placed by the data table in the cell range C16 C1015. For each of these cells Excel uses a value of 20 000 in cell C1. In C16 the column input cell value of 1 is placed in a blank cell and the random number in cell C2 recalculates. The corresponding profit is then recorded in cell C16. Then the column input cell value of 2 is placed in a blank cell and the random number in C2 again recalculates. The corresponding profit is entered in cell C17. By copying from cell B13 to C13 E13 the formula AVERAGE B16 B1015 you can compute average simulated profit for each production quantity. By copying from cell B14 to C14 E14 the formula STDEV B16 B1015 you can compute the standard deviation of the simulated profits for each order quantity. Each time you press F9 one thousand iterations of demand are simulated for each order quantity. Producing 40 000 cards always yields the largest expected profit so producing 40 000 cards appears to be the proper decision. Chapter 69 Introduction to Monte Carlo Simulation