Method for creating corporate valuation and financial projection form according to discounted cash flow method, and system therefor

Abstract

Disclosed are a method for creating a corporate valuation and financial projection form according to a discounted cash flow method, and a system therefor. According to one aspect of the present invention, provided is a computer program stored in a computer-readable medium, the computer program comprising the steps of: extracting data for analysis from an input file in which basic analysis information and basic financial statements have been written; creating an estimation data sheet regarding an estimation period corresponding to the basic analysis information; generating a first account type-specific sheet regarding a profit and loss statement, and creating a valuation type-specific form within the first account type-specific sheet; calculating a corporate tax rate on the basis of an expected operating profit derived by synthesizing the first account type-specific sheet; generating a second account type-specific sheet regarding a statement of financial position, and creating a valuation type-specific form within the second account type-specific sheet; generating a statement of projected profit and loss and a projected statement of financial position by combining the first account type-specific sheet and the second account type-specific sheet; and performing a corporate valuation by using the statement of projected profit and loss, the corporate tax rate, and the projected statement of financial position.

Description

Method for preparing enterprise valuation and financial projection forms based on the discounted cash flow method and the system thereof

[0001] The present invention relates to a method for preparing a financial projection form and a system for enterprise valuation based on the discounted cash flow method.

[0002] Currently, analysts manually fill out forms in Excel to perform current valuations or future financial projections. Consequently, these processes are time-consuming. Furthermore, calculation errors resulting from manual entry can lead to inaccurate valuations even in credible valuation processes.

[0003] At present, standard forms are sold to assist analysts in valuation and future financial projections. However, because the financial information of each company varies, the problem of filling out forms manually has not been fundamentally resolved.

[0004] Furthermore, while there are services that provide automatic value calculation through programs, they fail to reflect the specific characteristics of each company because they rely on automatically inputting simple and consistent assumptions. Additionally, since they do not conduct in-depth analysis and are not integrated with Excel, a major analytical tool, they are currently difficult to present as a fundamental solution.

[0005] The matters described in the technical background section of this invention are for the purpose of understanding the background of the invention and cannot be concluded as prior art already known to a person with ordinary knowledge in the field to which this technology belongs.

[0006] The present invention aims to provide a method and system for creating a form for in-depth corporate valuation and financial estimation based on the discounted cash flow method, which can automatically generate a form for in-depth corporate valuation and financial estimation without the need for manual entry, based on financial statements and basic information for analysis entered into an input form.

[0007] Other objects of the present invention will become more apparent through the preferred embodiments described below.

[0008] According to one aspect of the present invention, a computer program stored on a computer-readable medium for performing a method of preparing a business valuation and financial estimation form is provided, wherein the computer program causes a computer to perform the following steps, the steps comprising: extracting data for analysis from an input file containing basic analysis information and basic financial statements; preparing an estimation data sheet regarding an estimation period corresponding to the basic analysis information; creating a first account type sheet regarding an income statement and preparing a valuation type form within the first account type sheet; calculating a corporate tax rate based on an expected operating profit derived by synthesizing the first account type sheet; creating a second account type sheet regarding a balance sheet and preparing a valuation type form within the second account type sheet; creating an expected income statement and an expected balance sheet by synthesizing the first account type sheet and the second account type sheet; and performing a business valuation using the expected income statement, the corporate tax rate, and the expected balance sheet.

[0009] The above first account type sheet includes at least one of a Sales (Sales) sheet, a Cost of Goods Sold (COGS) sheet, a Selling and General Expenses (SGA) sheet, and a Non-Operating Items (NO) sheet, and the above second account type sheet may include at least one of Working Capital (WC), Fixed Assets (FA), Non-Operating Assets (NOA), Interest-Bearing Debt (IBD), Other Operating Capital (OAL), and Equity Capital (CAP) sheet.

[0010] In the above-mentioned step of performing the enterprise valuation, a enterprise valuation sheet may be prepared by applying the discounted cash flow method, calculating the annual operating cash flows, calculating the present value of the cash flows by discounting them to their time value, and the form may be prepared to calculate the enterprise value and stock value by summing the present value and the residual value.

[0011] The above input file includes an analysis basic information sheet, a basic income statement sheet, a basic balance sheet sheet, and a discount rate sheet. In the analysis basic information sheet, the reference point and end point of the analysis are set. In the basic income statement sheet, an existing income statement is systematized and entered into the first setting area, and one or more of the first account type and analysis method are set in the second setting area. In the basic balance sheet, an existing balance sheet is systematized and entered into the third setting area, and the second account type is set in the fourth setting area. In the discount rate sheet, information related to the discount rate can be set.

[0012] Prior to extracting the above analysis data, a step of refining the format of the above input file is performed, and the blank parts in the above second setting area and the above fourth setting area can be automatically set by utilizing an external generative AI linked via API.

[0013] Meanwhile, according to another aspect of the present invention, a system for creating enterprise valuation and financial estimation forms comprises: a data extraction unit that extracts data for analysis from an input file containing basic analysis information and basic financial statements; a data sheet generation unit that creates an estimation data sheet regarding an estimation period corresponding to the basic analysis information; an account type sheet generation unit that creates a first account type sheet regarding an income statement, creates a form for valuation type within the first account type sheet, calculates a corporate tax rate based on an expected operating profit derived by synthesizing the first account type sheet, creates a second account type sheet regarding a balance sheet, and creates a form for valuation type within the second account type sheet; a standard report generation unit that generates an expected income statement and an expected balance sheet by synthesizing the first account type sheet and the second account type sheet; and an enterprise valuation unit that performs enterprise valuation using the expected income statement, the corporate tax rate, and the expected balance sheet.

[0014] The above first account type sheet includes at least one of a Sales (Sales) sheet, a Cost of Goods Sold (COGS) sheet, a Selling and General Expenses (SGA) sheet, and a Non-Operating Items (NO) sheet, and the above second account type sheet may include at least one of Working Capital (WC), Fixed Assets (FA), Non-Operating Assets (NOA), Interest-Bearing Debt (IBD), Other Operating Capital (OAL), and Equity Capital (CAP) sheet.

[0015] The above-mentioned enterprise valuation department prepares an enterprise valuation sheet by applying the discounted cash flow method, calculates the annual operating cash flows, calculates the present value of the cash flows by discounting them to their time value, and the form may be prepared to calculate the enterprise value and stock value by summing the present value and the residual value.

