Business Data Analysis- Fennal Design
Module code: BSS002-6 Module Name: Business Data Analysis
Submission: 30 November, 2012
I my class I learn how to simulate data to solve a problem. I learn to use excel and some function in it these are =SUM() = RAND() =AVERAGE() =STDV() =RANDINV() =VLOOKUP() and few more . These are widely used function when I simulated data in excel.
In task 1 I find out how to calculate or forecast how much card should we print. I use a random variable with =RAND( function and use =VLOOKUP() function to find out demand from discreet variable called cumulative probabilities. This is discrete because we find out the range by frequency distribution. Then I use if function to calculate disposable cost with =IF (Demand>Production,(Demand-Production)*Disposable cost, 0). This function works with logical criteria. It says that, if demand is greater than production then disposable cost will be (Demand-Production)*Disposable cost, if not then “0”. Then I calculated profit. Then I simulated 1000 trial with 4 types demand to find out with demand will give us maximum profit and this was $40000. If we print $40000 cards then we can make profit of $57758 which is higher than other. Then we use DATA ANALYSIS add-in to simulate descriptive statistic. From here we can answer question no 2 also. If you print 20000 cards then will make profit of 46178 which is less than what can we make in 40000 printed cards. In here simulated demand is uncertain variable when is used for forecasting.
For task 2 we make a profit model, It is given in below,
Profit Model= (Selling Price –Direct Labor-Part Cost)xDemand-Fixed Cost
And I calculated profit with this model. I critically analysis base case, worst case an best is. Worst case is, when cost is higher but demand is very low. In base case everything lies in middle and the best case cost is very low but price is very high. So in the 3 type of case best case gave us best profit. So I calculate some data with each
Submission: 30 November, 2012
I my class I learn how to simulate data to solve a problem. I learn to use excel and some function in it these are =SUM() = RAND() =AVERAGE() =STDV() =RANDINV() =VLOOKUP() and few more . These are widely used function when I simulated data in excel.
In task 1 I find out how to calculate or forecast how much card should we print. I use a random variable with =RAND( function and use =VLOOKUP() function to find out demand from discreet variable called cumulative probabilities. This is discrete because we find out the range by frequency distribution. Then I use if function to calculate disposable cost with =IF (Demand>Production,(Demand-Production)*Disposable cost, 0). This function works with logical criteria. It says that, if demand is greater than production then disposable cost will be (Demand-Production)*Disposable cost, if not then “0”. Then I calculated profit. Then I simulated 1000 trial with 4 types demand to find out with demand will give us maximum profit and this was $40000. If we print $40000 cards then we can make profit of $57758 which is higher than other. Then we use DATA ANALYSIS add-in to simulate descriptive statistic. From here we can answer question no 2 also. If you print 20000 cards then will make profit of 46178 which is less than what can we make in 40000 printed cards. In here simulated demand is uncertain variable when is used for forecasting.
For task 2 we make a profit model, It is given in below,
Profit Model= (Selling Price –Direct Labor-Part Cost)xDemand-Fixed Cost
And I calculated profit with this model. I critically analysis base case, worst case an best is. Worst case is, when cost is higher but demand is very low. In base case everything lies in middle and the best case cost is very low but price is very high. So in the 3 type of case best case gave us best profit. So I calculate some data with each