I am sure that you would be preparing well for your ensuing end term examinations and looking forward for your Christmas vacation. You will have about 10 days leave and I thought it is an ideal period for you to spend some time to learn Ms Excel.

You will all agree that good working knowledge in Excel is one of the most important pre-requisites for corporate especially in the finance profile. While we are incorporating few portions of Excel in your main course curriculum, I feel you should spend some time to harness your skill there.

I find that excel can be self-learnt in your own pace and convenience. I find excellent YouTube videos particularly Excelisfun channel as one of the best help available to learn Excel.

We feel you should be comfortable in Excel in the following functions.

• Basic

• Keyboard navigations

• Relative, mixed and absolute cell referencing

• Various types of charts.

I am giving the following web links from “Excelisfun” channel which cover the above topics. They are given below:

...Week 6
Excel
PF008 A14
Introduction to Spreadsheets
A spreadsheet is an electronic file that contains a
grid of columns and rows used to organize related
data and perform calculations.
Excel is a spreadsheet program used to create and modify
electronic spreadsheets.
An electronic spreadsheet makes data-entry changes easy,
and if the formulas are correctly constructed, the results
recalculate automatically and accurately.
A worksheet is a single spreadsheet that typically contains
descriptive labels, numeric values, formulas, functions, and
graphical representations of data.
A workbook is a collection of one or more related
worksheets contained within a single file. Each worksheet
is identified by a sheet tab. By default, new workbooks
have three worksheets.
The whole file is a workbook
Worksheet
Planning Structure of Worksheets
1. State the purpose of the worksheet
2. Decide what input values are needed
An input area is a range of cells containing values
3. Decide what outputs are needed
An output area is a range of cells containing
results
4. Assign the worksheet inputs and results
Use rows and columns
5. Enter the labels, values, and formulas
6. Format the numerical values
7. Format the descriptive titles and labels
8. Document the worksheet
9. Save the completed workbook
Exploring the Excel Window
• Worksheet rows lie horizontally
• Worksheet columns lie vertically
• A cell is the intersection of a row and...

...to Create an Interactive Quiz in Excel
http://college.cengage.com/accounting/resources/instructors/air/fall_2006...
Creating Interactive Quizzes in Excel
J.A. (Jim) Connell University of Montevallo Michael E. Stephens College of Business Station 6551 Montevallo, AL 35115 Elizabeth Mulig USF St. Petersburg 140 Seventh Avenue South St. Petersburg Florida 33701-5016 727-553-4154 mulig@stpt.usf.edu
Introduction Have you ever received a spreadsheet file that asked you questions and then magically told you whether your answers were correct? They can be more than e-mail pastimes. Once you understand how those spreadsheet files list questions and check your answers, you can create spreadsheet files that ask your students accounting questions. All it takes is an understanding of a few simple functions within the spreadsheet, and we’re about to reveal those secrets. They’re a lot easier to create than most people think and it only takes one simple formula to produce a scoring system that makes your quiz interactive. This means that your answers will be included in the spreadsheet, but it’s easy to hide the answers and password protect the spreadsheet so your students have to work to find the correct answer. To illustrate how it works, we’ve created an interactive quiz based for a principles-level accounting class. Once you understand the function, you can adapt this to your own needs by changing the headings and questions. We used Microsoft...

...Student ID: 21709822
Exam: 038229RR - Microsoft Excel
When you have completed your exam and reviewed your answers, click Submit Exam. Answers will not be recorded until you
hit Submit Exam. If you need to exit before completing the exam, click Cancel Exam.
Questions 1 to 20: Select the best answer to each question. Note that a question and its answers may be split across a page
break, so be sure that you have seen the entire question and all the answers before choosing an answer.
1. The order of precedence is very important when building formulas in Excel. Which of the following
formulas will produce 778 as the result?
A. =25*(27/9 + 5) + 309*2
B. =25*27/9 + 5 + 309*2
C. =25*((27/9 + 5) + 309*2)
D. =(25*(27/9) + 5 + 309)*2
2. Which of the following identifies the patterns used for each data series in a chart?
A. The horizontal and vertical axes
B. Data series
C. Legend
D. Data point
3. Helga needs to lay out her newly created pivot table. What are the two ways she can set up a pivot
table?
A. When Helga created the pivot table, Excel automatically put the fields to the appropriate cells of the pivot table. There aren't
two ways to set up a pivot table.
B. Helga can check the field or type in the data in the pivot table.
C. Helga can drag the fields to the four boxes in the pivot table field list or right-click a field name and choose its location from
the shortcut menu.
D. Helga can slide cells to the four boxes or double-click...

...-------------------------------------------------
Excel Assignment #1
Exercise 1: Student Grade Book (expected time – less than 45 minutes)
Please download the spreadsheet Excel Assignment 1.xlsx from Blackboard. Click on worksheet E1 (you may be in Worksheet E1 by default). This spreadsheet keeps track of students’ grades. There are two exams – Exams 1 and Exam 2.
Please do the following:
(a) Find the total weighted score for the semester based on Exam 1 and Exam 2 and their respective weights. (i.e., Exam 1 * Weight 1 + Exam 2 * Weight 2) Exam 1 is weighted 45% of the total, and Exam 2 is weighted 55% of the total. These weights are in cells B4 & C4. (Hint: you might want to use absolute cell references when referring to these cells.) This formula belongs in the Weighted Total column. You MUST use absolute cell references to get credit for this question.
(b) Find the average (AVERAGE), standard deviation (STDEV), maximum (MAX), and minimum (MIN) for Exam 1, Exam 2 and Total Weighted Score. (Click on the “Formulas” tab and then select “Insert Function.” Select “Statistical” from the category drop down box.) Put these formulas in cells B40:D43, as appropriate.
(d) You have two more columns called Consistent A and Consistent C or D. Display “Yes” in the Consistent A if the student scored 90 or above on both exams. Otherwise, display “No.” Similarly, display “Yes” in the Consistent C or D column if the student received a score less...