[0016] Other aspects, features, and advantages other than those described above will become clear from the following drawings, claims, and detailed description of the invention.

[0017] According to an embodiment of the present invention, based on financial statements and basic information for analysis entered into an input form, there is an effect of automatically generating a form for in-depth corporate valuation and financial estimation without the need for manual entry.

[0018] The effects obtainable from the present invention are not limited to those mentioned above, and other unmentioned effects will be clearly understood by those skilled in the art from the description below.

[0019] FIG. 1 is a block diagram of a system for preparing enterprise valuation and financial estimation forms based on the discounted cash flow method according to one embodiment of the present invention.

[0020] Figure 2 is a diagram showing the configuration of the input file,

[0021] Figures 3 and 4 are diagrams showing the configuration of each sheet of the input file.

[0022] FIG. 5 is a flowchart of a method for preparing a corporate valuation and financial estimation form based on the discounted cash flow method according to an embodiment of the present invention.

[0023] Figure 6 is an example of an estimated data sheet,

[0024] FIG. 7 is a diagram showing the schematic configuration of the first account type sheet regarding the income statement,

[0025] Figure 8 is a drawing showing the forms of a Sales sheet,

[0026] FIG. 9 is a diagram showing the forms of COGS / SGA sheets,

[0027] Figure 10 is a corporate tax sheet,

[0028] FIG. 11 is an example of a sheet by second account type regarding the balance sheet,

[0029] Figure 12 is an example of an estimated profit and loss statement and report,

[0030] Figure 13 is an example of a projected balance sheet and report,

[0031] Figure 14 is an example of a DCF report.

[0032] The present invention is capable of various modifications and may have various embodiments, and specific embodiments are illustrated in the drawings and described in detail. However, this is not intended to limit the invention to specific embodiments, and it should be understood that the invention includes all modifications, equivalents, and substitutions that fall within the spirit and scope of the invention.

[0033] When it is stated that one component is "connected" or "connected" to another component, it should be understood that while it may be directly connected or connected to that other component, there may also be other components in between. On the other hand, when it is stated that one component is "directly connected" or "directly connected" to another component, it should be understood that there are no other components in between.

[0034] Terms such as "first," "second," etc., may be used to describe various components, but said components should not be limited by said terms. These terms are used solely for the purpose of distinguishing one component from another.

[0035] The terms used herein are merely for describing specific embodiments and are not intended to limit the invention. Singular expressions include plural expressions unless the context clearly indicates otherwise. In this specification, terms such as “comprising” or “having” are intended to indicate the presence of the features, numbers, steps, actions, components, parts, or combinations thereof described in the specification, and should be understood as not precluding the existence or addition of one or more other features, numbers, steps, actions, components, parts, or combinations thereof.

[0036] In this specification, the term "part" includes a unit realized by hardware, a unit realized by software, and a unit realized using both. Additionally, one unit may be realized using two or more pieces of hardware, and two or more units may be realized by one piece of hardware. Meanwhile, "part" is not limited to software or hardware, and "part" may be configured to reside in an addressable storage medium or configured to run on one or more processors. Accordingly, as an example, "part" includes components such as software components, object-oriented software components, class components, and task components, as well as processes, functions, attributes, procedures, subroutines, segments of program code, drivers, firmware, microcode, circuits, data, databases, data structures, tables, arrays, and variables. The functions provided within the components and "parts" may be combined into a smaller number of components and "parts" or further separated into additional components and "parts." In addition, the components and '~parts' may be implemented to regenerate one or more CPUs within the device.

[0037] Furthermore, the components of the embodiments described with reference to each drawing are not limited to the respective embodiments and may be implemented to be included in other embodiments within the scope of maintaining the technical spirit of the present invention. It is also obvious that multiple embodiments may be re-implemented as a single embodiment that integrates multiple embodiments, even if a separate description is omitted.

[0038] Furthermore, in the description referring to the attached drawings, identical components are assigned the same or related reference numerals regardless of drawing symbols, and redundant descriptions thereof are omitted. In describing the present invention, if it is determined that a detailed description of related prior art could unnecessarily obscure the essence of the present invention, such detailed description is omitted.

[0039] FIG. 1 is a block diagram of a system for creating enterprise valuation and financial estimation forms based on the discounted cash flow method according to an embodiment of the present invention, FIG. 2 is a diagram showing the configuration of an input file, FIG. 3 and FIG. 4 are diagrams showing the configuration of each sheet of an input file, FIG. 5 is a flowchart of a method for creating enterprise valuation and financial estimation forms based on the discounted cash flow method according to an embodiment of the present invention, FIG. 6 is an example diagram of an estimation data sheet, FIG. 7 is a diagram showing the schematic configuration of a first account type sheet regarding an income statement, FIG. 8 is a diagram showing forms of a Sales sheet, FIG. 9 is a diagram showing forms of a COGS / SGA sheet, FIG. 10 is a corporate tax sheet, FIG. 11 is an example diagram of a second account type sheet regarding a balance sheet, FIG. 12 is an example of an estimated income statement and report, FIG. 13 is an example of an estimated balance sheet and report, and FIG. 14 is an example diagram of a DCF report.

[0040] The system for generating enterprise valuation and financial estimation forms based on the discounted cash flow method according to the present embodiment is characterized by being able to automatically generate forms for in-depth enterprise valuation and financial estimation without the need for manual entry, based on financial statements and basic information for analysis recorded in an input file. The analyst can easily perform enterprise valuation and financial estimation by entering their assumptions into the blanks indicated on the form, thereby reducing time and decreasing the possibility of errors.

[0041] Referring to FIG. 1, the system (100) for creating enterprise valuation and financial estimation forms based on the discounted cash flow method according to the present embodiment performs data processing according to a predetermined algorithm on an input file containing basic financial statements and basic information for analysis, thereby creating a standard projected income statement and a projected balance sheet that estimate the future desired by the user, and also performs enterprise valuation and provides it in the form of an output file.

[0042] The enterprise valuation and financial estimation form creation system (100) may include a data extraction unit (110), a data sheet generation unit (120), a sheet creation unit by account type (130), a standard report creation unit (140), and an enterprise valuation unit (150).

[0043] The system (100) is implemented as a program (or application) and can be installed on or downloaded to a computing device to be executed. The computing device on which the system (100) is installed or executed may be, for example, a smartphone, tablet PC, laptop, desktop PC, etc.

