Module 3 PDF

Preview

Summary

Chapter 3 notes...

Description

Module 3 – CSIT 132 Working with Large Sheets, Charting and What-If Analysis Assumptions:  Are values in cells that you can change to determine new values for formulas.  Used to ensure what-if questions, example, what happens to the six months operating income if you decrease the equipment repair and maintenance expenses assumption by 1%. 3.1 – Project = Financial Projection Worksheet with What-If analysis and Chart. Learn to: 1. Enter headings and data 2. Enter formulas and functions 3. Create sparkle charts 4. Format the worksheet 5. Create a column chart 6. Chang view of worksheet 7. Ask What-If questions. Videos: 1. Rotating Text and using the Fill Handle to create a Series: Q: Rotate the contents in Cell B3:I3 to exactly 45 degrees, which is 3 point up and to the left of the zero-degree marker on the orientation arc. Click selected cells – alignment (on rinbbon) – click launcher (0 (this will select function greater than Zero in E4) PRESS TAB  Value_IF_TRUE Box: Type YES - PRESS TAB (if value is > than 0 (zero), there will be YES in F4)  Value_IF_FALSE Box: Type NO – (if value in E4 is less than or equal to 0 (zero), there will be NO in F4)  Press OK. Logical Test: Sale Revenue > or = to $65000 YES Bonus equals $123

NO Bonus equals 0

IF Function: Function that assigns a value to a cell, based on a logical test. General form of IF Function is: =IF (Logical_test, value _if_true, Value_if_false) Important: Control + to check formulas in the sheet and again to revert back to normal. Nested IF Function: Is one in which the action to be taken for the true or false case includes yet another IF Function. The 2nd IF Function is considered to be nested or layered within the first.

Sale Revenue > or = to $65000 YES

NO

Sales revenue > or =to $8000 YES Bonus equals $5500. Therefore:

Bonus equals 0

NO Bonus equals to $3500

=IF (B4>=B21, IF (B4>=B22, 5500, 3500), 0)

9. Adding and Formatting Sparkline Charts: There are three types of sparkline charts: Line, Column, win / loss Q: Insert a line type in Cell E5 using data range B5:D5. Click Cell C5 – click Insert tab – Sparkline (on ribbon on the right) - click Line button – Box opens – select data range (B5:D5) in worksheet (with mouse) (location range E5 is already selected since the cell was selected – Ok (in box) This line shows the trend of sales, revenue etc. if up or down or fluctuating. 10. Apply, Modify a Sparkline style (change it another style): Q: Change the style of the Sparkline in Cell E5 to Sparkline style……. Click cell with existing sparkline design – click Design (to modify or change a style) – various styles are present so expand the style screen – select preferred style. Change Sparkline Type: Click Sparkline – select cells – click tab (top left) e.g. select Column and the style will change to column. 11. Use the Format Painter: Q: Use the format painter to apply the format from B6 to B20:D20 Format painter can be used to copy a cell format. Click cell (to copy) – click Format painter icon (top left of ribbon below copy icon) click the other cell (this allows the selected cell/s to be the same as the initial cell) Press escape to turn off the painter format.

12. Italicize cell content: Click cell – click I button on ribbon (between B and U) Adding a Clustered Column Chart to the Workbook: 13. Insert a Chart using the Recommended Chart Button: Q: Chose from excels Recommended charts to insert a clustered column chart in worksheet based on range A3:B8. Select cells as required – click Insert tab – Recommended charts (box opens) – click clustered column option – Ok 14. Edit Place Holder (Chart Title) Text in Chart Element: Q: Enter the text 2020 Sales in the Bar Chart title placeholder. Click chart – click chart title – enter title, e.g. 2020 Sales – click any empty cell (text will appear in chart) (In MacBook – click the title and change the details) 15. Add a Chart Element: (in MacBook – Chart Elements are in the ribbon – extreme left) Q: Add a chart element to the Pie Chart at the default location. Click chart – (in windows 3 icons appear on right of chart – in MacBook top left corner of ribbon after clicking chart) select the element as required, e.g. Chart Title, Chart Axis etc. 16. Add Chart Element at a Specific Location: (Data labels) Q: Add Data labels chart element to the Bar chart at the “Outside End” position. Click chart - click + (for Microsoft and in ribbon for MacBook) – select Data label – drop down menu – click “Outside End” 17. Move a Chart Element: Q: In the Pie Chart, move the chart legend to the right position. Click chart – click + (for Microsoft and in ribbon for MacBook) – move mouse near Legend and click dropdown arrow – another box – click “Right” (Excel moves the legend to the right) 18. Add Axis Title to a chart:

Q: On the Bar Chart, add the title “Dollars” to vertical axis and “Food Category” to the horizontal axis. Click chart = click + (for Microsoft and in ribbon for MacBook) box opens – click Axis title check box – select vertical box and type – select horizontal box and type – click blank cell to see titles. 19. Change Text Direction of an Axis Title: Q: Change the text direction of the vertical axis title “Job Title” to horizontal. Click Axis Title – right click – short cut menu – click format axis title – box opens – click size and properties (top right of box) – click alignment – click text. direction arrow – click horizontal. 20. Delete a Chart Element: Q: Delete the Legend from the Pie Chart. Click chart – click + (for Microsoft and in ribbon for MacBook) uncheck Legend (it will be deleted from the chart) Move Chart: Click on chart design (on ribbon after selecting chart) – click move chart (top right of ribbon) – select “New Sheet” and write down new sheet name, e.g. Expense Chart – click ok. Organizing the Workbook: 21. Move worksheets: (tabs at bottom of excel sheet) Q: Reorder the worksheets so that the salaries worksheet is the last worksheet in the workbook. Click sheet tab (Salaries) – press and hold the mouse and drag to the left or right, based on where to move. 22. Group and Ungroup Worksheets: Q: Group Jan, Feb, Mar worksheets, then in Cell F16 on Jan worksheet, use the auto sum button to total the values in range F4:F15. Ungroup the worksheets and switch to Feb worksheet. Click 1st worksheet tab (e.g. Jan) – hold shift key and click another sheet, i.e., Mar (Feb is automatically selected as in between) This way all three tabs are grouped. e.g., Click cell F16 and use the auto sum button to total the values in range F4:F15. Click

(Since worksheets are grouped excel will place the same formula in other worksheets in same cell number, e.g. F16 in each sheet) Ungroup: Right click Jan tab – click ungroup from box Click Feb tab and the formula are there as well. Changing the View of the Worksheet: 23. Zoom a Worksheet: Q: Change the zoom level of the worksheet to 100%. Click view – click zoom – select 100% option – ok. 24. Split a worksheet into Panes: Q: Split the worksheet into four panes at Cell E13. Click Cell E13 – click view – click Split icon – Splits into four panes – split is where the empty cell is. 25. Remove worksheet panes: Q: Remove the panes from the worksheet. Click view – click split icon. (split is cleared) 26. Freeze Columns and Rows: Q: Freeze Column A and Row 1 through 3 in the worksheet. Click cell ( to freeze both rows and column in the worksheet click cell one row below the last row you want to freeze and one column to the right from the last column you want to freeze) Click view – click freeze panes – freeze panes ( this freezes both column and row based on cell position) 27. Unfreeze columns and rows: Q: Unfreeze the worksheet rows and columns. Click view tab – click freeze pane icon – click unfreeze panes. What-If Analysis: 28. Use Goal Seek: Q: Use Goal Seek to calculate the changing value in B5 that will result in the set value in cell B7 of 0.3 or 30%.

Click cell (e.g. Gross Profit Margin) – Data – Forecast – What-If analysis – click goal seek (from drop down menu) – Dialogue box opens : In Set Cell – already selected Click To Value Text Box and enter 0.3 – Press tab Click another cell (e.g. Cost of Goods Sold) (this will come in By Changing Cells) Ok – Ok Values in other cells change as well as they have formulas in them. What-If analysis: Scrutinizes the impact of changing values in the cells that are referred by formulas in other cells.

Additional Notes: Date Stamp: Click NOW function (in formulas to put date and time) For total Calculation: 1

2

3

Total

Double click Sum Button Σ – It totals automatically. Volatile Function: Function in which the number that the function returns is not constant but changes each time the worksheet is opened. Assign formats to non-adjacent ranges: 1) Select cells – select non adjacent cells (in mac – select cell than hold command key to select adjacent cells) 2) Click number format 3) Click currency and / or decimal place (click box on top of $, % &, and select more format – box opens – select) Format worksheet titles: Example: Click cell A1 than click Column A to select all to – a) Bold all (b) to change color select cell and fill color and change font if needed.

Rename and Color Sheet Tabs: Right click – select rename or color palette from menu. Spell Check multiple sheets: Click review tab – spelling check (top left of ribbon) or Select a cell (e.g. (fn+A1) than hold shift key and select another worksheet. INSIGHTS: Uses Bing search engine and other internet resources to help locate more information about the contents of the workbooks. Comments used to look up definitions; To use Smart lookup Insight: Select cell – review tab – smart lookup (in tools on top)...