English French German Portuguese Russian Spanish
You are here Home
Friday, 05 April 2013 14:07

Data Analysis Using Microsoft Excel Featured

WRITTEN_BY  Administrator
Rate this item
(0 votes)

Data Analysis Using Microsoft Excel

Question 1

$1a.       The CSV file will be converted to excel by saving it as an excel file using the file save as option.  The CSV file should be well saved to ensure the data is useful. It should be saved in the right format.

$1b.       Open Microsoft Excel with a new spreadsheet. Each excel has numerous worksheets that can be used. The margins of the worksheets can be set up depending on the data. Using the page set up option select the orientation that is suitable for the data. After opening the worksheet, one should click a cell on the sheet and start keying in the data in columns and rows.

$1c.       Spark lines are important in representing data for the individual stores. A line chart will be used to represent data for the individual stores in a given period.

$1d.       To prevent the boss from deleting the formulas, I will protect them using the excel protection option. I will go to format, cells, protection and then hidden to protect the cells with formula. Then I will go to tools, protection, protect sheet then put a password to protect the data. The boss will not see the formulas and change them as they are hidden.

Question 2

$1a.        Since the Excel list has two columns labeled customer category and zip code, I will adjust the whole list in order to apply filters. Then I would use the filter option to select customers coded as PROS or potential customers. Then apply filter on Zip Code column to select the zip code of choice. This will give me customers with the same physical address as the business zip code.

$1b.       I will modify every letter individually if the list has no more than 10 customers by including the mail information for each customer. If the list is more than 100, I would utilize the mail merge functions in Microsoft words. I would link the excel file during the mail merge wizard steps. Then populate the letters with customer information.

$1c.        I will develop a macro to run the steps mentioned in part A and save it as a different worksheet so as not to confuse with the present data set. This will also make it easy to monitor the sheet. The worksheet will be set up well to be able to use it together the Mail merge document produced in Part B.

$1d.       I will utilize the “if analysis function” found in excel, develop an excel file with different worksheets so that each sheet shows a different scenario. The situations will be developed according to the zip code and kind of customer. Then I will prepare a summary report at the end of the excel. The report will have look up functions such as V look UP and call to certain cells to have a clear picture of the possible options. I will utilize a pivot table to administer the data and change the values calculated easily. 

Question 3

$1a.       The worksheet will consist of three columns. That is employee name (C), department (D), Guest (E) and Cost (F). The Guest column will be populated with 0 or 1. Column G (will attend) will have 0 or 1 value. The cost column will have the formula (45+45* E2))*G2. The cost of the attending will be 0 if column G is 0. If the worker brings a guest, the value for column E will be 1 and the cost will be double. If the value of Column E is 0, the cost will be $45.

$1b.      I will use the following formula in column C. 0= (COUNT C (Sheet1! A: A))-1.  Sheet 1 will have the list of employees prepared in Part A. -1 is used to ensure the header is not counted. I will add another column on the sheet prepared in section A known as Group Discount. The column will be populated using the following formula If (Sheet 2! C1>0, IF (B2=1, 10, 5)). This formula will place 5 if the worker does not bring a guest and 10 if he does. The difference in price is $5 per person. Then a second worksheet with a pivot table will be used to calculate the cost of the party.

$1c.        I will set up a conditional formatting so that when the value of column G stated in part A will be 1, then column C will have a yellow background.

$1d.       I will produce a copy of the pivot table produced in section A and save it as a different file. The pivot table function will create a link to the base file automatically. Then the base file in section A and the file with the pivot table in the net work folder. The pivot table will be titled employee party report. The file will refresh automatically when one opens it and show the present data. The pivot table will be developed to show workers attending and cost of the parry when the management team accesses it.

Question 4

$1a.       I will develop several columns. That is production (A), revenue (B), cost(C) and scraps (D) columns. Other columns are mini-gizmo (E) and magna-gizmo (F).

The total revenue = (A* C)-(D*C).I will set a conditional formatting so that when the value of Scrap (D) is more than 10 of the production. Total production of mini-gizmo (E) = Sum(A)-sum(F).

b. I will use the “what if function” to generate summary reports after creating the scenarios. I will generate a copy of the pivot table generated in Section A and save it differently. The pivot table will link the base file automatically. The base file and file having the pivot table will be put in a  folder in network named summary report.

c. I will add a header to the worksheets and summary reports using the macro option. The header will have the date and name of the reports and sheets.

d. I will produce bar graphs to accompany the summary reports and worksheets. 

Question 5

a. The excel look up function is used to find and return the interest rate. The other information needed is principal loan and period.  The interest rate is determined using the principal and period.

 b. Building a list in access in the banking environment will make it easy for the staff to get information concerning a particular customer. This will enable them to offer faster, satisfactory and up to date information to customers. This will in turn attract and retain customers.

c. To create an input form, open an excel workbook and use the file menu to access it. Use tools, macro, and visual basic editor, click insert and choose user form. Then drag a command button into the form. Click View and properties windows to label the command buttons. Select the command button on the form titled “OK”and label it “cmdOK” and then use the properties window to set the command button OK. Repeat for the other buttons. The macros are used to develop the form. Click the other command button and name it cmdCancel and set the caption in the properties widow to “Cancel”. Repeat the process for the third button and mane it “Clear”. Then type a code into the code window.


Jelem, B. (2002). Guerilla Data Analysis Using Microsoft Excel. Tickling Keys, Inc


Last modified on Friday, 05 April 2013 14:10
Trusted Site Seal SSL Certificate Provider SSL