[0044] The computing device may be equipped with an input unit for receiving user input (e.g., touchscreen, keypad, keyboard, mouse, microphone, etc.), an output unit for displaying various information (e.g., display, touchscreen, etc.), and a communication unit for communication (e.g., LAN, mobile communication module (3G, 4G, 5G, etc.), short-range communication module (WiFi, Bluetooth, etc.)), etc.

[0045] The data extraction unit (110) can extract specified data from the input file when the program is run (step S210). The input file (10) is a data file having a specified format and may include basic analysis sheets (11~14) such as basic analysis information (BI), basic profit and loss statement (PL), basic balance sheet (BS), and discount rate (WACC).

[0046] First, when the user runs the program and selects an input file or a folder containing the file, form creation begins.

[0047] The input file is a file having a specified Excel format and may include a Basic Investigation (BI) sheet, a Basic Income Statement (PL) sheet, a Basic Balance Sheet (BS) sheet, and a Weighted Average Cost of Carriers (WACC) sheet.

[0048] Referring to Fig. 3a, the analysis basic information sheet sets the reference time (month) and end time (month) of the analysis.

[0049] Referring to Fig. 4a, the basic income statement sheet sets the income statement, account type, and analysis method. When setting the income statement, the user can organize existing income statements and fill them into the first setting area (S1), and set the account type and analysis method in the second setting area (S2). Here, if the settings in the second setting area (S2) are not made by the user and are left blank, an external generative AI can be utilized via an API to automatically set the appropriate type and method. Here, the generative AI may be, for example, ChatGPT 4o. In other words, even if the user is not familiar with or proficient in account types or analysis methods, the creation of the enterprise valuation and financial estimation forms can be performed normally.

[0050] Referring to FIG. 4b, the balance sheet and account types are set in the basic balance sheet. When setting the balance sheet, the user can organize existing balance sheets and fill them into the third setting area (S3), and set the account types in the fourth setting area (S4). Here, if the settings in the fourth setting area (S4) are not made by the user and are left blank, an appropriate type may be automatically set by utilizing an external generative AI via an API.

[0051] Referring to Fig. 3b, discount rate-related information is set in the discount rate sheet.

[0052] Before extracting data from the data extraction unit (110), form refinement of the input file may be performed. To prevent errors during data processing, preprocessing work to refine the form, such as clearing blanks and clearing special characters, may be performed on the values ​​entered in each cell.

[0053] The data sheet generation unit (120) can create an estimated data sheet using the data extracted from the data extraction unit (110) (step S220). Referring to FIG. 6, an estimated data sheet with expected economic indicators for each year is newly created. The values ​​entered in the sheet can be linked to the sheets created for each account type, which will be described later.

[0054] The estimation period (future years) estimated in the estimation data sheet can be determined based on the analysis reference month and end month included in the analysis basic information extracted from the data extraction unit (110).

[0055] The sheet creation section (130) for each account type is a main sheet included in the financial statement, and creates an intermediate sheet for each account type with the goal of the estimated income statement sheet and the estimated balance sheet (step S230).

[0056] Sheets related to the income statement by account type may include Sales, Cost of Goods Sold (COGS), Selling and Administrative Expenses (SGA), and Non-Operating Items (NO) sheets.

[0057] Sheets for each account type related to the balance sheet may include Working Capital (WC), Fixed Assets (FA), Non-Operating Assets (NOA), Interest-Bearing Debt (IBD), Other Operating Capital (OAL), and Equity (CAP) sheets.

[0058] The sheet creation section (130) for each account type can create a form for each evaluation type for the sheet corresponding to each account type (step S240).

[0059] Referring to Fig. 7, an example of an account type sheet n is shown. This sheet contains two forms. Looking at the second form, it includes past performance items and future estimated performance items, and may include a parameter setting area for analysis in the form.

[0060] In this way, by creating and applying various evaluation-type forms for each account type, users can easily set up scenarios for each account type and link expected economic indicators to use in estimation.

[0061] You can prepare forms for the Sales, Cost of Goods Sold (COGS), Selling and Administrative Expenses (SGA), and Non-Operating Items (NO) sheets for each account type of the income statement.

[0062] The Sales sheet estimates sales based on various analysis methods, such as market share, sales growth rate, and production capacity. For example, in the case of market share analysis, the system can be configured to calculate estimated sales by multiplying the growth rate by the market share when an analyst inputs the growth rate.

[0063] For example, in the case of market share-based analysis, revenue is estimated based on the market growth rate; the projected market size and estimated share are input. The program can calculate the revenue by multiplying these values.

[0064] Example) If the market growth rate is an average annual 5% and the estimated market share is 10%, the program calculates the projected revenue by multiplying the market size by the share.

[0065] As another example, in the case of production capacity-based analysis, sales can be calculated by inputting the company's maximum production capacity and expected production rate.

[0066] Example) When the maximum production capacity is 1 million units and the production rate is 80%, the program calculates the estimated revenue as 800,000 units and calculates the final revenue by multiplying that value by the unit price.

[0067] Referring to Figures 8a and 8b, examples of forms according to five methods for creating a sales revenue sheet are provided. (a) For market share, a form can be created for the evaluator to calculate estimated sales revenue by estimating the market size based on the estimated growth rate and multiplying it by the estimated share. (b) For sales growth rate, a form can be created for the evaluator to calculate estimated sales revenue based on the estimated sales growth rate. (c) For production capacity, a form can be created for the evaluator to estimate the company's maximum production capacity and production rate and calculate estimated sales revenue by multiplying the maximum production capacity and production rate. (d) For market share-production capacity, a form can be created to estimate future sales revenue based on market share, but such that it cannot exceed the sales revenue calculated based on production capacity. (e) For sales growth rate-production capacity, a form can be created to estimate future sales revenue based on the sales growth rate, but such that it cannot exceed the sales revenue calculated based on production capacity.

[0068] In the Cost of Goods Sold (COGS) and Selling and Administrative Expenses (SGA) sheets, four methods (labor costs, variable costs, fixed costs, and depreciation) can be presented to analyze Selling and Administrative Expenses (SGA) and Cost of Goods Sold (COGS).