...DISCUSSION:
After using both languages, Microsoft Excel and MATLAB (MATrix LABoratory), on the same module experiments from 1 to 4, I observed the following:
* For Microsoft excel,
* It uses spreadsheet application where each elements or data values are distributed in cells.
* Most statistical computations are easy to solve due to the manner of data organization where data are inserted on cells arranged in numbered rows and letter-named columns.
* It is easy to plot the selected values by just choosing the selected data then use the chart tools/design tab.
* The Disadvantage of this language are as follows:
* Difficulty in debugging the error
* Limited set of statistical tools
* In the case of excessively large results, Excel returns #NUM! instead of an answer
* Inability to handle large datasets
* For MATLAB,
* It uses matrix based algorithms hence each element or data values are treated in an m row by n column, such that a single integer is considered a matrix of one row and one column.
* Manipulation of vectors is easily done by one command.
* To plot, matlab needs to use certain algorithms which are compiled in an M-file.
* The Disadvantage of this language are as follows:
* Expensive wherein every single toolbox has to be paid due to its licensure
* It uses a huge amount of computer memory; hard...

...Microsoft Excel Exercise
IT/206
September 19, 2011
Patrica Byerly
Microsoft Excel Exercise
Situation 1: A business professional wants to use Microsoft Excel to organize data for the monthly expense report. He has never used Microsoft Excel and is inquiring about the functions that would support him with this task.
The approach that should be used to for this customer is to explain the functions that would help the customer to organize his spreadsheets for his monthly expense report. I would also explain to the customer that he may add formulas to the spreadsheet that will add up the totals for him, to help him get it done quicker. I would tell the customer that he could set it up to give him the total expenses by the day, week and also the month. I would also tell the customer that he could also set it up to calculate the totals spent on transportation, food, and motel expenses. Additionally, he could also calculate the fuel, entertainment, phone, and misc. expenses. I would also alert the customer that using filters will help narrow down data for an easy view without having to look all over the data to find what he is looking for, such as how much was spent on the motel stay. I would then tell the customer that he can add graphs and charts to show what was spent in each category, whether it was by the day, week, or month. I would let him know that if he decides to use graphs and charts it would be an easy...

...EXCEL SOLVER TUTORIAL
Many firms face the problem of how to best use multiple scarce resources. Linear programming is designed to help find the product mix that maximizes profits in the short run when multiple constraints exist. While linear programming can be solved as a mathematical problem using pencil and paper, it is much more efficient to use Excel Solver. The key to using Excel Solver is to make certain you have modeled the problem correctly and then interpreted the results appropriately. In this problem we will practice the use of Solver.
In this example suppose that you manufacture regular and premium golf carts. The selling price, variable costs and manufacturing times are as follows:
Regular
Premium
SALES PRICE
$ 8,000
$ 10,000
VARIABLE COST
5,600
6,500
CONTRIBUTION MARGIN
$ 2,400
$ 3,500
Assembly hours
20
50
Inspect and Test
5.0
2.5
Your company currently has 10,000 hours available for assembly and 1,200 hours for inspection and testing. There is also a limit to how many premium golf carts that can be sold (150 max). Given this information if you want to maximize profits what mix of regular and premium golf carts should you produce?
SOLUTION:
1. Input the above data into an Excel Spreadsheet (including constraints). Add cells for total hours used (these should be formulas). Add two additional lines...

...Advance Excel 2010
Duration- 2 Days (16 Hrs)
Course Objectives
At the end of the course, the participant can
•
•
•
•
•
•
•
•
•
•
•
Use outlines, range names, databases & the data form
Use AutoFilter & advanced filters
Work with Data Series
Use advanced functions
Modify charts
Use Scenarios & Goal Seek
Use templates, data tables & solve problems
Use automatic formatting & styles
Work with views, consolidate worksheets
Create & revise PivotTables
Use, record & edit macros
Advanced Excel Training Course Prerequisites
Participants should already:
•
Be using Excel on a regular basis and or
•
Knowledge of Excel Introduction topics
•
New Features of Excel 2010
o Spark lines
o Slicers
o Microsoft SQL Server Power Pivot for Excel add-in
o New statistical functions
o Customizing Ribbon
o Adding Tabs, Groups and Commands on Ribbon
•
Improved features in Excel
o Improved Filtering
o Improved Pivot Table
o Improved Conditional formatting
o Improved Charting
Page 1 of 3
Head Office: Aptech House, A- 65 , MIDC Marol , Andheri (E), Mumbai 400 093.
Tel: 022 – 28272300, Fax: 228272399
•
Using Formatting and Functions
o Applying Formatting to Numbers
o Creating Custom Number Formats
o Applying Conditional Formats
o Using Formulas in Conditional Formats
o Absolute and Relative References
o Naming a Range
o Using a Named Range in a Formula
o Using Tables in Functions
o Using Lookup...