Final Essay: Evaluation

I studied Excel in high school but I did learn a tremendous amount of new things about Excel 2007 in this class. First, it started with the shortcuts in Excel, then formulas, lookup and database formulas, macros,… I have written on my blog every week what I learnt in Excel. I feel that I have become very familiar with Excel; I don’t have to think too much when I do something in Excel. I liked very much our textbook. It had a lot of useful tools like a retirement calculator for example and many more things. I also liked the CaseGrader because then you can actually see what you are able to do when a case is given to you. I used Excel to set up my academic schedule for next semester. It gave me a good view of what classes are when and if I can take them. I am sure that Excel will also help me in the future, maybe even in the near future like a summer internship. What stands out to me in htis class is that everyone was following with when we were demonstrating something in class. There was a nice collaborative work from everyone and this is thanks to the professor who prepared every class beforehand in an interesting, entertaining, and educational way. I really wanted to join this class because I wanted to master Excel. That is also the reason why I have put a lot of effort in this class. I did all 8 cases and read the textbook. It was an amazing class and I want to thank Professor Jorish for making it the best and most interesting class in YU.

Case 8

Case 8 asks to protect a worksheet. This is useful for a company and other big organizations. A second tool for companies are to create validation rules for data entry. This is like programming. It prevents employees to fenter false data and tells them whats wrong. I like the function of a macro. I heared of macro's but I never knew exactly what it was. I did not know untill I did this case. It actually records your steps and repeats it when you run the macro. I also liked that you can assign a macro to a butto.

Case 7

Case 7 was a compact case with interesting functions like:

❖ Nested IF function
❖ VLOOKUP function
❖ COUNTIF function
❖ SUMIF function
❖ AVERAGEIF function
❖ Database functions

These functions are very useful with large tables. I liked especially the VLOOKUP but even more the database function that will look up for a CRITERIA and then will calculate for what u ask like DSUM, DAVERAGE,...

Nested fuctions and Vlookup