[0069] For example, labor costs are classified by their nature into fixed-cost wages, variable-cost wages, bonuses, allowances, retirement benefits, and welfare expenses (Fig. 9a). Depending on the nature of each, a form can be prepared so that fixed-cost wages are calculated in proportion to past personnel and average wages, variable-cost wages are calculated in proportion to sales revenue, and bonuses, allowances, retirement benefits, and welfare expenses are calculated in proportion to wages.

[0070] Example) If the workforce was previously set to 50 people and the average annual salary to 50 million won, fixed labor costs would be 50 people x 50 million won = 2.5 billion won, and if the future labor cost growth rate is set to 5%, the fixed salaries for the following year will increase to 2.5 billion won x 1.05 = 2.625 billion won.

[0071] Example) If variable labor costs are set at 5% of sales, when the estimated sales are 1 trillion won, the variable labor costs are calculated as 1 trillion won x 5% = 50 billion won.

[0072] Example) If the bonus is set at 10% of the salary, and the total labor cost (sum of fixed and variable costs) is 50 billion won, the bonus is calculated as 50 billion won x 10% = 5 billion won.

[0073] As another example, variable costs are costs that change in proportion to sales revenue, etc., and a form can be prepared to calculate them by setting them as a certain percentage of sales (Fig. 9a).

[0074] Example) If the material cost is set at 20% of sales and the sales are 1 trillion won, the material cost is calculated as 1 trillion won x 20% = 200 billion won.

[0075] As another example, fixed costs can be formed to be incurred as a constant amount regardless of sales, or to increase due to external factors such as inflation (Fig. 9b).

[0076] Example) If the advertising cost is set at 10 billion won and the growth rate is 3%, the advertising cost increases by 3% every year, resulting in 10 billion won in the first year and 10.3 billion won in the following year.

[0077] As another example, in the case of depreciation expenses, a form can be prepared so that future depreciation expenses are calculated according to a future depreciation schedule (Fig. 9b).

[0078] The Non-operating Items (NO) sheet is a sheet for managing non-operating items. It can be created by collecting accounts that are not directly related to business and assuming they will be zero.

[0079] The corporate tax sheet can be configured to calculate the corporate tax rate (or corporate tax amount) according to the corporate tax rate table based on the estimated operating profit (sales - cost of goods sold - selling and administrative expenses) derived from the sheet by account type associated with the income statement sheet (see Fig. 10).

[0080] In addition, the sheet preparation unit (130) for each account type can prepare sheet forms for working capital (WC), fixed assets (FA), non-operating assets (NOA), interest-bearing debt (IBD), other operating capital (OAL), and equity (CAP) for each account type of the balance sheet. Accounts of the same account type can be grouped on the same sheet.

[0081] In the Working Capital (WC) sheet, working capital primarily refers to the capital required for the operation of the business, and future changes in working capital are estimated by considering the working capital turnover period relative to sales or the cost of goods sold. The form can be designed to allow the user to calculate the estimated balances for each account based on assumed future turnover periods, reflecting past working capital turnover periods relative to sales or the cost of goods sold, and to calculate the changes in working capital based on these calculations.

[0082] For example, if the past accounts receivable turnover period is 45 days and the expected sales revenue is 100 billion won, the expected accounts receivable balance is 100 billion won x 45 / 365 = 12.3 billion won.

[0083] The Fixed Assets (FA) sheet can be designed to input the projected future balance of fixed assets. In other words, a form can be created that leaves blanks so that the user can enter the assumed future balance of the fixed assets.

[0084] The Non-Operating Assets (NOA) sheet can be created to enter the projected future balance of non-operating assets.

[0085] The Interest-Backed Debt (IBD) sheet can be prepared to enter the future expected balance of the expected interest-backed debt.

[0086] The Other Operating Capital (OAL) sheet includes items expected to remain the same in the future and can be calculated as a fixed amount.

[0087] The Capital Cap (CAP) sheet can be prepared to enter the projected future balance of capital.

[0088] Referring again to FIG. 1, once the sheet creation for each account type is completed, the standard report creation unit (140) can perform financial statement integration and final calculation (step S250).

[0089] Data from all sheets prepared by account type can be aggregated into the forecast sheets (Estimated Income Statement (Est_PL) and Estimated Balance Sheet (Est_BS) sheets).

[0090] The Est_PL sheet groups accounts related to the income statement, while the Est_BS sheet groups accounts related to the balance sheet. Finally, these can be presented in an organized form on the report sheets (Report_Est_PL and Report_Est_BS sheets). This may be designed so that analysts can utilize it in the form of a report.

[0091] The projected income statement is a consolidated Est_PL sheet containing estimated figures calculated from each account type sheet on the company's income statement, and its components may include sales, cost of goods sold (COGS), selling and administrative expenses (SGA), non-operating items (NO sheet items), and corporate tax.

[0092] The estimated balance sheet is a consolidation of estimated figures calculated from each account type sheet on the company's balance sheet into an Est_BS sheet, and its components may include working capital (WC), fixed assets (FA), non-operating assets (NOA), interest-bearing debt (IBD), other operating capital (OAL), and equity (CAP).

[0093] The business valuation department (150) can finally apply the discounted cash flow method to create a business valuation sheet (DCF sheet) (step S260).

[0094] The DCF sheet provides a comprehensive analysis by linking data from all relevant sheets to evaluate the value of the company and stock. It calculates the annual Free Cash Flow by utilizing data such as revenue, expenses, and taxes derived from sheets for each account type.

[0095] To calculate operating cash flow, values ​​such as operating profit, corporate tax, amortization, capital expenditures, and working capital may be linked. To discount the calculated operating cash flow to its time value, the discount rate value in the WACC sheet may be linked to calculate the present value of the cash flow. Additionally, a residual value may be calculated by assuming that cash flows will continue at a constant growth rate after the valuation period.

[0096] Finally, a form is prepared to calculate enterprise value and stock value by summing the present value and residual value of cash flows, and a sheet (Report_DCF) for report preparation can be created by recombining the form.

[0097] In the case of this system (100), the form is created in Excel just like before and can be freely modified. All processes and result values ​​can be linked to Excel functions. When the designated blanks are filled, the valuation is completed, allowing for faster work. Scenarios can be easily set up through the CHOOSE function and list selection. Expected economic indicators can be linked and used for estimation. It is possible to create an expected income statement and an expected balance sheet, and long-term estimation (e.g., a 30-year period in the future) is also possible. The form can be created so that it can be easily transferred and utilized in reports. The monetary unit, such as won or one million won, can be freely changed through settings.

