Đang chuẩn bị liên kết để tải về tài liệu:
Microsoft Excel 2010: Data Analysis and Business Modeling phần 3

. Truy cập trang web của chúng tôi tại Gửi ý kiến vào và các nhãn hiệu hàng hóa được liệt kê tại Với doanh thu hàng quý của công ty, làm thế nào tôi có thể chỉ ra khu, trong đó doanh thu tăng so với quý trước đó trong một màu, và các khu | 128 Microsoft Excel 2010 Data Analysis and Business Modeling With a one-way data table you can determine how changing one input changes any number of outputs. With a two-way data table you can determine how changing two inputs changes a single output. This chapter s three examples will show how easy it is to use a data table and obtain meaningful sensitivity results. Answers to This Chapter s Questions I m thinking of starting a store in the local mall to sell gourmet lemonade. Before opening the store I m curious about how my profit revenue and variable costs will depend on the price I charge and the unit cost. The work required for this analysis is in the file Lemonade.xlsx. See Figures 17-1 17-2 and 17-4. The input assumptions are given in the range D1 D4. I assume that annual demand for lemonade see the formula in cell D2 equals 65000-9000 price. Chapter 79 Estimating a Demand Curve contains a discussion of how to estimate a demand curve. I ve created the names in C1 C7 to correspond to cells D1 D7. I computed annual revenue in cell D5 with the formula demand price. In cell D6 I computed the annual variable cost with the formula unit_cost demand. Finally in cell D7 I computed profit by using the formula revenue-fixed_cost-variable_cost. 1 A B c price 0 4.00 2 demand 29000 3 unit cost 0.45 4 fixed cost 45.000.00 5 revenue 116 000.00 6 variable cost 13 050.00 _7 profit 57 950.00 FIGURE 17-1 The inputs that change the profitability of a lemonade store. Suppose that I want to know how changes in price for example from 1.00 through 4.00 in 0.25 increments affect annual profit revenue and variable cost. Because I m changing only one input a one-way data table will solve the problem. The data table is shown in Figure 17-2. Chapter 17 Sensitivity Analysis with Data Tables 129 B 0 D E F 9 profit revenue variable COS1 10 price 57950 116000 13050 11 1.00 14 200.00 56 000.00 25 200.00 12 1.25 2.000.00 67 107.50 24 107.50 13 1.50 9 075.00 77 250.00 23 175.00 14 1.75 19 .