MBA-Case Study

 

Case study

Question: Write steps to insert a piechart using Excel for the following data

particulars	companyA	CompanyB
raw materials	50000	70000
work in progress	45000	34000
finished goods	60000	55000
sapres	20000	25000
Total	1,75,000	1,84,000

And also do the percentages and draw the pie diagram

Answer:Steps to Create a Pie Chart

To create a pie chart in Excel 2007, you will need to do the following steps:

1.     Highlight the data that you would like to use for the pie chart. In this example, we have selected the range A1:B5.

particulars	companyA	CompanyB
raw materials	50000	70000
work in progress	45000	34000
finished goods	60000	55000
sapres	20000	25000
Total	1,75,000	1,84,000

 

 

2.     Select the Insert tab in the toolbar at the top of the screen. Click on the Pie button in the Charts group and then select a chart from the drop down menu. In this example, we have selected the first pie chart (called Pie) in the 2-D Pie section.

 

 

3.     Now you will see the completed pie chart. In this tutorial, the pie chart has 4 slices (one for each division). Each slice represents the sales as a percentage of the total sales.

 

particulars	companyA	CompanyB
raw materials	50000	70000
work in progress	45000	34000
finished goods	60000	55000
sapres	20000	25000
Total	1,75,000	1,84,000

5. Now, calculate the percentage for the given dataset

 

 

particulars	companyA	CompanyB	Total	per
raw materials	50000	70000	120000	8.608321
work in progress	45000	34000	79000	5.667145
finished goods	60000	55000	1150000	82.49641
sapres	20000	25000	45000	3.228121
Total	1,75,000	1,84,000	1394000	100

 

6. Now draw the pie diagram after calculate the percentage for the given data set

 

 

Question2: Give a set of 100 observations explain the steps to

(i)                 Construct a histogram and

(ii)               Find mean and variance and print them using spss

Answer :

Quick Steps

1. Click Graphs -> Legacy Dialogs -> Histogram
2. Drag variable you want to plot as a histogram from the left into the Variable text box
3. Select “Display normal curve” (recommended)
4. Click OK
5. Histogram will appear in SPSS output viewer

 

The Data

As per usual with these quick tutorials, the starting assumption is that you have already imported data into SPSS, and you’re looking at something a bit like this.

The variable we’re interested in out of the three you can see here is height. We want to know how the frequency of heights is distributed. For example, are there more heights at the top end than at the bottom end – in other words, is the distribution skewed? A histogram will go some way to answering this question.

Make a Histogram

The simplest and quickest way to generate a histogram in SPSS is to choose Graphs -> Legacy Dialogs -> Histogram, as below.

This brings up the following dialog box.

You need to select the variable on the left hand side that you want to plot as a histogram, in this case Height, and then shift it into the Variable box on the right. You can do this by selecting the variable, and then clicking the arrow (as above). We suggest you also tick the Display normal curve option, though this is optional. You’re now ready to create the histogram. Just press the OK button.

The Histogram

The SPSS output viewer will pop up with the histogram that you’ve created.

The y-axis (on the left) represents a frequency count, and the x-axis (across the bottom), the value of the variable (in this case Height). You’ll notice that SPSS also provides values for mean and standard deviation.

If you want to save your histogram, you can right-click on it within the output viewer, and choose to copy it to an image file (which you can then use within other programs)

 

Calculate Mean & Standard Deviation in SPSS

Quick Steps

1.   Click Analyze -> Descriptive Statistics -> Descriptives

2.   Drag the variable of interest from the left into the Variables box on the right

3.   Click Options, and select Mean and Standard Deviation

4.   Press Continue, and then press OK

5.   Result will appear in the SPSS output viewer

There are a number of different ways to achieve this, but what follows is probably the easiest.

The Data

We’re working on the assumption that you have already imported your data into SPSS, and you’re looking at something a bit like this (though obviously with different variables, figures, etc).

As you can see, we’ve got three variables: (a) duration – which is the duration in seconds it takes to complete a certain task; (b) sex – male or female; and (c) height – in inches. You want to find out the mean and standard deviation of the duration variable. In other words, you want to know the average time it took to do the task, and how much the times vary – their spread.

The Calculation

To calculate the mean and standard deviation, choose Analyze -> Descriptive Statistics -> Descriptives, as below.

This will open up the following dialog box.

You need to get the variable for which you want to know the mean and standard deviation into the variables box on the right (as per the image above). This can be done by selecting it on the left, and then clicking the blue arrow button.

Now choose Options, and select Mean and Std. deviation (and if you wish you can choose additional measures – e.g., Skewness).

That’s pretty much it. Press Continue, and then press OK in the Descriptives dialog box.

The Result

The SPSS output viewer will appear with the following result (though, of course, the result will be different according to the data you enter).

As you can see, the values for the mean and standard deviation appear next to the value for N (which is the number of items in your dataset).

 Qno. 3

Suppose that every month, you create a report for your accounting manger. You have to format the names of the customers with overdue accounts in red and apply bold formatting. Explain how to create and run MACRO, that apply these formatting changes to the cells you select.

Ans :

Step 1 ->    Create data in excel sheet with field names

          S.no , Customer name, Address , Amount purchased, Due amount

 

Step 2 -> Create report to the accounting manager showing that the customer due amount is not > than Rs 5000/- , Click on conditional formatting  Icon in Styles Group

 

 

 

 

 

 