[0098] In this embodiment, the Discounted Cash Flow (DCF) valuation method is one of the widely used methods for evaluating corporate value. It is a method that calculates a company's intrinsic value by discounting expected future free cash flows (FCF) to their present value.

[0099] Below is an explanation of the key steps and methodology of the DCF evaluation method:

[0100] 1. Estimation of Future Cash Flows (FCF)

[0101] The core of DCF is forecasting future cash flows. To do this, FCF is generally calculated in the following way:

[0102] FCF = Operating Profit - Corporate Tax + Depreciation - Changes in Working Capital - Capital Expenditures (CAPEX)

[0103] Operating Profit = Revenue - Cost of Goods Sold - Selling and Administrative Expenses

[0104] Corporate tax = Operating profit × Effective tax rate

[0105] Changes in Working Capital: Changes in accounts receivable, accounts payable, inventory, etc.

[0106] CAPEX: Cash outflow from fixed asset investment

[0107] 2. Setting the Discount Rate (WACC)

[0108] The Weighted Average Cost of Capital (WACC) is used to convert future FCF into present value.

[0109] WACC = E / (E + D) × Ke + D / (E + D) × Kd × (1 - corporate tax rate)

[0110] E: Equity

[0111] D: Debt

[0112] Ke: Cost of equity

[0113] Kd : Debt cost

[0114] 3. Discounting of future cash flows

[0115] The present value is calculated by discounting the projected FCF using WACC. The following formula is used for each year:

[0116] PV (Present Value) = FCF / (1+WACC) t

[0117] 4. Calculation of Terminal Value

[0118] DCF includes permanence value to account for value beyond the valuation period. The Gordon growth model is frequently used, and the calculation method is as follows.

[0119] Permanent Value = FCF n ×(1 + g) / (WACC - g)

[0120] FCF n : FCF of the last year of the evaluation period

[0121] g : Permanent growth rate

[0122] 5. Calculation of Enterprise Value

[0123] Enterprise Value is calculated by summing the present value of all future cash flows and perpetuation value.

[0124] Equity Value can be derived by adding Non-Operating Assets (NOA) and subtracting Interest-Bearing Debts (IBD) from Enterprise Value.

[0125] The specific execution process of the program in relation to the present invention is as follows.

[0126] 1. Start program

[0127] When the program is launched, a window opens allowing you to select a folder or file, and the program starts running after you select the folder or file.

[0128] 2. Aquaculture purification

[0129] This program processes Excel templates to generate Excel files. For the program to run, an Excel template must be entered. To achieve this, blank spaces and special characters are cleared from the values ​​entered in each cell. Additionally, column K of the PL sheet must contain the account type and column L the analysis method, and column K of the BS sheet must contain the account type. However, if these are not entered, ChatGPT 4o is integrated via API to automatically select and fill in either the account type or the analysis method.

[0130] 3. Create Data Sheet

[0131] A new Data sheet is created. The Data sheet contains projected economic indicators by year as code within the program, and the sheet is created when the program is executed. Subsequently, the cell values ​​within the Data sheet can be utilized by linking them with other sheets using Excel functions.

[0132] 4. Creation of PL Sheets by Account Type (Income Statement)

[0133] In the income statement form, column K contains account types classified as [Sales / COGS / SGA / NO] and column L contains analysis methods (① for Sales: ⓐ Market Share / ⓑ Sales Growth Rate / ⓒ Production Capacity / ⓓ Market Share & Production Capacity / ⓔ Sales Growth Rate & Production Capacity / ⓕ Unit Price x Sales Volume; ② for COGS or SGA: ⓐ Labor Costs / ⓑ Variable Costs / ⓒ Fixed Costs / ⓓ Depreciation; ③ for NO: ⓐ Non-operating). Then, copy the existing PL sheet and rename it to Sales Sheet / COGS Sheet / SGA Sheet / NO Sheet according to the account type, creating a table form for estimating future years by keeping only the items that match each account type. Then, each sheet is generated sequentially by creating a summary table that sums up each item.

[0134] 4-1. Creating Forms in the Sales Sheet

[0135] In the Sales sheet, after creating an Excel function to display the annual growth rate of the sum of each item in the summary table, forms corresponding to the analysis method are sequentially generated for each account type in the lower row of the summary table according to the analysis methods listed in column L: ⓐ Market Share / ⓑ Sales Growth Rate / ⓒ Production Capacity / ⓓ Market Share & Production Capacity / ⓔ Sales Growth Rate & Production Capacity / ⓕ Unit Price x Sales Volume.

[0136] ⓐ Market share analysis method: Calculate future year sales revenue by multiplying the projected market size for the future year by the projected market share.

[0137] Regarding market size, a table is prepared in the row below to allow the evaluator to determine the market size growth rate for future years. The evaluator can either enter a growth rate they have personally determined or select an estimated GDP growth rate or estimated inflation rate linked via Excel functions in the Data sheet as the estimated market size growth rate. This table is then linked to Excel functions when calculating the market size for future years within the analysis form. Through this process, the evaluator calculates the market size for future years.

[0138] Calculate the ratio of the company's revenue to its past market share, and calculate that the ratio of revenue to market size for the past 1 year / past 2 years average / past 3 years average (selectable) will continue in the future.

[0139] Sales revenue is calculated by multiplying the projected market size by the calculated sales ratio.

[0140] ⓑ Sales growth rate analysis method: Calculate the sales revenue for the future year by calculating the sales growth rate of the future year relative to the sales revenue of the previous year.

[0141] Regarding market size, a table is prepared in the row below to allow the evaluator to determine the sales growth rate for future years. The evaluator can either enter a growth rate they have personally selected or choose an estimated GDP growth rate or estimated inflation rate linked via Excel functions in the Data sheet as the estimated sales growth rate. This table is then linked to Excel functions when calculating sales for future years within the analysis form. Through this process, the evaluator calculates the market size for future years.

[0142] ⓒ Production capacity analysis method: Future year sales are calculated by multiplying the estimated sales unit price x estimated maximum production quantity x estimated production rate.

[0143] Past unit selling price is calculated as past sales revenue / sales quantity, and past production rate is calculated as sales quantity / maximum production capacity.

