1
Editor’s Note June 2017 Heartiest greetings to all! With great excitement and fervour, team Quantinuum presents “Quriosity” - the Quant magazine of SIMSR. This edition of Quriosity is summer special edition which focuses on some of the important articles on Excel. Excel is one of the famous and widely used tools in corporate for data analysis and data representation. The Excel as we know is just another Microsoft tool to do some basic mathematical operations and solve some LPP. But its a lot more than that. Excel can be used to do operations related to data analysis and representing data. Knowing excel better can surely make you look better than rest of your peer. With the last edition we brought to you the important formulas in excel. This edition has a lot more useful information about Excel. As the cover story we have the “Importance of Excel in Business” in page no 3 by Nilesh Desle from PGDM FS 2016-2018 which covers the some of the important uses of excel in various business domains. As the sub article we have some of the “Important Shortcuts in Excel” in page no 6 by Rishita Shah PGDM FS 2016-2018 which presents you the basic shortcuts which can help you to save a lot of time in the frequent operations you do in excel. We also have some “Useful tips for Excel” in page no 9 by Rishita Shah PGDM FS 2016-2018 which presents you about the some of the useful tips in excel which can make you working with excel easier.
Hope! These articles are useful to you in your internship as well in your future corporate life.
Happy Learning, Editorial Team!
2
Cover Story June 2017
Importance of Excel in Business
M
icrosoft Excel was released in 1985 and has grown to become arguably the most important computer program in workplaces around the world. Whether you are budgeting, organizing client sales lists, or need to plan an office social gathering, Excel is a powerful tool that has become entrenched in business processes worldwide.
take that possible risk of entry error. Excel relieves you of the need to perform calculations with a couple of included features. Located on the "Home" tab's "AutoSum" button are instantly clickable ways to add an addition, averaging, division and subtraction to your spreadsheets. For example, if you're totaling up payroll expenses for your 20-person staff, highlight the salary cells and click the "AutoSum" button to instantly see the total spent. Any time you click in to change a payroll number, the AutoSum feature automatically updates.
The benefits to a small business using Microsoft Excel may be fairly obvious – Excel offers a grid-like platform where you can enter and transform data into formatted information, as well as use features such as charts and graphs. But Excel offers far more than just ways to dress up the characters in its cells. Small businesses may find Excel far more of a workhorse than expected, with Excel "excelling" in its contributions to both financial and fiscally related areas of the company.
Printing Press Although your business will have to take care of the actual printing, Excel can turn your small business into a small printing press without having to enlist graphic designers, desktop publishers or layout personnel. Found on Excel's "File" tab's "New" option is a large collection of templates. Use these templates to create materials without having to start from scratch, saving you setup time on forms such
Fact Check While it's possible to do some of the calculations required for an Excel spreadsheet in your head, on paper or with a calculator, you won't need to 3
as receipts, invoices, labels, and timesheets, as well as more creative items such as cards, booklets, presentations, and fliers. All of Excel's templates are completely customizable, which means your business can benefit from all of the setups but still leave personal stamps such as inserting a logo or changing the colors and fonts on the materials.
business corral everything into one single file. Use the "Insert" tab to add other objects directly onto the spreadsheet, keeping everything together. Add extra sheets to the spreadsheet at the bottom of the work page, or keep everything on one sheet and take advantage of the scroll bar. Collecting multiple files in one spreadsheet may make the Excel file slightly larger, but you then only have one file to send rather than many.
Accessible Records One of the main benefits of the program is how you can always access the information on your spreadsheets. Unlike keeping data in a ledger, which can get messy with cross-outs, erasures, and liquid paper, Excel spreadsheets remain in their easy-toread format and are available – even enlargeable with the zoom features – any time you need to get your information. You can update records by creating templates, which you open, add a line or two of information to, then resave with a new version number, giving you ways to both create, and cover, your tracks.
Finance and Accounting If you walk through the finance or accounting department at any major corporate office, you will see computer screens filled with Excel spreadsheets outlining financial results, budgets, forecasts, and plans used to make big business decisions. This is the area of business with the biggest reliance and benefit from Excel spreadsheets. Advanced formulas in Excel can turn manual processes that took weeks to complete in the 1980s into something that takes only a few minutes today.
Collection Spot
Most users know that Excel can add, subtract, multiply, and divide, but it can do much more with advanced IF functions when coupled with VLOOKUP, INDEX-MATCH-MATCH, and pivot tables.
Excel offers innumerable ways to assist in the day-to-day business functions of finance and math, but it can also serve your business as a catch-all. If you're preparing one spreadsheet from multiple documents including other spreadsheets, images, portable document format files and other files, Excel can help your 4
Marketing Management
and
Product
And hours by pay period, month, or year, and better understand how your workforce is spread out by function or pay level. HR professionals can use Excel to take a giant spreadsheet full of employee data and understand exactly where the costs are coming from and how to best plan and control them for the future.
While marketing and product professionals look to their finance teams to do the heavy lifting for financial analysis, using spreadsheets to list customer and sales targets can help you manage your sales force and plan future marketing plans based on past results.
The Bottom Line
Using a pivot table, users can quickly and easily summarize customer and sales data by category with a quick drag-and-drop. All parts of the business can benefit from strong Excel knowledge, and marketing functions are not exempt.
Excel is not going anywhere, and businesses will continue to use Excel as a primary tool for diverse functions and applications ranging from IT projects to company picnics. A working knowledge of Excel is vital for most office based professionals today, and stronger Excel skills can open the door to promotion and leadership opportunities. Excel is a powerful tool but cannot function alone. It takes a savvy computer user to take advantage of everything Excel has to offer to provide the best results for their company.
Human Resources Planning While database systems like Oracle (ORCL), SAP (SAP), and Quickbooks (INTU) can be used to manage payroll and employee information, exporting that data into Excel allows users to discover trends, summarize expenses
By Nilesh Desle PGDM FS (2016-18)
5
Excel Shortcuts As we all know that Microsoft Excel is a very powerful application for spreadsheet processing and a pretty old one. Each new version of Excel came with more and more new shortcuts and seeing the full list (over 200!) you may feel a bit intimidated. Don't panic! Following few shortcuts will help you to spend a little less time navigating, formatting, selecting, and entering formulas for your data.
Managing and Navigating through workbooks Use the arrow keys on your keyboard to activate cells on the worksheet Shortcut
Description
Shift + arrow keys
Highlight a range of cells in a worksheet.
Ctrl + PageUP/PageDown
To open other worksheets in a workbook.
Shift + F11
Inserting New worksheet.
Ctrl+N
Create a new workbook.
Ctrl+O
Open an existing workbook
F12
Save the active workbook under a new name, displays the Save as dialog box
CTRL+W
Close the active workbook.
Home
Return to the 1st cell of the current row in a worksheet.
Ctrl+Home.
Move to the beginning of a worksheet (Đ?1 cell)
Ctrl+End
Move to the last used cell of the current worksheet, i.e. the lowest row of the rightmost column.
6
Formatting data Shortcut
Description
F2
To edit a cell press Fn+F2, cell will be activated
Ctrl + B/I/U
Hold the CTRL key while pressing the letter B/I/U for Bold/Italic/underline
Ctrl+1
Open the "Format Cells" dialog.
Alt + H + W
For wrap text
Ctrl + 9
Hold down the CTRL key while pressing the number 9 key.
Ctrl + Shift + )
Highlight cells above and below the hidden row(s), then hold down the CTRL key and the SHIFT key while pressing the open parenthesis key “(“ on your keyboard.
ALT key and then the letters H, I, and C one at a time.
A column will be inserted to the left of the activated cell
ALT key and then the letters H, D, and R one at a time.
The row with the activated cell will be deleted.
ALT key and then the letters H, M, and C one at a time.
Merge and centre
ALT key and then the letters H, M, and U one at a time.
Unmerge Cell
Activate a cell location.
For Entering Multiple lines of Text.
Type the first line of text. Hold down the ALT key and press the ENTER key. Type the second line of text and press the ENTER key.
7
Selecting data
Shortcut
Description
Ctrl+A.
Select the entire worksheet. If the cursor is currently placed within a table, press once to select the table, press one more time to select the whole worksheet
Ctrl+Home then Ctrl+Shift+End
Select the entire range of your actual used data on the current worksheet.
Ctrl+Space
Select the entire column
Shift+Space
Select the entire row.
Working with Formulas
Shortcut
Description
Tab
Autocomplete the function name. Example: Enter = and start typing vl, press Tab and you will get =vlookup(
F4
Cycle through various combinations of formula reference types. Place the cursor within a cell and hit F4 to get the needed reference type: absolute, relative or mixed (relative column and absolute row, absolute column and relative row).
Ctrl+`
Toggle between displaying cell values and formulas.
CTRL+'
Insert the formula of the above cell into the currently selected cell or the Formula Bar.
By Rishita Shah PGDM FS (2016-18)
8
Excel Tips
Deleting Worksheets Be very cautious when deleting worksheets that contain data. Once a worksheet is deleted, you cannot use the Undo command to bring the sheet back. Deleting a worksheet is a permanent command
If you are working with someone who is using a version of Microsoft Office that is older than Office 2007, you will have to save your workbook under the Excel 97-2003 Workbook format. A person who is running Office 2003 will not be able to open workbooks that are saved under the Office 2010 or Office 2007 file types. You can save a workbook as an Excel 97-2003 file type by clicking the down arrow next to the “Save as type” box in the Save As dialog box
The benefit of using the Wrap Text command is that it significantly reduces the need to expand the column width to accommodate multiword column headings. The problem with increasing the column width is that you may reduce the amount of data that can fit on a piece of paper or one screen. This makes it cumbersome to analyze the data in the worksheet and could increase the time it takes to make a decision.
Why? Pound Signs (####) Appear in Columns When a column is too narrow for a long number, Excel will automatically convert the number to a series of pound signs (####). In the case of words or text data, Excel will only show the characters that fit in the column. However, this is not the case with numeric data because it can give the appearance of a number that is much smaller than what is actually in the cell. To remove the pound signs, increase the width of the column.
9

Standard Order of Mathematical Operations
Symbol
Order
^
First: Excel executes any exponential computations first
* or /
Second: Excel performs any multiplication or division computations second. When there are multiple instances of these computations in a formula, they are executed in order from left to right.
+ or −
Third: Excel performs any addition or subtraction computations third. When there are multiple instances of these computations in a formula, they are executed in order from left to right.
()
Override Standard Order: Any mathematical computations placed in parentheses are performed first and override the standard order of operations. If there are layers of parentheses used in a formula, Excel computes the innermost parentheses first and the outermost parentheses last.
By Rishita Shah PGDM FS (2016-18)
10
Quant Connect November 2016
Q
uantinuum, the Quant forum of KJ Somaiya Institute of Management Studies and Research aims to empower students and professionals alike to organize and understand numbers and, in turn, to make good and rational decisions as future managers. The newsletter published monthly consists of a gamut of articles for readers ranging from beginners to advanced learners so as to further enrich the young minds understand the contributions made to the field of mathematics along with a couple of brain- racking sections of Sudoku to tickle the gray cells. For any further queries and feedback, please contact the following address: KJ Somaiya Institute of Management Studies and Research, Vidya Nagar, VidyaVihar, Ghatkopar East, Mumbai -400077 Mentor Prof. N.S.Nilakantan (+919820680741) – Email – nilakantan@somaiya.edu Team Leaders: Vaibhav Mohan(+9177086521382)
Rupal Doshi (+919831427640)
Rishita Shah(+91986729008)
Nilesh Desle ( +91 8097316866)
Maheshwaran K (9820669211)
Designing Team:
Editorial Team:
Shreyas Kulkarni (+918600106378)
Arunava (+917720093053)
Ashish Mohadik (+919819741018)
Milcah George (+917406186087)
Follow us on: https://www.facebook.com/simsr.quantinuum. For any queries, drop us a mail at: simsr.quantinuum@gmail.com
11
Quantinuum is soon coming with QSIC- The Quant summer internship competition which is ready to glorify the best analytic summer internship project. Get ready to showcase your project and win the cash prizes waiting for you.
12