A strong business is basically built on data and analysis using historical figures for assessment and evaluation while preparing to plan a business. Having the funds to start a business without proper data analysis is similarly to committing a suicide.
We will shed more light in the next paragraphs on the fundamentals of data analysis for proper dimensioning of the needed assets and manpower needed for a business study.
Linear programming (LP) is a useful technique for planning and dimensioning factories, production unit, assemblies and distribution. LP is a simulator to optimally achieve the set objectives (either maximize the profit or minimize the costs) using the given constraints (resources, products and machinery).
Bring the data, set the objective and let the excel do the work. “Solver” is your Saver.
- Start the excel, go to File – Option – Add-ins – Go… – Select Solver Add-in – Ok
- Select Data tab and you will see Solver on the top right side
- Once seen, your sheet is ready to solve your objective
Depending on the objectives, then tabulate the given data to formulate the equation related to the constraints. Pay attention that to maximize the profit use “<=” while for minimizing the cost use “>=”.
Once all the functions are set, the Solver will calculate the outcomes:
- Click on Solver
- Choose the cell related to the objective
- Select the “Min” or “Max” (depending on your case)
- Select the variables related to the business
- Add the constraints
- Then press on Solve
Interpreting the results is very important to understand the results and accordingly adjust the values to fit your business case (whether to optimize the machinery usage or increase products to match the desired profit).
Other tools are used in Data Analysis using previous business records; the Correlation and Regression. Using the same steps to add Solver, select “Analysis ToolPak” to add the needed tool.
Having the data listed on excel, properly organized, the Correlation tool will do the work.
- Select Data Analysis
- Choose Correlation
- Select the data “input range”
- Tick “label in first row”
- Choose empty cell for the result and “Ok”
The result will show the correlation between the dependent and independent variable. The values will be between -1 and 1. If the value is positive higher than 0.5, it means there is a strong correlation between the variables and if the values are less than 0.5, it means a weak correlation.
Moving to Regression (more detailed and deep analysis), follow the same steps by selecting the range of data and choose where to have your results and enjoy the summary ready for interpretation:
Regression statistics will show multiple results. However, the interpretation of data is focused on:
- R Square: it shows the percentage of good the data will fit the regression model
- F and Significance F: it represents how the data are significant
- Coefficients: can be positive or negative. These are factors multiplied by the variables
- P-Value shows whether there is a significant relationship between variables (<=0.05) or not significant (>0.05)
Practicing is recommended in order to make sure that no errors or wrong results will be generated and might lead to wrong business decisions and analysis.
In the end, let us keep in mind this quote by W. Edwards Deming: “In God we trust. All others must bring data”.