For the case found here we can use nested formulas like =IF(B11>=B6,SUM(C2:C6),IF(B11>=B5,SUM(C2:C5),IF(B11>=B4,SUM(C2:C4),IF(B11>=B3,SUM(C2:C3),IF(B11>=B2,SUM(C2),IF(B11>=0,0,0)))))) . It looks complicated but if you havve a look at it it is not. It just repeats itself. The first part says that if the amount of homeruns is equal or bigger than 60, he will get 1000000 (the sum of C2:C4) and if not it would look if it is bigger orequal to 50 and so on...
For the vlookup you can convert first the range into a table to make it easier. You have to add a third column to the table with the cumulative amounts. After you did that you just need to enter the formule =VLOOKUP(B11,table1,3 where B11 is the lookup data and table1 is the name of the table where it should lookup and 3represents the column of the table where to look ti up and refer to that value.

Case 6

Case 6 was an incredible case. So much stuff in it...amazing. It tests on the editing and formatting of multiple worksheets at the same time. This is very important for when you have similar tables from different affiliates of a company, for example, that have different data but same style of table. By grouping different sheets it is possible to change the formatting of a cell or to enter a formula into all the grouped sheets at the same time. You win so much time by doing it that way. When entering a formule in one sheet it is possible to refer to a cell in a different worksheet. This case also tests on creating a link to an other workbook and also to insert a hiperlink in a cell. At the end it is also asked to save two different workbooks into one workspace. So when you open this particular workspace both workbooks will open together one next to the other (titled). It also asks to create a static web page and a custom template. A custom template is very important in the business world. All companies use templates for letters etc. They always use the same layout, header and footer. So they create one or more custom templates so that they do not have to rework the layout, header and footer every time.

The Pivot table is very useful and it is easy to "play around with it". You can decide what to take as column and row very easy by just dragging it in the boxes of column and row. It is also possible to see what to use as value and so much more...thank you for that class. It was nice ending it like that before pessach with case 6 and Pivot table and chart. Have a nice pessach and congratulation to Professor Jorisch for being elected Professor of the year!

Excel shortcut and function keys

The most used Excel shortcuts are:

• CTRL+1 Displays the Format Cells dialog box.
• CTRL+R Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
• CTRL+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
• CTRL+T Displays the Create Table dialog box.
• F5 Displays the Go To dialog box.
  

---------------------------------------------------------------------------------

The most useful generic Windows shortcuts are:

• CTRL+A Selects the entire data.
• CTRL+C Copies the selected text/object/cells.
• CTRL+V Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you have cut or copied an object, text, or cell contents.
• CTRL+X Cuts the selected text/object/cells.
• CTRL+P Displays the Print dialog box.
• CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry that you typed.
• CTRL+S Saves the active file with its current file name, location, and file format.

For more Excel shortcuts click here or look on the tool at the bottom of the blog.

Case 5

This case is a tuff one. It asks to freeze panes which is very useful for long lists or tables. Second, it tests you on conditional formatting and on filtering data. These help you to get some first information by looking at the data. You are also asked to find subtotals by salesperson. This is asked alot of times in the business world. The last part of this case tests you about a pivot table and pivot chart.  It is a very handy tool but it was hard to find out how to do it. Finally, I did it but I do not really understand what it is for...

Bean Bag Quarterly Income Statement and Break-Even Analysis

This spreadsheet shows how Excel can be important for the decision making process of a business. This kind of spreadsheet can be used for people which investigates the relationship between a product’s expenses (cost), its volume (units sold), and the operating income (gross profit) [cost-volume-profit (CVP) (also called break-even analysis)].  Any money a company earns above the break-even point is called operating income, or gross profit. By inserting the data and appropriate formulas in some of the cells you get the first table. Before inserting the formulas it will be easier to change all the cell names by the corresponding left cell. This is possible to do by clicking on the formulas tab on the ribbon and then clicking in the Defined Names box on Create From Selection (make sure that the dialog box left column is selected).  Afterwards, you create a data table with the what-if analysis (here called the "Break-Even Analysis" table). From this table it is possible to determine (just read of the table) the revenues, expenses, and income for different amounts of units sold. From this table a manufacturor can conclude how many units he has to sell in order to get a (positive) income. That is why this table is called a Break-Even analysis because you can find out when the revenues will exceed the expenses (break-even).  For the exercise click here.

Case 4

This case tests your skills about different kind of graphs in Excel 2007. It is important to know how to use different type of graphs for appropriate data that you want to graph. It also test you on the formatting of a chart. For example, by formatting the 3D pie chart it is possible to accentuate something. The last graph of the case is a combination of a bar graph and line graph which can be very useful for comparisons of data in presentations.

Case 3

The case is a nice exercise to get to know how to work with the SUM,MIN,MAX,AVERAGE, and IF formula. The Future Value (FV) formula is explained in more detials. The exercise also shows that sometimes you have to use absolute reference before copying the formula.

Online Tutorial: Financial Formulas

This tutorial shows us how powerful Excel can be for making business decisioins. It can help you to figure out the following things for example:

• How much you would need to spend on monthly payments such as mortgage or car payments.
  
• How much you would need to save in order to accumulate a specific amount by a certain point in time.
  
• How much of a down payment you would need to make, for monthly payments to equal a particular amount.
  
• How much you would gain over time on a specific amount of savings.
  

It explains also how to use the 'basic' financial formulas of Excel like:

• PMT
• NPER
  
• PV
  
• FV
  

I think that they should have mentioned also the RATE function in the tutorial. With these formulas you can find the answers to the above and to so many other business related questions. We also learnt in class that by conditional formatting it is also possible to automatically indicate the result/value that we are looking for.

We made a Loan Payment Calculator, Interest Table, and Amortization Table by just using 2 of these formulas. So you can imagine how important it is to know how to use these formulas.

Case 2

I learned nice ideas how to format a table in an Excel spreadsheet. The way you present financial information or statements are very important. So I'm happy about the exercise Case 2. I also really enjoyed what we did on Wednesday. It is very related to my finance major and it is also important for everyone who wants to manage his own money.

Online Excel Tutorials

From the first assignment I have learnt:


- how to add an icon/function to the Quick Access Toolbar. It is very practical like this you can add the icons that you use frequently so that you do not have to go look for them in a submenu for example.


- that with the ALT button you can use keyboard functions for every icon in Excel. It is helpful for when your mouse is not functioning or sometimes it is faster to use keyboard shortcuts than to use the mouse.


- the new features of Excel 2007, like: Safer files, Less risk of file corruption, Reduced file size, More useful data,...


- what the several extensions like *.xlsx, *.xlsm, *.xltx, *.xltm, *.xlsb, and *.xls (Excel 97 - Excel 2003 Workbook) stand for.




From the second assignment I have learnt:


- what #NAME?, #REF!, and ##### stand for. I always knew that it meant that there is an error in the cell but now I know to what kind of error it refers to.








The online tutorials are very good to get everyone in the class at the same level of Excel 2007 so that we can make more complex things, for example, like the Loan Payment Calculator that we did last class.

Case 1

It is still the basic stuff of Excel so I knew already everything. It is good that the first case is easy so that it helps you to get familiar with the CaseGrader.

Aryeh