[0144] Regarding the projected unit price for future years, a table is prepared in the row below the previous year's unit price to allow the evaluator to determine the unit price increase rate for the coming year. The evaluator may enter a rate of increase they have personally determined, or select an projected GDP growth rate or projected inflation rate linked via Excel functions in the Data sheet as the projected unit price increase rate. This table is then linked to Excel functions when calculating the unit price for future years within the analysis form. Through this process, the evaluator calculates the unit price for the coming year.

[0145] The estimated maximum production quantity for future years can be entered manually by the evaluator.

[0146] Regarding the expected production rate for future years, the ratio of sales volume to the maximum production capacity in the past is calculated as the past production rate, and the production rates of the past 1 year / past 2 years average / past 3 years average (selectable) are calculated as continuing in the future.

[0147] Future year's sales revenue is calculated by multiplying the estimated unit price, estimated maximum production capacity, and estimated production rate.

[0148] ⓓ Market share and production capacity analysis method: The market share analysis method and production capacity analysis method and the method of filling out the form are identical, but the two forms are combined, and an Excel function is created so that the sales revenue calculated through the market share analysis method cannot exceed the sales revenue calculated through the production capacity analysis method.

[0149] ⓔ Sales growth rate and production capacity analysis method: The sales growth rate analysis method and the production capacity analysis method and the form completion method are identical, but the two forms are combined, and an Excel function is created so that the sales amount calculated through the sales growth rate analysis method cannot exceed the sales amount calculated through the production capacity analysis method.

[0150] ⓕ Unit Price x Sales Volume Analysis Method: Calculate future year sales revenue using the estimated sales unit price x estimated sales volume for the future year.

[0151] Past unit selling price is calculated as past sales revenue / sales quantity.

[0152] Regarding the projected unit price for future years, a table is prepared in the row below the previous year's unit price to allow the evaluator to determine the unit price increase rate for the coming year. The evaluator may enter a rate of increase they have personally determined, or select an projected GDP growth rate or projected inflation rate linked via Excel functions in the Data sheet as the projected unit price increase rate. This table is then linked to Excel functions when calculating the unit price for future years within the analysis form. Through this process, the evaluator calculates the unit price for the coming year.

[0153] The evaluator may manually enter the projected sales quantity for future years.

[0154] Future year's sales revenue is calculated by multiplying the estimated unit price by the estimated sales quantity.

[0155] Subsequently, the sales revenue values ​​calculated from the forms for each account type are linked to the sales revenue values ​​for each item on the summary table using Excel functions.

[0156] 4-2. Form Creation in COGS·SGA Sheets

[0157] In the COGS·SGA sheet, after creating an Excel function to display the ratio of the sum of each item in the summary table to the sales revenue (connected to the Sales sheet via an Excel function), forms corresponding to the analysis method are sequentially generated according to the analysis methods of ⓐ labor costs / ⓑ variable costs / ⓒ fixed costs / ⓓ depreciation expenses listed in column L for each account type below the summary table.

[0158] ⓐ Labor cost analysis method

[0159] After creating a summary table that aggregates only the items analyzed as labor costs, prepare a form so that the ratio to sales and sales (linked to the Sales sheet via Excel functions) are calculated and linked to the summary table. Then, create a form table for labor cost analysis in the row below.

[0160] A table is prepared in the form classifying the nature of items into fixed costs, variable costs, bonuses, allowances, retirement benefits, and welfare expenses.

[0161] Regarding fixed non-cost pay, there is a section for manually entering past fixed non-cost pay and the average number of employees, and an Excel function is provided to calculate past salary per person by dividing fixed non-cost pay by the average number of employees. For the future salary per person growth rate, an Excel function is linked to allow the evaluator to select the expected wage growth rate or expected inflation rate from the Data sheet, and the salary per person for the following year is calculated as Previous Year's Salary per Person x (1 + Wage Growth Rate). The average number of employees for the future year is entered manually, and the fixed non-cost pay for the future year is calculated by multiplying the average number of employees by the salary per person.

[0162] Regarding variable cost salaries, there is a section for manually entering past variable cost salaries, and an Excel function is created to calculate the ratio relative to past sales by dividing past fixed cost salaries by past sales. The ratio relative to sales for future years is calculated based on the assumption that the ratios of the past 1 year, the past 2-year average, or the past 3-year average (selectable) will continue in the future. The variable cost salaries for future years are calculated by multiplying the projected sales for future years (linked to the Sales sheet via an Excel function) by the ratio relative to future sales.

[0163] Regarding bonuses, allowances, and welfare expenses, there is a section for manually recording past figures. Additionally, an Excel function is created to calculate the ratio relative to the past fixed-cost allowance + variable-cost allowance by dividing the total bonuses, allowances, and welfare expenses by the past fixed-cost allowance + variable-cost allowance. The ratio relative to the fixed-cost allowance + variable-cost allowance for future years is calculated based on the assumption that the ratios from the past 1 year, the past 2-year average, or the past 3-year average (selectable) will continue in the future. The bonuses, allowances, and welfare expenses for future years are calculated by multiplying the estimated fixed-cost allowance + variable-cost allowance for future years by the ratio relative to the future fixed-cost allowance + variable-cost allowance.

[0164] Regarding retirement benefits, there is a section for manually recording past retirement benefits, and an Excel function is created to calculate the ratio relative to past fixed expenses + variable expenses + allowances by dividing the retirement benefits into past fixed expenses + variable expenses + allowances. The ratio relative to fixed expenses + variable expenses + allowances for future years is calculated based on the assumption that the ratios from the past 1 year, the past 2-year average, the past 3-year average, or 1 / 12 (selectable) will continue in the future. The retirement benefits for future years are calculated by multiplying the estimated fixed expenses + variable expenses + allowances for future years by the ratio relative to fixed expenses + variable expenses + allowances for future years.

[0165] ⓑ Variable cost analysis method

[0166] After creating a summary table that aggregates only the items analyzed as variable costs, prepare a form so that the ratio to sales and sales (linked to the Sales sheet via Excel functions) are calculated and linked to the summary table. Then, create a form table for variable cost analysis in the row below.

[0167] The form table is designed to calculate the amount, ratio to sales, and sales for each item, and the amount for each item is linked to the summary table. The amount for each item is calculated by multiplying the ratio to sales by the sales amount, and the ratio to past sales is calculated as past sales by item / past variable costs by item. The ratio to sales for future years is calculated based on the assumption that the ratio of the past 1 year / the past 2 years average / the past 3 years average (selectable) will continue in the future.

