See how to create a Frequency Distribution, a Cumulative Freqency Distribution, a Histogram Chart and an Ogive Chart with the Data Analysis Add-in.Chapter 02 Busn 210 Business and Economic Statistics and Excel Class This is a beginning to end video series for the Business & Economics Statistics/Excel class, Busn 210 at Highline Community College taught by Michael Gel Excelisfun Girvin
http://www.youtube.com/watch?v=-P5h45EDOm0&hl=en
Showing posts with label Analysis. Show all posts
Showing posts with label Analysis. Show all posts
How Can Excel Pivot Tables Increase Your Productivity in Data Analysis?
If you are interested in how to quickly analyse data in excel, then you need to start using pivot tables. This powerful tool will convert huge lists of data into tables that can be copied and changed with a couple of clicks. Excel pivot tables are a great data analysis tool.
You can filter a large database using the page field option. A page field displays only the data for the selected page field item. For example, if you had data that covered several years, "years" could be a page field.
Your data source can be just about anything, but the format must be in list or flat file. In other words, a list of field names at the top, with each record of data entered in rows. Field names should be based on the type of data that column will hold. Spaces can be allowed as you can hide blank values or show specific error messages.
Another useful feature is the option to show row and column totals. You can switch this feature on and off. It is useful if you are tracking year to date figures or running totals throughout the year, without it having to be a separate exercise.
After you have created your first table, excel provides the option to copy your table either as an exact copy of the original or as a picture without the interactivity. This is handy if you don't want people to access the data list behind the pivot. To get access to this data, you just double-click on the pivot table itself. A new worksheet will be created with the source data included.
The fastest way to learn about pivot tables is to use a data list. This does not have to be thousands of rows. It could be as little as 5. The larger your test data list, the more information you might gain from the analysis.
If you have spent some time creating your perfect pivot table, but want to show this graphically, then you have the option in excel to create a pivot chart. This is a separate worksheet of your pivot table in chart format. It can be a bit cumbersome at first to use them.
Pareto Analysis Using Microsoft Excel 2007
A Pareto diagram has to have a certain look and scale. Here's how to create a proper Pareto diagrm with Excel 2007.
http://www.youtube.com/watch?v=_1AlJvmW98k&hl=en
http://www.youtube.com/watch?v=_1AlJvmW98k&hl=en
Sensitivity Analysis in Microsoft Excel
One of the finest features in Microsoft Excel is sensitivity analysis using either a table (Excel 2003) or 'What-if' in Excel 2007. Suppose you want to start a cybercafe or a restaurant in a new mall. You have done a study on the footfall and the kind of people who visit the mall. You have also found out about the business atmosphere, security and rent or the outright purchase price. You also know the rates in the market that other businesses are charging, let's say, for surfing the net per hour. You then estimate your capital costs like doing up the cybercafe and the price of the computers. You also use the Excel spreadsheet to estimate and calculate the number of people you'll need to run the show and the amount of salaries you'll have to pay. You have also estimated other variable costs like electricity and phone.
From the above data in the Excel worksheet you can calculate your total monthly or yearly costs. Now based on a certain price that you will charge the customers, number of computers and working hours you can calculate your revenue per month or per year. From the data of revenue and income you can easily calculate the profit. Till now everything was easy to implement in Excel.
Now you decide to find out how your profit can vary if you vary the charge per hour or the number of people who will visit your cybercafe or establishment. Of course, you cannot charge what you want but you can get a good estimate by observing what others are charging and what quality of service and environment they are providing.
Arranging all your data properly, click on 'Data' in the ribbon in Microsoft Office 2007 or 'Data' in the menu bar in Excel 2003. In Excel 2007 select 'What-if' analysis and finally 'Data Table...'. In the popup window in the 'Row input cell' type the data that you have input horizontally next to the profit and in the 'column input cell' write down the price and vary it it by 1% 0r 2% so that that Excel can perform an analysis for, say., $0.5 per hour charge for a cybercafe to $1.5 per hour. The horizontal values can be the number of people per hour or month or year that will visit the shop and keep on varying the values by a certain estimated percentage. Click 'OK' and you can see how your profit varies with the number of customers and the price you charge. This is also known as a two variable table because you calculated the changes in your profit based on two parameters - price and number of customers.
You can also do a single variable sensitivity analysis by selecting only one parameter.
You can use the 'Data' and 'Table' feature in Excel 2003 to implement the same analysis.
The practical implementation of the above sensitivity analysis in the form of a training video can be found in the link below.
Subscribe to:
Posts (Atom)
