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 demand and make a graph to understand the break even points so that we can improve our system to generate more profit.
For task 3
I took the selling price 249 and took admin and advertising cost of total 1000000. Then we took 500 trial and simulated random variable with =RAND() function. Then I use =VLOOKUP() function to allocate labor cost to the random variable. Then I calculate part cost with low part cost-r(High-Low) equation. Where low part cost is 80 and high part cost is 100 and r=random value.
Then I use =NORMINV() function, which works with mean, probability and standard deviation to predict demand. Then I calculated profit with the profit model that we created earlier. From that 500 trial we got our average labor cost, part cost demand and profit. From that table I find out maximum profit and minimum profit. Find out the risk and probability of loss. After 500 trial I saw that only 30 trials are facing loss to I divide 30/500=6%, which is the probability of loss.

Then I was asked to find out the frequency of simulated profit. Frequency counts each item within a range to measure the tendency of centralization. We put the lowest and highest profit and create a pivot table. With that we make a continuous distribution table and find out the tendency of profit to centralized

At the end I made some graph to give a better understanding. The data simulation process is fast and reliable. If we could take more data than the chance of error could be less. We tried our best to analyze these data accurately and interpreted then effectively. I cannot say that I got a better result because we all are working with random variables so might be other results will be more good than my result. But I do it accurately how I learnt from my class room.