[0168] ⓒ Fixed Cost Analysis Method

[0169] After creating a summary table by gathering only fixed-cost items and summing them up, prepare a form so that the ratio to sales and sales revenue (linked to the Sales sheet via Excel functions) are calculated and linked to the summary table. In the summary table, the future fixed cost growth rate for each item is linked via Excel functions so that the evaluator can select the expected inflation rate or expected GDP growth rate from the Data sheet, and the fixed cost for each item for the following year is calculated as the previous year's fixed cost x (1 + wage growth rate).

[0170] ⓓ Depreciation analysis method

[0171] After creating a summary table that aggregates only items analyzed as depreciation expenses, prepare a form so that the ratio relative to sales and sales (linked to the Sales sheet via Excel functions) are calculated and linked to the summary table. Then, create a form table in the row below for analyzing depreciation expenses for each item.

[0172] The form table is prepared to calculate depreciation expenses for existing assets, the inflation rate, new investments, and depreciation expenses for new investments for each item. Depreciation expenses for existing assets for future years are to be entered manually, and the inflation rate is entered by linking the estimated inflation rate from the Data sheet using an Excel function. For the investment in depreciation expenses for existing assets for future years, an Excel function is created to calculate the amount increased by the inflation rate from the previous year's depreciation expense x (1 + estimated inflation rate) x depreciation reinvestment ratio, based on assumptions. The investment plan is to be entered manually by the evaluator, and an Excel function is created to calculate new investments by summing the investment in depreciation expenses for existing assets and the investment plan. Depreciation expenses for new investment acquisitions for each year are calculated using an Excel function assuming the straight-line method, and an Excel function is created to calculate the total depreciation expense by summing the depreciation expenses for existing assets and the depreciation expenses for new investment acquisitions.

[0173] 4-3. Creating Forms in the NO Sheet

[0174] For the NO sheet, the future year amount for each item in the summary table is entered as 0.

[0175] 5. Combine the sheets by account type to create the Est_PL sheet and Report_Est_PL sheet.

[0176] Copy the existing PL sheet, rename it to Est_PL, and create a table template for entering estimates for future years. Subsequently, aggregate the amounts from the sheets categorized by account type to prepare an income statement. Finally, create a new Report_Est_PL sheet and refine the Est_PL sheet to create a template that resembles an actual income statement.

[0177] 6. Calculation of Estimated Corporate Tax Based on Operating Profit

[0178] Copy the existing PL sheet, rename it to TAX, and create a table template for entering estimates for future years. Subsequently, prepare a corporate tax rate table based on the codes, and write Excel functions to calculate operating profit by subtracting cost of goods sold and selling and administrative expenses from the Sales, COGS, and SGA sheets. Include adjustment items for each operating profit to allow the evaluator to adjust the amount; finally, calculate the tax amount by writing the corporate tax calculation formula using Excel functions on the adjusted figures.

[0179] 7. Creating Sheets by Account Type in the BS Sheet (Balance Sheet)

[0180] With the balance sheet format containing account types classified as [WC (Working Capital) / FA (Fixed Assets) / NOA (Non-Operating Assets) / IBD (Interest-Bearing Liabilities) / OAL (Operating Assets and Liabilities Other than Working Capital) / CAP (Capital)] in column K, the existing BS sheet is copied and renamed according to account type into WC Sheet / FA Sheet / NOA Sheet / IBD Sheet / OAL Sheet / CAP Sheet, leaving only the items corresponding to each account type to create a table format for estimating future years. Then, each sheet is created sequentially.

[0181] 7-1. Creating Forms in the WC Sheet

[0182] First, create a summary table on the WC (Working Capital) sheet. Separate items classified as assets and liabilities by major category, and write an Excel function to sum the asset items into the Total Working Assets and the Total Liabilities into the Total Working Liabilities. Then, write an Excel function to subtract the Total Working Liabilities from the Total Working Assets to obtain the Total Net Working Capital. Next, write an Excel function to calculate the change in Net Working Capital based on the difference between this year's Total Net Working Capital and last year's Total Net Working Capital. Finally, create a template table for working capital analysis in the row below.

[0183] The form table is prepared to calculate the item-specific amount, the variable factor (sales or cost of goods sold), and the turnover period relative to the variable factor for each item, and the item-specific amount is linked to the summary table. The item-specific amount is calculated as the product of the item-specific variable factor and the turnover period, and the past turnover period is calculated as past item-specific variable factor / past item-specific amount. The ratio relative to sales for future years is calculated based on the assumption that the ratios of the past 1 year, the past 2-year average, and the past 3-year average (selectable) will continue in the future.

[0184] 7-2. Form Creation in FA Sheet, NOA Sheet, and CAP Sheet

[0185] In the FA (Fixed Assets) sheet, NOA (Non-Operating Assets), and CAP (Capital) sheet, create a summary table summing up each item, and leave the amounts for future years blank so that the appraiser can fill them in.

[0186] 7-3. Form Creation in the IBD Sheet

[0187] In the IBD (Interest-bearing Debt) sheet, separate items classified as assets from liabilities by major category. Create Excel functions to subtract the sum of asset items from the sum of liability items and add them to the sum of IBD. Then, create a summary table by subtracting the IBD subtraction from the sum of IBD items to obtain the total IBD. Leave the amounts for future years blank so that the appraiser can enter them.

[0188] 7-4. Creating Forms in OAL Sheets

[0189] In the OAL (Operating Assets and Liabilities Other than Working Capital) sheet, create a summary table to sum each item, and write an Excel function to keep the amounts for future years the same as the previous year.

[0190] 8. Combine the sheets by account type to create the Est_BS sheet and Report_Est_BS sheet.

[0191] Copy the existing BS sheet, rename it to Est_BS, and create a table template for entering estimates for future years. Subsequently, compile the amounts from the sheets categorized by account type to prepare the balance sheet, and create a new Report_Est_BS sheet to refine the Est_BS sheet and format it to resemble an actual balance sheet.

[0192] 9. Generate DCF sheet and Report_DCF sheet by aggregating values ​​calculated from sheets by account type

[0193] Create a form to calculate enterprise value and stock value according to the previously described DCF valuation method by combining the values ​​derived from the sheets for each account type, and generate a DCF sheet by linking the values ​​from each sheet using Excel functions.

