23.7.16

SAP HANA PAL 시나리오 2 - 신규 제품 투자의 현금 흐름 분석하기

이번 Blog에서는 SAP HANA PAL, 즉 Data Mining Library를 활용한 2번째 예제를 Post합니다.

고객은 새로운 제품을 만드는 데 필요한 투자의 현금 흐름 분석을 수행하고 싶습니다. 산출된 평가는 현금 흐름 계산으로부터 제품 수익, 제품 비용, 간접비 및 각 연도의 자본 투입에 부여되고 있습니다. 자본 투자 평가를 위한 현금 흐름은 개별 연도가 합쳐지고 미래 가치는 무시됩니다. 다시 말해 현금 흐름의 순 현재 가치는 투자 이익을 측정하는 하나의 값으로 도출됩니다.

투영 된 추정치는 각 데이터 포인트의 단일 포인트 추정치이며, 분석는 프로젝트의 순 현재 가치 (NPV)의 단일 포인트 값을 제공합니다. 이것은 결정론적 모델링으로 언급되며, 이것은 우리가 결과의 확률을 검토하는 확률 모델과는 대조적입니다. 예를 들어, NPV 확률은 0보다 큰 것입니다. 확률적 모델링은 또한 Monte Carlo Simulation이라고합니다.

Monte Carlo Simulation은 투자의 순 현재 가치 (NPV)를 추정하기 위해 우리의 예에서 사용됩니다.

1단계

제품 매출, 제품 비용, 간접비, 자본 투자에 대한 주어진 추정치 (단일 지점에서 결정 값)를 입력하십시요. 이 예에서, 시간주기는도 5 (1 년 내지 5 년)이다.

각 변수의 무작위 분포 샘플링 알고리즘을 실행하고 수천의 샘플 세트를 생성합니다. 샘플 세트 수는 분석을위한 선택됩니다. 값이 크면 클수록 출력 분포를 더 매끄럽고 정규 분포가 되는 것이 더 가깝습니다.

SET SCHEMA DM_PAL;
-----------------------------------------
---Random sampling process---------------
-----------------------------------------
DROP TYPE PAL_DISTRRANDOM_DISTRPARAM_T;
CREATE TYPE PAL_DISTRRANDOM_DISTRPARAM_T AS TABLE( NAME VARCHAR(50), VAL VARCHAR(50));

DROP TYPE PAL_DISTRRANDOM_RESULT_T;
CREATE TYPE PAL_DISTRRANDOM_RESULT_T AS TABLE(ID INTEGER, RANDOM DOUBLE);

DROP TYPE PAL_CONTROL_T;
CREATE TYPE PAL_CONTROL_T AS TABLE(NAME VARCHAR (50), INTARGS INTEGER, DOUBLEARGS DOUBLE,
STRINGARGS VARCHAR (100));

DROP TYPE PAL_CASHFLOW_T;
CREATE TYPE PAL_CASHFLOW_T AS TABLE(ID INTEGER, CASH DOUBLE);
DROP TABLE PDATA;
CREATE COLUMN TABLE PDATA("POSITION" INT, "SCHEMA_NAME" NVARCHAR(256), "TYPE_NAME" NVARCHAR(256), "PARAMETER_TYPE" VARCHAR(7));

INSERT INTO PDATA VALUES (1, 'DM_PAL','PAL_DISTRRANDOM_DISTRPARAM_T', 'IN');
INSERT INTO PDATA VALUES (2, 'DM_PAL','PAL_CONTROL_T', 'IN');
INSERT INTO PDATA VALUES (3, 'DM_PAL','PAL_DISTRRANDOM_RESULT_T', 'OUT');

call SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('DM_PAL','PAL_DISTRRANDOM');
call SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'DISTRRANDOM', 'DM_PAL','PAL_DISTRRANDOM', PDATA);

---------------------------
----------YEAR 1 -----------
-----Product revenue------
DROP TABLE PAL_DISTRRANDOM_DISTRPARAM_TAB;
CREATE COLUMN TABLE PAL_DISTRRANDOM_DISTRPARAM_TAB LIKE
PAL_DISTRRANDOM_DISTRPARAM_T;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '0');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '0.0001');

