Data Mining&Business Planning of Engineering/Research Projects

Data Mining&Business Planning of Engineering/Research Projects Presentation 9 Dr. Gábor Pauler, Associate Professor Faculty of Sciences, University of Pécs Tel:30/9015-488 E-mail:pauler@t-online.hu

Content of the Presentation • Handling of PaulerSoft™ BusinessPlanner 2.0, Part 1 • Introduction, Basic terms • Product Lifecycle • Sampling Plan • Survey Template • System settings • Currency- and time units • Product description • Product parameters • Basic unit, Unit price levels • Definition and size of target market • Considering competition • Product database • Demand estimation • Forecast demand in product lifecycle, reliability of forecasting • Aggregating forecasted demand into empiric demand data • Nonlinear demand function fit, estimating unit utility and basket size, reliability of demand estimation • Calculating unit costs of product • Handling cost calculation tables • Handling VAT • Handling inflation • Inventory and capital cost of inventory • Sales- and profit maximizing prices • Estimated demand, revenue and gross profit at these prices • References

PaulerSoft™ BusinessPlanner 2.0: is an MS Excel-based business planner software, which – against the mainstream of business planners (eg. Business Plan Pro http://www.paloalto.com/ps/bp/ ) – stresses on the best possible statistical estimation of expected demand/ revenue/ profit of your product, fitting better to innovative products: It handles 4 type of product lifecycle (Termék-életciklus) models for better forecasting of future demand It helps to optimize pricing of your product through its whole lifecycle It helps to optimize financing your project with a requirement-based model: it does not use any standard credit construction of a commercial bank, but helps you to bargain with the bank the best payback schedule to minimize your interest costs Limitations of the system: It does not handle qualitative (Nem számszerű) business planning tools (eg. STEP, SWOT. See them in: Presentation 1) It does not optimize make-or-buy (Gyártsd vagy vedd) decisions and inventory management (Készletezés), because you can find range of enterprise resource management (ERP) systems (Integrált vállalatirányítási rendszer) on the market solving these problems Sample data used: We present usage of the system using Miracle Roof case study PaulerSoft™ BusinessPlanner 2.0: Introduction

Every product is consumed in consumption periods (Fogyasztási periódus):how frequent-ly shopping decisions are made (eg. bread: 1 day, buildings:20-25 years) Product lifecycle (Termék életciklus) is chan-ge of demand across consumption periods: There are one-time consumed (Egyszeri fogyasztású) products (eg. software: 1 guy buys other download free from torrent). Here we cannot make any demand forecast There are impulse (impulzus) products (eg. fashion- and architectural styles). Demand at time period t can be forecasted by impulse lifecycle function (Életciklus függvény): Demand(t) = t × PeakDemand / PeakTime × × Exp(1 - t / PeakTime)(9.1) Its parameters PeakDemand, PeakTime are estimated from market research data query-ing time period of intended purchasing Regularly(Rendszeresen)consumed products (eg. bread, building maintenance) demand is forecasted simply by it average Increasing (Növekvő) product’s demand is forecasted with linear lifecycle function: Demand(t)=t × Slope + Intercept (9.2) Parameters Intercept, Slope are also estimated from market research data Please note that while our example, Mira-cle Roof is most likely impulse- or one-time product for most of the customers, its maintenance as a separate product/ service can be continuosly consumed! PaulerSoft™ BusinessPlanner 2.0: Basic terms: Product Lifecycle Demand, units Peak (Csúcs) Demand, units Consumption period Peak Demand (Csúcskereslet) Peak time (Csúcsidő) Lecsengés (Decay) Take off (Felfutás) Consumption period Demand, units Consumption period Demand, units Intercept (Konstant) Slope (Meredeks.) Consumption period

PaulerSoft™ BusinessPlanner 2.0: Basic terms: Sampling Plan As we already mentioned in Presentation3, demand estimation necessary for quantitative business planning should be based on a Representative Sample (Reprezentatív minta) of prospective customers identified by brainstorming and focus group. It can be ensured by correct Sampling Plan (Mintavételi terv): Eg.in Miracle Roof Case Study,target market is iden-tified as roof spaces above 100m2 area, owned by public or private real estate companies (Ingatlancég) with capitalization between EUR 18M-30M Aim of the sampling plan (Mintavételi terv) is to create a sample,where most important properties of respondents (Válaszadók) are representative to basic population: In the first step, we conduct research in specialized databases of a given area (eg. Hungarian Chamber of Architects (Magyar Építészkamara) http://www.mek.hu) to get the total number of prospective customers in tar-get area (eg.: 10,175 buildings), and their frequency distribution (Gyakorisági eloszlás) by their most impor-tant properties: Ownment:Public|Private (Tulajdonlás: önkormányzati|magán), Capitalization, M EUR (Alap-tőke, Millió EUR), Roof area, m2(Tető alapterület, m2) Then we define a filter about our target market (eg. EUR18M ≤ Capital ≤ EUR30M AND 100m2≤ Area) Then we sum up number of prospective cutomers comp-lying with the filter: this is Target Market Size (Célpiac-méret)(eg.: 504 buildings) Then we compute Frequencies of property values inside filter (Szűrőn belüli arányok) (they sum up to 100%) Then we compute Cross-products (Descartes-szorzat) of frequencies of property values to get quotes (Kvóták) should be kept in sample: (Eg. 49%(Public)× 35.7% (EUR21M-25M)×42%(500-750m2) = 7.4% of sample) To get at least 10%sampling rate (Mintavételi arány) S=100% S=100% S=100% × × × × × × we should collect at least 504×0.1 ≈ 51 valid responses from house ow-ners by interwievers (it means cost of several times more contacts)!

On the questionnaire, we have to apply the following set of questions to make de-mand estimation working: In the introduction of „Product ideas” chapter of the ques-tionnaire, „how valuable they are” is the buzzword. It is followed by short and OBJECTIVE introduction of the product idea. The ques-tionaire IS NOT promotion tool. Even slight pressure on respondents will totally distort results! First question is about unit price of the product unit given by focus group. The first price is always 0 (don’t buy), follo-wed by low-, medium-, high prices set up at focus group. The second question is about product lifecycle presented in simple english ( you should not bomb the respondents with professional terms in the questionnnaire) The first al-ternative here is also don’t buy. Then, respondents can select best fitting lifecycle model to their behavior. PaulerSoft™ BusinessPlanner 2.0: Basic terms: Survey Template1

The third question is about quantity of product units plan-ned to purchase in 5 conse-cutive future consumption pe-riods (Fogyasztási Periódus): this is the shortest time the consumer on the tartgeted market able to make autono-mous purchasing decisions. Its lenght highly depends on the type of products (eg. bre-ad: 1day..buildings: 5-25year-s).It can have identical lenght or multiples of financial de-sign period (Pénzügyi Terve-zési Periódus) of the client. Lenght of consumption peri-ods should be selected that way to allow 5 periods to co-ver possible major turning po-ints in product lifecycle (take off, peak, decay, exit) Important: we always offer the possibility to the respon-dent giving zero quantities in case he will not purchase, to avoid pressure on him Consistency of the respon-dent can be checked compa- ring future quantities and life-cycle pattern selected (eg. if increasing quantities AND buy once is selected, we shall dispose respondent) PaulerSoft™ BusinessPlanner 2.0: Basic terms: Survey Template2

At Settings|Beállítások worksheet, we can modify system settings. (Color codes of input, parameter, partial results, output fields are the same as at Budget Objects): What is the abbreviation of currency unit) (eg. EUR, Ft, $) Average number of days in a month, quarter, year? (Don’t modify!) What is the financial planning period? (In general, this is year) What is the starting date of the project? (It is recommended to match it with starting date of financial periods) Number of financial periods to forecast data? (Min. 5 .. Max. 121. We should give that many which can accommodate majority of lenght of the product lifecycle. (eg. at Miracle Roof System 25 years) What is the reqested reliability of forecasting product lifecycle?(75% is OK, don’t touch) What is the requested reliability of estimating demand function? (80-85% is fine) PaulerSoft™ BusinessPlanner 2.0: System Settings

On the next 4 worksheets (Prod1Param|Term1Param, Prod1Data|Term1Adat, Prod1Demand|Term1Keresl, Prod1UnitCost|Term1EgysKts) a product is defined, whose parameters can be set at worksheet Prod1Param|Term1Param: All products should get unique ID (eg. if you try to sell a roof system and its maintenance, it should be accounted as 2 separate products, because you can sell a roof for 1 customer only once, but yearly maintenance service repeatedly) Short description and basic measure unit:(in general pieces, for roof system, m2) Product packaging size in units: (If not packed=1, here 880m2average roof area) Delivery size in units: (If it is no delivered physically, write 1, here 880m2) Production batch (Termelési Széria) size:(Number of products can be produced with 1 setting of production lines. (For a roof, write 880m2average roof area) Low-, Medium-, High unit price levels for a given unit:(come from earlier focus group) PaulerSoft™ BusinessPlanner 2.0: Product Parameters 1

Consumption period: it should be equal or multiples of financial planning period. 5 consumption periods should cover major turning points in product lifecycle Customer unit: in general it is household, person, company (eg. at Miracle Roof: as buildings can have different roof area, customer unit there is roof area, m2) Code/description of tartgeted market segment: (eg. as we have 1 targeted segment (roof with area>100m2 and owner capitalized between EUR18-30M) write there 1) Size of target segment in customer units:(eg.at Miracle Roof: we already know that there are 504 buildings in target market. Then we compute their weighted average roof area on a separate sheet: 880m2. So total market size is: 504×880m2 = 443,520m2) Expected market share in target segment: even if you think your product idea is very unique, you ALWAYS have existing competitors with at least partially compatible product, or potential ones, who can quickly copy your product! The best prudent estimation of your expected market share is your share from highly skilled workforce necessary for production of the product (eg. at Miracle Roof, the company has 7 skilled tiler who can handle safety film, while cometition has 21 in total, so expected share is 7/28 = 25%) PaulerSoft™ BusinessPlanner 2.0: Product Parameters 2 × =S

At Prod1Data|Term1Adatok worksheet we fill green input cells with the database of survey (1 row = 1 questionnaire, columns contain numeric codes of answers). (in Miracle Roof, Segment code is 1 for all 125 respondents as we used only segment 1) PaulerSoft™ BusinessPlanner 2.0: Product Database

At Prod1Demand|Term1Keresl worksheet, first we give in the yellow cell the code of market segment (=1) whose demand is estimated. Then the system summarizes from the survey database consumption quantities of respon-dents in 5 consumption periods × 4 lifecycle models × 4 unit price levels into blue cells. If Excel asks to refresh pivot table, press OK, but do not modify other parts of the sheet!. Then, at all unit price levels of Impulse and Increasing lifecycle model, the system tries to forecast demand in time. In the Reliability column, it gives reliability of forecasts (0%:bad,100%-perfect). If reliability is under the requirement given in Settings|Beallítások sheet, or there were no data there, forecasting is replaced with time-average of quantities PaulerSoft™ BusinessPlanner 2.0: Demand Estimation 1

Then, forecasted quantities are summarized in each time period, at each price level. As they were aggregated from a small sample, they are upscaled to the size of the target market and our expected market share there: Upscaled quantity = Quantity × Target market size × Expected market share (9.3) Sample size If one would buy the product for higher price, we can assume that he would also buy it for lower price. So in the next step, quantities of higher price levels are added to quan-tities of lower price levels in each period, creating empiric demand data (Tapasztalati keresleti adatok) describing demand in product units at the given unit price levels (see + marks in blue shades for the first 5 periods on the diagram) PaulerSoft™ BusinessPlanner 2.0: Demand Estimation 2 + + +

Then the system tries to fit a continous demand function curve to this data in each time period separately with non-linear curve fitting (Nemlináris görbeillesztés) (see curves in blue shades for the first 5 periods on the diagram). It has 3 results in each time period essential for pricing, revenue-, and profit estimation later: Unit utility (Egységhasznosság) parameter of demand functions Basket size (Kosárméret) parameter of demand functions Reliability of demand estimation (0%:bad, 100%:perfect). If it is under the requested value given in Settings|Beállítások sheet: In any of the first 5 period: the system rejects any further computations, and you have to repeat survey on a bigger sample with improved questionnaire. In further periods: parameters of the last correct period are used to current one PaulerSoft™ BusinessPlanner 2.0: Demand Estimation 3

If we have demand functions and we determine unit costs of product then we can do pricing and compute revenue and profit. This is in Prod1UnitCost| Term1EgysKölts worksheet, which contains a cost calculation table (Költségtábla). Let’s see how to handle it: Rows of table are identified by Item code. If we would like to insert a new row into table, we can do it after selecting its place with Insert|Rows menu, then copy content (cell formula) of an existing, empty table row into the new row through clipboard (Edit|Copy, Edit|Paste) Modify its item code to unique value (eg. if it goes between rows 2.0 - 3.0, code can be 2.5) Then in column Item type we should set the fuction of new row: +: simple positive income item, -: simple negative cost item, =: balance of items above, added to next item, ∑:sum of items above, not added to next Give Item description and Quantity: this will be multiplicated with its unit value (income or cost) given in time periods, eg. 4.50 pieces/m2 × - 2.50EUR/m2 = - 11.25EUR/m2 PaulerSoft™ BusinessPlanner 2.0: Calculating unit cost 1

Then, give Measure unit, and its Basis: basis can be 1 unit/ 1 package/ 1 delivery or 1 batch of product given at Prod1Param|Term1 Param sheet. (eg. Basis is useful when the delivery cost is given by racks. We don’t have to compute by head how many product units can be carried on a rack. Instead we can give the cost by delivery size and, the system divides it into product unit automatically) If we write negative number into Quantity column, this means that the item has changing quantity in time period, which comes from a separate row: its item code is the negative number. These quantities are multiplicated with the item’s given unit value at each time period separately. (eg. row 17.0:Pieceworker employees social security tax rate refers to row 16.0:Pieceworker employes net salary to compute tax amount: -1 {minus row}× (- 30EUR/m2 {comes from row 16.0} × - 43.40%) = - 13.02EUR/m2) PaulerSoft™ BusinessPlanner 2.0: Calculating unit cost 2

In the cost calculation table we can collect many different type of unit costs of the product: Direct material cost (Direkt Anyagköltség), Packaging cost (Csomagolási Költség), Pieceworker Employees/Contracted Net/Gross Salary(Alkalmazott/szerződésesek, nettó/bruttó darabbére): At contracted worforce, we use net salary only At employees, we consider Medicare (Egészség-ügyi járulék) and Social security (Nyugdíj járulék) Time workers salary and tax is NOT here!!! Utility Cost (közüzemek) directly necessary for 1 pro-duct unit (eg. heating of offices is NOT here!!!) As these costs are usually difficult to compute for one product unit, we will use our previously prepared Bill of Material (BOM) in Budget Objects to estimate them: First we calculate with that cost of smallest possible quantity/ size of product Then we continuosly increase product quantity/ size through parametering, to asses how much cost increaes by adding 1 product unit in average. Handling of Value Added Tax, VAT(Általános Forgalmi Adó, ÁFA): to simplify planning, all costs billed for us (even contracted salary) are considered in Net value (Nettó érték) - without VAT – because we can redeem VAT later when we sold our product to end user. Handling of inflation (Infláció): to simplify planning, all future costs are considered on real value (Reálérték) without the effect of inflation, if we can assume that our product’s price increases at the same rate as price of raw materials and labour. We consider future price increase only at electricity, fuel, etc. If viability of the project depends on inflation rate, its not worth to do at all! BusinessPlanner 2.0: Calculating unit cost 3

There can be variable costs not only for 1 product unit but for a Batch (Termelési Széria): Research&Development, R&D (Kutatás+Fejlesztés, K+F)(eg. at Miracle Roof, costs of developing UV-resistant adhesive of safety foil) Kaizen costs (A termék folyamatos termelés közbeni fejlesztésének költségei): in this japanese-originated quality management method, employees are financi-ally motivated to discover product faults and even stop production if necessary to eliminate scrap cost. Of course motivation and management have cost also. Also, there can be Setup (Gépbeállítási), Auxiliary Material (Segédanyag), Total Quality Management , TQM (Teljes Körű Minőség-menedzsment, TKM), Maintenance (Karbantartás), Promotion (Promóciós) costs connected to batches (eg. at Miracle Roof, scaffolding (Állványozás) of the building) Batch costs are followed by one part of Supply Chain Costs (Terítési költség): these are accounted on a product unit (eg. delivery of 1 unit/ 1 package, etc.), and not as % of sales revenue (eg. sales tax and duty): AtInventory cost (Raktározási költs.) wedifferentiate: Utility cost of inventory (Raktár rezsije) (eg. heating, electricity, guards, etc.) Inventory Capital Cost (Raktárkészlet finanszírozási költségei): this can be far bigger item than utilities! We have to pay for materials when we purchase them, but we can get back our money only later when we can sell our product. The longer the time gap, it grows exponentially because of interest (Kamat) rates: If inventory is financed by current assets loan (Forgóeszköz-hitel), we have to pay interest If we purchased it from own capital (Saját tőke), we cannot put that in the bank, and loose interest BusinessPlanner 2.0: Calculating unit cost 4

At the bottom of worksheet Prod1UnitCost|Term1EgysKölts, first we can see summary of the costs above in Variable Costof Unit (1termékre jutó változó költség)(eg. EUR162.86) The estimated sales maximizing/dumping unit price (Forgalom maximalizáló egységár/ Dömpingár) comes from estimated unit utilities in time periods at worksheet Prod1Demand| Term1Keresl. (eg. EUR172.19..149.46) If there are no sufficient data for demand estimation, the system writes here „BAD|ROSSZ" and denies any further computations. Profit maximizing unit price (Profit maximáló egységár) is the sum of dumping price and variable cost of unit. You can select unit prices in time between these two price levels. Chosen Gross Unit Price (Választott bruttó egységár) is by default the profit maximizing price – as the basic purpose of business planning to show maximal profitability for the bank or investors – but this can be overriden later manually, if you want market share! At VAT(ÁFA) you should give the inverse percentage: (eg. if VAT=25%: 1 - 1/(1.25) = 20%) After this, we collect Gross value-based % costs (Bruttó érték százalékában megadott költség): Excise (Termékadó), Fee (Illeték), Duty (Vám), Comission (Jutalék) After these, we get Net margin on unit (Termékegységre jutó fedezet), the net money inflow from selling 1 product unit Demand(Kereslet)is computed using chosen unit price in demand function.We multiply it: With chosen unit price, to get Net Sales (Nettó árbevétel), With Net margin on unit, to get Net Margin (Összfedezetet) from the product PaulerSoft™ BusinessPlanner 2.0: Calculating unit cost 5

References • Demand estimation: • Theory: http://www.sbe.csuhayward.edu/~clee/l2/ • http://www.econ.iastate.edu/classes/econ437/hayes/spring97/feb20/sld002.htm • Demand estimation in geoinformatics: http://www.gisdevelopment.net/application/Utility/transport/ma03085abs.htm • Cash-flow analysis: • Theory: http://www.cash-flow-analysis.com/ • Wheatworks CF-analyzer: http://www.wheatworks.com/cashflowcalculator.htm • Business Planning Software: • Business Plan Pro: http://www.paloalto.com/ps/bp/ • Hyperion: http://www.hyperion.com/products/applications/modeling_optimization/business_modeling/ • Silverrun: http://www.silverrun.com/bpm.html • Computer associates: http://www3.ca.com/Solutions/SubSolution.asp?ID=3819

Data Mining&Business Planning of Engineering/Research Projects