[0194] Refines the DCF sheet for reporting purposes to create the Report_DCF sheet.

[0195] The embodiments described above may be implemented as hardware components, software components, and / or combinations of hardware components and software components. For example, the devices, methods, and components described in the embodiments may be implemented using one or more general-purpose computers or special-purpose computers, such as, for example, a processor, a controller, a Central Processing Unit (CPU), a Graphics Processing Unit (GPU), an Arithmetic Logic Unit (ALU), a digital signal processor, a microcomputer, a field programmable gate array (FPGA), a programmable Logic Unit (PLU), a microprocessor, Application Specific Integrated Circuits (ASICS), or any other device capable of executing and responding to instructions.

[0196] The aforementioned method for preparing enterprise valuation and financial estimation forms may also be implemented in the form of a recording medium containing computer-executable instructions, such as applications or program modules executed by a computer. A computer-readable medium may be any available medium accessible by a computer and includes both volatile and non-volatile media, as well as removable and inseparable media. Additionally, a computer-readable medium may include a computer storage medium. A computer storage medium includes both volatile and non-volatile, removable and inseparable media implemented by any method or technique for storing information, such as computer-readable instructions, data structures, program modules, or other data.

[0197] The aforementioned method for preparing a corporate valuation and financial estimation form may be executed by an application installed by default on a terminal (which may include a program included in a platform or operating system installed by default on the terminal), or by an application (i.e., a program) directly installed by a user on a master terminal through an application provider server, such as an application store server, an application, or a web server related to the service. In this sense, the aforementioned method for preparing a corporate valuation and financial estimation form may be implemented as an application (i.e., a program) that is installed by default on the terminal or directly installed by a user, and may be recorded on a computer-readable recording medium such as a terminal.

[0198] Although the present invention has been described above with reference to embodiments thereof, those skilled in the art will understand that various modifications and changes can be made to the present invention without departing from the spirit and scope of the invention as described in the following claims.

Claims

1. A computer program stored on a computer-readable medium for performing a method of preparing a business valuation and financial estimation form, said computer program causing the computer to perform the following steps, said steps being, A step of extracting analysis data from an input file containing basic analysis information and basic financial statements; A step of preparing an estimation data sheet regarding the estimation period corresponding to the above-mentioned basic analysis information; A step of creating a first sheet for each account type regarding the profit and loss statement, and creating a form for each evaluation type within the first sheet for each account type; A step of calculating the corporate tax rate based on the expected operating profit derived by combining the sheets for each of the first account types above; A step of creating a sheet for a second account type regarding the balance sheet and preparing a form for a valuation type within the sheet for the second account type; A step of generating an estimated profit and loss statement and an estimated balance sheet by combining the first account type sheet and the second account type sheet; and A computer program stored on a computer-readable medium comprising the step of performing an enterprise valuation using the above-mentioned projected income statement, the above-mentioned corporate tax rate, and the above-mentioned projected balance sheet.

2. In Paragraph 1, The above first account type sheet includes at least one of a Sales sheet, a Cost of Goods Sold (COGS) sheet, a Selling and Administrative Expenses (SGA) sheet, and a Non-Operating Items (NO) sheet, and A computer program stored on a computer-readable medium, characterized in that the sheet for each second account type includes at least one of working capital (WC), fixed assets (FA), non-operating assets (NOA), interest-bearing debt (IBD), other operating capital (OAL), and equity (CAP) sheets.

3. In Paragraph 1, A computer program stored on a computer-readable medium, characterized by a form prepared to calculate the enterprise value and stock value by applying the discounted cash flow method in the above-mentioned enterprise valuation execution step, calculating the annual operating cash flow, calculating the present value of the cash flow by discounting it to time value, and summing the present value and the residual value.

4. In Paragraph 1, The above input file includes an analysis basic information sheet, a basic income statement sheet, a basic balance sheet, and a discount rate sheet, and In the above analysis basic information sheet, set the reference time and end time of the analysis, and In the above basic income statement sheet, the existing income statement is systematized and entered into the first setting area, and one or more of the first account type and analysis method are set in the second setting area, and In the above basic balance sheet, the existing balance sheet is systematized and entered into the third setting area, and the second account type is set in the fourth setting area, and A computer program stored on a computer-readable medium characterized in that discount rate-related information is set in the above-mentioned discount rate sheet.

5. In Paragraph 4, Prior to extracting the above analysis data, a step of refining the format of the above input file is performed first, wherein A computer program stored on a computer-readable medium, characterized in that the blank parts in the second setting area and the fourth setting area are automatically set by utilizing an external generative AI linked via an API.

6. In the system for preparing corporate valuation and financial projection forms, A data extraction unit that extracts analysis data from an input file containing basic analysis information and basic financial statements; A data sheet generation unit that creates an estimation data sheet regarding an estimation period corresponding to the above-mentioned basic analysis information; A sheet creation unit for each account type that creates a first sheet for each account type regarding the income statement, prepares a form for each valuation type within the first sheet for each account type, calculates the corporate tax rate based on the expected operating profit derived by synthesizing the first sheet for each account type, creates a second sheet for each account type regarding the balance sheet, and prepares a form for each valuation type within the second sheet for each account type; A standard report generation unit that generates an estimated profit and loss statement and an estimated balance sheet by combining the above-mentioned first account type sheet and the above-mentioned second account type sheet; and A system for preparing enterprise valuation and financial estimation forms, comprising an enterprise valuation unit that performs enterprise valuation using the above-mentioned projected income statement, the above-mentioned corporate tax rate, and the above-mentioned projected balance sheet.

7. In Paragraph 6, The above first account type sheet includes at least one of a Sales sheet, a Cost of Goods Sold (COGS) sheet, a Selling and Administrative Expenses (SGA) sheet, and a Non-Operating Items (NO) sheet, and A system for preparing enterprise valuation and financial estimation forms, characterized in that the sheet for each second account type above includes at least one of working capital (WC), fixed assets (FA), non-operating assets (NOA), interest-bearing debt (IBD), other operating capital (OAL), and equity (CAP) sheets.

8. In Paragraph 6, A system for creating enterprise valuation and financial estimation forms, characterized in that the enterprise valuation department prepares an enterprise valuation sheet by applying the discounted cash flow method, calculates annual operating cash flows, calculates the present value of cash flows by discounting them to time value, and calculates the enterprise value and stock value by summing the present value and residual value.

Images