DROP TABLE PAL_CONTROL_TAB;
CREATE COLUMN TABLE PAL_CONTROL_TAB LIKE PAL_CONTROL_T;
INSERT INTO PAL_CONTROL_TAB VALUES ('NUM_RANDOM',5000,null,null);
INSERT INTO PAL_CONTROL_TAB VALUES ('SEED', 0, null, null);
INSERT INTO PAL_CONTROL_TAB VALUES ('THREAD_NUMBER', 8, null, null);

DROP TABLE PAL_DISTRRANDOM_RESULT_TAB_REVENUE;
CREATE COLUMN TABLE PAL_DISTRRANDOM_RESULT_TAB_REVENUE LIKE
PAL_DISTRRANDOM_RESULT_T;

CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_REVENUE) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE;

---------Product Costs---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '1000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '5625');

DROP TABLE PAL_DISTRRANDOM_RESULT_TAB_COSTS;
CREATE COLUMN TABLE PAL_DISTRRANDOM_RESULT_TAB_COSTS LIKE
PAL_DISTRRANDOM_RESULT_T;

CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_COSTS) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_COSTS;

--------OVERHEADS---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'UNIFORM');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MIN', '1400');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MAX', '1500');

DROP TABLE PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS;
CREATE COLUMN TABLE PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS LIKE
PAL_DISTRRANDOM_RESULT_T;

CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS) with overview;
-- SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS;

-------INVESTMENT---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '10000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '250000');

DROP TABLE PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT;
CREATE COLUMN TABLE PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT LIKE
PAL_DISTRRANDOM_RESULT_T;

CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT;

--------calculate cash flow -------
DROP TABLE PAL_CASHFLOW_YEAR1;
CREATE COLUMN TABLE PAL_CASHFLOW_YEAR1(ID INTEGER,CASH DOUBLE);

INSERT INTO PAL_CASHFLOW_YEAR1
SELECT PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID, PAL_DISTRRANDOM_RESULT_TAB_REVENUE.RANDOM
- PAL_DISTRRANDOM_RESULT_TAB_COSTS.RANDOM - PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS.RANDOM -
PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT.RANDOM
FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE left
join PAL_DISTRRANDOM_RESULT_TAB_COSTS on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_COSTS.ID left
join
PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS.ID
left join
PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT.ID;
--SELECT * from PAL_CASHFLOW_YEAR1;

---------------------------------
------------YEAR 2 --------------
--product revenue----
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '3000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '90000');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE;

CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_REVENUE) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE;

----Product Costs---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '1000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '5625');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_COSTS;

CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_COSTS) with overview;
SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_COSTS;

----OVERHEADS---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'UNIFORM');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MIN', '1800');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MAX', '2200');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS;

CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS) with overview;
SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS;

----INVESTMENT---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '2000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '10000');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT;
CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT;

-- calculate cash flow ----
DROP TABLE PAL_CASHFLOW_YEAR2;
CREATE COLUMN TABLE PAL_CASHFLOW_YEAR2(ID INTEGER,CASH DOUBLE);
INSERT INTO PAL_CASHFLOW_YEAR2 SELECT PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID,
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.RANDOM
- PAL_DISTRRANDOM_RESULT_TAB_COSTS.RANDOM -PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS.RANDOM -
PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT.RANDOM
FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE left
join PAL_DISTRRANDOM_RESULT_TAB_COSTS on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_COSTS.ID left
join
PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS.ID
left join
PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_INVESTMENT.ID;
--SELECT * from PAL_CASHFLOW_YEAR2;

-----------------------------
----------YEAR 3 ------------
----product revenue----
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '8000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '640000');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE;
CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_REVENUE) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE;

----Product Costs---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '2500');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '35156.25');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_COSTS;
CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_COSTS) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_COSTS;

----OVERHEADS---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'UNIFORM');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MIN', '2200');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MAX', '2800');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS;
CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS;

-- calculate cash flow ----
DROP TABLE PAL_CASHFLOW_YEAR3;
CREATE COLUMN TABLE PAL_CASHFLOW_YEAR3(ID INTEGER,CASH DOUBLE);
INSERT INTO PAL_CASHFLOW_YEAR3 SELECT PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID,
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.RANDOM
- PAL_DISTRRANDOM_RESULT_TAB_COSTS.RANDOM -PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS.RANDOM
FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE left
join PAL_DISTRRANDOM_RESULT_TAB_COSTS on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_COSTS.ID left
join
PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS.ID;
--SELECT * from PAL_CASHFLOW_YEAR3;