Step 3 -> after applying the conditional formatting and report the data in Red color and also apply bold formatting

 

 

 

 

 

 

 

 

 

 

 

 

 

Step 4- > now create and run a Macro  that applies these formatting changes, the cells you select

Click Conditional Formatting in the Styles group,à and then click New Rule. ->Click Use a formula to determine which cells to format under Select a Rule

Type.--> Click inside the Format values where this formula is true box. Then, select the cell that you want to use for the conditional formatting

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Question 4: Give a set of 100 observations explain the steps to

(i)                 Construct a Frequency Distribution and

(ii)               A frequency curve using SPSS

Solution

The Data

This is the data set we’ll be using.

The Frequency Distribution Table

To make a frequency distribution table, click on Analyze -> Descriptive Statistics -> Frequencies.

This will bring up the Frequencies dialog box.

 

12 14 14 14 16

18 20 20 21 23

27 27 27 29 31

31 32 32 34 36

40 40 40 40 40

42 51 56 60 65

 

 

Steps to Making Your Frequency Distribution

Step 1: Calculate the range of the data set

The range is the difference between the largest value and the smallest value. We need this to figure out how much “space” we need to divide into groups. In this example:

Range=65−12=53  Range=65−12=53

Step 2: Divide the range by the number of groups you want and then round up

Doing this allows us to figure out how large each group is. It’s as if we are going to cut a board into equal pieces. In step 1, we measured how long the board is and now we are deciding how big each piece will be.

Hmmm… but how many groups to have? Too many, and our graphs and tables won’t be much better than a list of numbers. Too few, and the pattern will be hidden with too little detail. Often, a good number of groups is 5 or 6 although there are some rules that people use to decide this. MORE OFTEN, people will let the computer decide and then adjust if they want to while textbooks will tell you how many groups to use. But if you are working with the dataset yourself, you will have to see what the graph looks like before you can be sure you chose a good number.

Let’s say that we choose to have 6 groups. If we do this then:

536=8.8536=8.8

The number we just found is commonly called the class width. We will round this up to 9 just because it is easier to work with that way. A computer would probably keep the 8.8 so be aware that sometimes you will see this number as a decimal. NOTE: In general, people e who are doing this by hand always round up even if it was 8.1!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step 3: Use the class width to create your groups

I’m going to start at the smallest number we have, which is 12, and count by 9 until I have my 6 groups. For example, my first group will be 12 to 21 since 12+9=21. My next group will be 21-30 since 21+9=30… and so on. I’ll put these in a table and label them “classes”. I will also add “frequency” to the table.:

CLASSES	FREQUENCY
12 – 21
21 – 30
30 – 39
39 – 48
48 – 57
57 – 66

Step

 

 

 

 

4: Find the frequency for each group

This part is probably the most tedious and the main reason why it is unrealistic to make a frequency distribution or histogram by hand for a very large data set. We are going to count how many points are in each group. Let’s start with our first group: 12 – 21. We want to count how many points are between 12 and 21 NOT INCLUDING 21. You see the overlap between the groups right? That’s to account for decimals and we keep it even when we don’t have any. The right hand endpoint of any group isn’t included in that group. It goes in the next group. That means 21 would be in the second group and any 30 we have would be counted in the third group.

Back to the first group: 12-21. I have circled the points which would be included in this group:

Alright – now I update the table with this information!

 

 

 

CLASSES	FREQUENCY
12 – 21	8
21 – 30
30 – 39
39 – 48
48 – 57
57 – 66

 

 

Continuing with this pattern (each group is a different color!):

CLASSES	FREQUENCY
12 – 21	8
21 – 30	6
30 – 39	6
39 – 48	6
48 – 57	2
57 – 66	2

 

 

That last table is our frequency distribution! To make a histogram from this, we will use the groups on the horizontal axis and the frequency on the vertical axis. Finally, we will use bars to represent the the frequency of each individual group. With this data, the finished histogram will look like the one below.

 

 

 

 

QNO: 5

a)      Write steps to create  a data file in Excel for the following  data

Year	Percapita Income(in current prices)
1994-97	3285.4
1997-98	3842.0
1998-99	4346.5
1999-2000	4346.5
2000-01	4983.0
2001-02	5602.9
2002-03	6255.0

                                                            

(i)                 Determine the maximum percapital income for all the years and print it

(ii)               Determine average percapital income for all the years and print it

(b) Also write the steps to present the data graphically and store it in a separate file

 

 

Solution

a)      (i) Determine the maximum percapital income for all the years and print it

 

Function

=max(b13,b14,b15,b16,b17,b18,b19)  = 6255

(II) Determine average percapital income for all the years and print it

 

Function

Average(f4,f5,f6,f7,f8,f9,f10) = 4665.9

 

(b) Also write the steps to present the data graphically and store it in a separate file

Step 1 : select the data from the excel sheet

 

 

 

 

 

 

 

 

 

Step 2 -> click on insert  in the MENU BAR, in that select Charts group ( required charts for display of data)

Step 3- > Now select the column chart ( 2-D column )

 

Step – 4, save the  graph in separate file.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Qno 6.

ABC limited company is preparing month report for its sales. Sales include both cash and credit sales . you want to format the name of customers who turn as bad debts in green color, and also apply italic style, explain how you will apply in excel

Hint : see the procedure of Question -3 – practice