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.