-----------------------------
--------YEAR 4 --------------
--Product revenue---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '18000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '3240000');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE;
CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_REVENUE) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE;

----Product Costs---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '7000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '275625');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_COSTS;
CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_COSTS) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_COSTS;

----OVERHEADS---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'UNIFORM');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MIN', '2600');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MAX', '3400');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS;
CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS;

-- calculate cash flow ----
DROP TABLE PAL_CASHFLOW_YEAR4;
CREATE COLUMN TABLE PAL_CASHFLOW_YEAR4(ID INTEGER,CASH DOUBLE);
INSERT INTO PAL_CASHFLOW_YEAR4 SELECT PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID,
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.RANDOM
- PAL_DISTRRANDOM_RESULT_TAB_COSTS.RANDOM -PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS.RANDOM
FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE left
join PAL_DISTRRANDOM_RESULT_TAB_COSTS on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_COSTS.ID left
join
PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS.ID;
--SELECT * from PAL_CASHFLOW_YEAR4;

----------------------------
-------YEAR 5 --------------
--Product revenue----
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '30000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '9000000');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE;
CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_REVENUE) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE;

----Product Costs---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'NORMAL');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MEAN', '10000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('VARIANCE', '562500');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_COSTS;
CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_COSTS) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_COSTS;

----OVERHEADS---------
DELETE FROM PAL_DISTRRANDOM_DISTRPARAM_TAB;
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('DISTRIBUTIONNAME', 'UNIFORM');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MIN', '3000');
INSERT INTO PAL_DISTRRANDOM_DISTRPARAM_TAB VALUES ('MAX', '4000');
DELETE FROM PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS;
CALL DM_PAL.PAL_DISTRRANDOM(PAL_DISTRRANDOM_DISTRPARAM_TAB, PAL_CONTROL_TAB, PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS) with overview;
--SELECT * FROM PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS;

-- calculate cash flow ----
DROP TABLE PAL_CASHFLOW_YEAR5;
CREATE COLUMN TABLE PAL_CASHFLOW_YEAR5(ID INTEGER,CASH DOUBLE);
INSERT INTO PAL_CASHFLOW_YEAR5 SELECT PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID,
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.RANDOM
- PAL_DISTRRANDOM_RESULT_TAB_COSTS.RANDOM -PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS.RANDOM
FROM PAL_DISTRRANDOM_RESULT_TAB_REVENUE left
join PAL_DISTRRANDOM_RESULT_TAB_COSTS on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_COSTS.ID left
join
PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS on
PAL_DISTRRANDOM_RESULT_TAB_REVENUE.ID=PAL_DISTRRANDOM_RESULT_TAB_OVERHEADS.ID;
--SELECT * from PAL_CASHFLOW_YEAR5;


2단계

각 샘플링을 위해 다음의 식으로 투자의 순 현재 가치를 계산합니다.
---- calculate net present value of investment ----
DROP TABLE NPV;
CREATE COLUMN TABLE NPV(NPVALUE DOUBLE);

INSERT INTO NPV
SELECT PAL_CASHFLOW_YEAR1.CASH +
PAL_CASHFLOW_YEAR2.CASH/1.05 +
PAL_CASHFLOW_YEAR3.CASH/POWER(1.05,2) +
PAL_CASHFLOW_YEAR4.CASH/POWER(1.05,3) +
PAL_CASHFLOW_YEAR5.CASH/POWER(1.05,4)
FROM PAL_CASHFLOW_YEAR1 left join
PAL_CASHFLOW_YEAR2 on PAL_CASHFLOW_YEAR1.ID =
PAL_CASHFLOW_YEAR2.ID
left join PAL_CASHFLOW_YEAR3 on PAL_CASHFLOW_YEAR1.ID
= PAL_CASHFLOW_YEAR3.ID
left join PAL_CASHFLOW_YEAR4 on PAL_CASHFLOW_YEAR1.ID
= PAL_CASHFLOW_YEAR4.ID
left join PAL_CASHFLOW_YEAR5 on PAL_CASHFLOW_YEAR1.ID
= PAL_CASHFLOW_YEAR5.ID;

SELECT * FROM NPV;

다음과 같이 예상 된 결과는 다음과 같습니다.

3단계

투자의 NPV 정규 분포에 맞게 Distribution Fitting을 실행하고 투자의 순 현재 가치의 분포를 플롯합니다. (Central Limit 정리는 출력 분포가 정규 분포가 될 것이라고 언급합니다.)

