SSAS & Excel BI tips in one piece: what-if analysis

This series is used to document the experience of using SSAS and Excel for data analysis in the work, in the Days of Goodness (SSAS & Excel BI Tips), which is now intended to share small skills in the form of blogs, which is not enough to be explained as a separate course, but a lot of BA, Bu unfamiliar but very useful skills, dedicated to the needs of friends.

This article is about the what-if hypothesis analysis of Excel, and the what-if writeback of SSAS is intended to be a positive commentary.

Shown is a simple equal principal loan monthly calculation, the first line is the total loan amount of 300,000, the second line is the repayment of the month default is 0, the third line is the total repayment month 20 years 240 months, the fourth line is a formula, according to the number of months to calculate the monthly repayment amount, the figure can be seen in the first month of 2750RMB. At this point we open the Excel Data menu bar WHAT-IF analysis below the scenario Manager to add the scenario

The first scheme name added is "2nd year", specify changing cells as C2 cell (number of months repaid), OK

The Excel Pop-up dialog asks for a change value for this scenario, and we specify the 2nd year, so the corresponding setting here is 12.

After you loop to add the four scenarios, click the Summary button

In the dialog box that pops up, specify the result cells to be C4 (the amount of the current month), OK

Excel generates a scenario summary report that lists monthly contributions for the month specified by each programme

Go back to the Data menu bar to do goal seek target retrieval analysis

In the dialog box that pops up, specify the set cell as the C4 cell (the amount of the current month), specify the by changing cell as the C2 cell (the number of months that have been repaid), and enter the to value of 2000,

It means to see when the monthly supply drops to 2000, or a look forward.

Excel tells you to need to be 120 months later, that is, 10, a bolt from the blue.

Here the goal seek to pour out the repayment number of months is floating point, accurate to the decimal point after the N-bit

SSAS & Excel BI tips in one piece: what-if analysis