Brandon Peterson5/6/15Marketing 202

Bianca has continued her research on her soup cart and has used the DePaul University Marketing 202 class to conduct a survey. She had the students conduct a survey on how many times they would eat soup per week, their preference for soup, their price range, their soup preference, and their gender. The information gathered by the 202 students has been uploaded into an excel chart and will be used to calculate the Demand estimations of soup sold based on the survey. The excel chart has been sorted by respondents down the rows with their preference rating, frequency, price, preference, and gender along the columns based on the respondents answers. The total number of surveys totaled 616 by taking the =Count of all the Rows. The next step is to calculate the Category indigence frequency and chart.

Calculating CIF:

First you must take the eating frequency and copy it into a new sheet along with the Responses and encoding to go along with it. Once you have done that you will take the total by doing =count of all the rows. Once that has done we will sort the responses by highlighting all the encoded responses and going along the top tab to data. Once there you will select filter and then lowest to highest and you will now have all you encoded responses in order and grouped. Next youll make columns of response item response Frequency and category incidence per month. Since you have the coded responses in order you will enter =count and select all of the 1 codings to get the total of responses for twice or more per week. You will continue to do this for the other response codings giving you the number of respondents per frequency. The next step is to take the Category Incidence of soup eaten per month. To do so we will make anyone who eats soup once per month have a category incidence of 1. For those who eat soup twice a month you will have a category incidence of 2. You will continue to adjust the number based off of the times per month. For the people who eat soup 2/yr. you will calculate the category incidence by dividing 2 month divided by the 12 per year to give you .166667. You will do the same for 1/year but it will be 1 moth divided by 12 this time.

Creating the Graph:

Given your information above you will now create a Column chart to show your findings. First you will go up to the top again and select insert and then column chart. It will insert a blank square and that you will click on and choose select Data from the top. For the Legend Entries we will have a series name of category incidence per month and the series values will be your response frequencies at each coding. Then click OK to go to the previous screen. Now you will click edit and select the horizontal axis. For the Axis label range you will select the label for the response frequencys (Ex: 2 or more, 1/week, 2/ month, etc) to give you the labels for your columns. Once again you will select OK and go to the previous screen. Click Ok at the bottom one more time and you will now have you Category incidence frequency Chart.

Category Incidence Frequency

Next we will be start by calculating the Soups per month for each coding. You will find these numbers by taking you Response per coding and multiplying it by the Category Incidence for that coding. For 2 or more you should have 72 respondents and a category incidence of 8 giving you a total of 576 soups per month. You will do this down each row giving you the soups per month for each coding. Once completing that you will go to the very bottom and calculate the sum of the soups by doing =Sum( ) and selecting all the rows you just calculated in that column totaling 1526.42 soups per month. In order to get the Incidence frequency per month we must take that total and divide by the total number of respondents. To do so you will enter =1526.42/616 to equal 2.48 soups each month per person. Next we need to calculate the Incidence frequency per year and day. To calculate per year you will take you per month answer and multiple it by 12. For per day you will take the per year response and divide it by 365 or the days in a given year and now will have your incidence frequency for day, month, and year.

5000 Students and secondary location at 1250

To calculate the soups sold in a day with 5000 students passing the soup cart we will simply take the CIF per day and multiple it by the 5000 students. You will select the column with you answer of .0814 and do =.0814*5000 to get 407.33 soups sold that day. If you decide to move you cart to a secondary location which only has a total of 1250 pass by it in a given day you will do the same work as above to calculate soups sold. Once again you will multiple you CIF*1250 to give you an answer of 101.83 soups sold on that day in that location.

Open 4 days a week for location 1 and 6 days at location 2

Given that you will only have you soup cart open 4 days at location 1 you can predict the demand of soups by taking you calculation for one day of soup sales above and multiple it by the other 4 days of the week. You will take the answer of 407.33 and multiple it by 4 to give you 1629.32 soups over the 4 days at location one given you have a consistent market place of 5000 people. When you move to location 2 who sees an average of 1250 possible consumers in a given day you will also take you answer from above but this time youll multiple by 6 days. Youre calculation will be the 101.83*6 days giving you 610.98 soups sold in that 6 day week.

Calculating gender and race CIF

Just as you did for the first part with the frequency you will do with the gender and race. Once again you will copy them over, filter them, grab the totals. To calculate the frequency per race we will have to take our CIF total from above per month and divide the total of soup sold per month by per race to calculate the CIF for each race based off the CIF of per month. We will also have to do the same for the Gender of each respondent. After filtering and calculating the Races youll be left with 318 for Hispanic, 126 for black, 75 for white, and 97 for other. You will take you answer of total soups per month and enter =318/1526 for black and so on for each race. You will get a CIF of .208 for Hispanic, .0825 for Black, .0491 for white, and .0635 for other giving you your CIF. You will do just the same for gender. Your totals after filtering should break down to 315 males and 301 Females. For calculating the CIF you will take those numbers and divide them by the total soups per month once again to get the CIF per male and female customer. The totals should come out to be a CIF of .206 For males and .197 for females.