(HD) ACF2400 Excel Cheat Sheet PDF

Preview

Summary

A cheat sheet for any written excel questions they can throw at you as well as some excel equations....

Description

2

Contents System...................................................................................................................................................1

Important of Excel Skills: Spreadsheet: o A catch term of any type of information stored in Excel, including a financial mode.

Financial Model: o A complex type of spreadsheet. o Its purpose is to aid in decision making.

Attributes that Identify the Spreadsheet as a Financial Model: o More structured. o Dynamic. o Use relationships between several variables to create financial reports and change any or all of them will affect the output.

Financial and Accounting: o o o o

Budgets Financial results. Forecasts. Plans used to make big business decisions.

Marketing and Product Management: o List customer and sales targets to manage sales force. o Plan future marketing plans based on past results.

Human Resource Planning: o Understand where costs are coming from and how best to plan and control them for the future.

Good Modelling Practices:    

Clearly state inputs and assumptions. Separate model inputs from analysis. Reliably perform analysis in relatively easy to debug steps. Design model to enable understanding and use of the model by other users. o Reasons:  Reduce Errors.  Make the model easier to read, audit, update and use.

2

Financial Functions: Future Value (FV): o Returns the future value of an investment, where rate is the interest rate per period and nper is the total number of periods.

Present Value (PV): o Returns the present value of an investment.

(PMT): o Calculates the payments required each period on a loan or an investment.

(IPMT): o Calculates the amount of a loan payment devoted to paying the loan interest, where per is the number of payment periods.

(PPMT): o Calculates the amount of a loan payment devoted to paying off the principal of a loan.

Net Present Value (NPV): o Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.

(IRR): o Returns the Internal Rate of Return for a series of cash flows.

(MIRR): Returns the modified internal rate of return where positive and negative cash flows are financed at different rates.

Data Analysis: Auto Filler: o Quick way to find out what is in a column of data.

Conditional Formatting: o Highlight duplicate values.

Find and Replace: o Look for one thing and replace with another.

2

Excel Table: Subtotals and Outline: o Quick way to analyses a range of data. o Can only be used on Excel List and Excel Table. o Sort data based on what you need to subtotal.

Outline: o Create up to eight levels, one for each group. o Outer levels are represented by lower number. o Inner levels by Higher Number. o Each inner levels displays detailed data for preceding outer level. Advantages of Excel Tables: o Easy formatting. o Filter and Sort buttons. o Auto-Freeze Headers. o Single-Click selections. o Easy rearrangement. o Easy growth of data. o Special printing option. o Automatically copy formula. o Structured references. o Dynamic named range. o Special Total Row. o Insert Slicer.

Data Visualisation: Sparkline: o A graphic that is displayed entirely within a worksheet cell. o Compact in size, they do not include chart elements like legends, titles or gridlines. Data Bars: o Dynamic, changes as the cells value change. o Can be inserted anywhere within the workbook. o Goal: Convey maximum amount of information within a very small space.

Data Validation: 

An Excel featured used to control what a user can enter into a cell. o Each validation rule defines criteria for the data that can be entered and stored in a cell or a list of acceptable values. o You can add input message to inform users what is allowed. o You can add error message to stop invalid user input. Settings Tab: o A tab where you enter validation criteria.

2

Input Message Tab: o Defines a message to display when a cell with validation rules is selected. Error Alert Tab: o Controls how validation is enforced. Stop:



Stops users from entering invalid data in a cell.

Warning:



Warns users that data is invalid.

Information:



Informs users that data is invalid.

CFPD: 

Control Framework for Protecting Data.

PivotTable: 

A powerful analytical tool.

Advantages: o It organises data into meaningful summary. o Group data into categories and then uses functions to summarise the data. o You can easily rearrange, hide and display different category columns in the Pivot table to provide alternative views of the data.

Disadvantages: o They do not update automatically. o If data changes you have to manually update the PivotTable.

PivotChart: 

Use the analytical power of PivotTables to create graphic representations of data. o Allow you to interactively add, remove, filter and refresh data files in the PivotChart similar to working with a PivotTable. o They have all the same formatting as other charts, including layouts and styles. o They can move and resize chart elements or change formatting of individual data points. o You can copy and paste or move a PivotChart anywhere in the workbook.

Dashboard: 

Data visualisation that presents useful overview of consolidated business information. o Timely summary information or Key Performance Indicators (KPIs).

2

o Useful for decision makers. o Informs rather than overwhelms. o Call attention to unusual KPIs that require attention or are of interest.

Form Controls: 

Objects such as drop-down boxes and option buttons that sit over the top of Excel worksheets like charts do. o They can interact with formulas so that when the option is selected, it changes the formula and can drive the model.

Combo Box: o An important tool if you want the user to select from a predefined number of options.

Charts: Combination Chart: o Combines two chart types enabling the display of two sets of data.  Supports two vertical axes - primary and secondary axis.  Advantages over regular charts:  Hard to tell the total units sold on regular charts.  Plotting all data on the same axis will lose the ability to compare data of different scales. Column Chart Subtypes: Clustered Column:  Displays data series in separate columns side by side. Stacked Column:  Displays data series within combined columns showing how much is contributed by each series.

Decision Analysis: 

Spreadsheets provide the most convenient way for business-people to build computer models.

Categories: Sensitivity (What-if?) Analysis:  Examines how sensitive the result analysis are to changes in the assumptions.  Small changes in input that cause large changes in results (High Sensitivity)

2



Large changes in input that cause little changes in results (Low Sensitivity) Base-Case Selection:  The expected case of the model using the assumptions that management deems most likely to occur. The financial results for your base case should be better than those of your conservative case but worse than those of your aggressive case.

Choosing a Base-Case: 

Using most appropriate base-case;  Current policy is usual.  Most likely scenario when there is uncertainty about outcomes.  Best or work case scenarios sometimes used.  Answers questions such as;  If we follow last year's plan, how much profit should we expect next year?  How many items do we expect to sell next week? Base-Case: Comparing the Results of Complex Analysis:  Excel Scenario Manager;  Perform what-if analysis with more than two input cells.  Define and save sets of values as scenarios.  View and change them to produce and compare different results.  Requirements for running a scenario;  Well-structured input and output sections.  Output depends on input through use of formulas.  Changing cells and results cells must be on same worksheet - cannot span worksheets. Scenarios:  A result when a set of parameter values are changed.

Breakeven Analysis Optimisation Analysis

2

Spreadsheet Errors and Risks: Spreadsheet Design Principles: o Clear sections for input/calculation/outcomes. o Validity checks / input controls.  Navigation.  Presentation of data.  Hardcode once.  Formula design (absolute v relative references). Spreadsheet Errors can have Consequences:  Wide use of spreadsheets.  Some acquire 'critical' roles in processes.  There is a need for attention to errors in spreadsheets.

Auditing Tools in Excel: Excel Inbuilt Audit Tools: Trace Precedents:  See all the cells whose values are used in the formula in the present cell. Trace Dependents:  See all the cells that use the value in the current cell for the calculations. Trace Errors:  Excel warns about some potential errors automatically.  Green triangle flags potential error.

Manual Spreadsheet Auditing Techniques: o o o o

Inbuilt audit tools do not detect logic errors. Need to calculate result manually. Test using extreme and out-of-range values. Should be performed by an independent person....