---------------------------------------
------- distribution fit process ---
---------------------------------------
DROP TYPE PAL_DISTRFIT_DATA_T;
CREATE TYPE PAL_DISTRFIT_DATA_T AS TABLE(NPVALUE DOUBLE);

DROP TYPE PAL_DISTRFIT_ESTIMATION_T;
CREATE TYPE PAL_DISTRFIT_ESTIMATION_T AS TABLE(NAME VARCHAR(50),VAL VARCHAR(50));

DROP TYPE PAL_DISTRFIT_STATISTICS_T;
CREATE TYPE PAL_DISTRFIT_STATISTICS_T AS TABLE(NAME VARCHAR(50),VAL DOUBLE);

DROP TYPE PAL_CONTROL_T;
CREATE TYPE PAL_CONTROL_T AS TABLE(NAME VARCHAR (50),INTARGS INTEGER,DOUBLEARGS DOUBLE,STRINGARGS VARCHAR (100));

DROP TABLE PDATA_TBL;
CREATE COLUMN TABLE PDATA_TBL("POSITION" INT, "SCHEMA_NAME" NVARCHAR(256), "TYPE_NAME" NVARCHAR(256), "PARAMETER_TYPE" VARCHAR(7));

INSERT INTO PDATA_TBL VALUES (1, 'DM_PAL','PAL_DISTRFIT_DATA_T', 'IN');
INSERT INTO PDATA_TBL VALUES (2, 'DM_PAL','PAL_CONTROL_T', 'IN');
INSERT INTO PDATA_TBL VALUES (3, 'DM_PAL','PAL_DISTRFIT_ESTIMATION_T', 'OUT');
INSERT INTO PDATA_TBL VALUES (4, 'DM_PAL','PAL_DISTRFIT_STATISTICS_T', 'OUT');

call SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('DM_PAL','PAL_DISTRFIT');
call SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'DISTRFIT', 'DM_PAL','PAL_DISTRFIT', PDATA_TBL);

DROP TABLE PAL_CONTROL_TBL;
CREATE COLUMN TABLE PAL_CONTROL_TBL(NAME VARCHAR (50),INTARGS INTEGER,DOUBLEARGS DOUBLE,STRINGARGS VARCHAR (100));

INSERT INTO PAL_CONTROL_TBL VALUES ('DISTRIBUTIONNAME', null, null, 'NORMAL');
INSERT INTO PAL_CONTROL_TBL VALUES ('OPTIMAL_METHOD', 0, null, null);

DROP TABLE PAL_DISTRFIT_ESTIMATION_TBL;
CREATE COLUMN TABLE PAL_DISTRFIT_ESTIMATION_TBL(NAME VARCHAR(50),VAL VARCHAR(50));

DROP TABLE PAL_DISTRFIT_STATISTICS_TBL;
CREATE COLUMN TABLE PAL_DISTRFIT_STATISTICS_TBL(NAME VARCHAR(50),VAL DOUBLE);

CALL DM_PAL.PAL_DISTRFIT(NPV, PAL_CONTROL_TBL, PAL_DISTRFIT_ESTIMATION_TBL, PAL_DISTRFIT_STATISTICS_TBL) with overview;
SELECT * FROM PAL_DISTRFIT_ESTIMATION_TBL;

다음과 같이 예상 된 결과는 다음과 같습니다.

4단계

적용시킨 모델에 따르면, 주어진 투자의 NPV와 같거나 더 작은 투자의 NPV를 갖는 확률을 얻기 위해 누적 분포 함수를 실행합니다.
---------------------------------------
----distribution probability process --
---------------------------------------
DROP TYPE PAL_DISTRPROB_DATA_T;
CREATE TYPE PAL_DISTRPROB_DATA_T AS TABLE(DATACOL DOUBLE);

DROP TYPE PAL_DISTRPROB_DISTRPARAM_T;
CREATE TYPE PAL_DISTRPROB_DISTRPARAM_T AS TABLE(NAME VARCHAR(50),VALUEE VARCHAR(50));

DROP TYPE PAL_DISTRPROB_RESULT_T;
CREATE TYPE PAL_DISTRPROB_RESULT_T AS TABLE(INPUTDATA DOUBLE,PROBABILITY DOUBLE);

DROP TYPE PAL_CONTROL_T;
CREATE TYPE PAL_CONTROL_T AS TABLE(NAME VARCHAR (50),INTARGS INTEGER,DOUBLEARGS DOUBLE,STRINGARGS VARCHAR (100));

DROP TABLE PAL_DISTRPROB_PDATA_TBL;
CREATE COLUMN TABLE PAL_DISTRPROB_PDATA_TBL("POSITION" INT, "SCHEMA_NAME" NVARCHAR(256), "TYPE_NAME" NVARCHAR(256), "PARAMETER_TYPE" VARCHAR(7));
INSERT INTO PAL_DISTRPROB_PDATA_TBL VALUES (1, 'DM_PAL','PAL_DISTRPROB_DATA_T', 'IN');
INSERT INTO PAL_DISTRPROB_PDATA_TBL VALUES (2, 'DM_PAL', 'PAL_DISTRPROB_DISTRPARAM_T', 'IN');
INSERT INTO PAL_DISTRPROB_PDATA_TBL VALUES (3, 'DM_PAL','PAL_CONTROL_T', 'IN');
INSERT INTO PAL_DISTRPROB_PDATA_TBL VALUES (4, 'DM_PAL','PAL_DISTRPROB_RESULT_T', 'OUT');

CALL SYS.AFLLANG_WRAPPER_PROCEDURE_DROP('DM_PAL','PAL_DISTRPROB_PROC');
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'DISTRPROB', 'DM_PAL','PAL_DISTRPROB_PROC', PAL_DISTRPROB_PDATA_TBL);

DROP TABLE PAL_DISTRPROB_DATA_TBL;
CREATE TABLE PAL_DISTRPROB_DATA_TBL LIKE PAL_DISTRPROB_DATA_T;
INSERT INTO PAL_DISTRPROB_DATA_TBL VALUES (7000);
INSERT INTO PAL_DISTRPROB_DATA_TBL VALUES (8000);
INSERT INTO PAL_DISTRPROB_DATA_TBL VALUES (9000);
INSERT INTO PAL_DISTRPROB_DATA_TBL VALUES (10000);
INSERT INTO PAL_DISTRPROB_DATA_TBL VALUES (11000);

DROP TABLE PAL_DISTRPROB_DISTRPARAM_TBL;
CREATE TABLE PAL_DISTRPROB_DISTRPARAM_TBL LIKE PAL_DISTRPROB_DISTRPARAM_T;
INSERT INTO PAL_DISTRPROB_DISTRPARAM_TBL VALUES ('DISTRIBUTIONNAME', 'Normal');
INSERT INTO PAL_DISTRPROB_DISTRPARAM_TBL VALUES ('MEAN', '100');
INSERT INTO PAL_DISTRPROB_DISTRPARAM_TBL VALUES ('VARIANCE', '1');
UPDATE PAL_DISTRPROB_DISTRPARAM_TBL SET VALUEE = (SELECT VAL FROM PAL_DISTRFIT_ESTIMATION_TBL WHERE NAME = 'MEAN') WHERE NAME = 'MEAN';
UPDATE PAL_DISTRPROB_DISTRPARAM_TBL SET VALUEE = (SELECT VAL*VAL FROM PAL_DISTRFIT_ESTIMATION_TBL WHERE NAME = 'SD') WHERE NAME = 'VARIANCE';
SELECT * FROM PAL_DISTRPROB_DISTRPARAM_TBL;

DROP TABLE #PAL_CONTROL_TBL;
CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL(NAME VARCHAR (50),INTARGS INTEGER,DOUBLEARGS DOUBLE,STRINGARGS VARCHAR (100));
INSERT INTO #PAL_CONTROL_TBL VALUES ('LOWER_UPPER',0,null,null);

DROP TABLE PAL_DISTRPROB_RESULT_TBL;
CREATE TABLE PAL_DISTRPROB_RESULT_TBL LIKE PAL_DISTRPROB_RESULT_T;

CALL DM_PAL.PAL_DISTRPROB_PROC(PAL_DISTRPROB_DATA_TBL, PAL_DISTRPROB_DISTRPARAM_TBL, #PAL_CONTROL_TBL, PAL_DISTRPROB_RESULT_TBL) WITH OVERVIEW;
SELECT * FROM PAL_DISTRPROB_RESULT_TBL;

다음과 같이 예상 된 결과는 다음과 같습니다.



No comments:

Post a Comment