### Present Remotely

Send the link below via email or IM

CopyPresent to your audience

Start remote presentation- Invited audience members
**will follow you**as you navigate and present - People invited to a presentation
**do not need a Prezi account** - This link expires
**10 minutes**after you close the presentation - A maximum of
**30 users**can follow your presentation - Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

### Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.

You can change this under Settings & Account at any time.

# P1 and D1

How can Spreadsheets be used to solve complex problems?

by

Tweet## Rachael Jay

on 14 September 2012#### Transcript of P1 and D1

How can Spreadsheets be used to solve complex problems?

- P1 & D1 - By Rachael Jay Why are Spreadsheets so Important? Can be used for complex problem solving.

A powerful and versatile business tool that can store and provide the user with valuable information.

Compatible with other programs such as Word and Powerpoint.

No wonder so many people use Spreadsheets! The Many Uses of Spreadsheets: Who uses Spreadsheets? Accountants - keep track of money, calculate profits, predict performance of business, calculate staff wages.

Schools - keep track of grades/marks, classroom activities, taking registers.

Supermarkets - keep a record of finances, analyse queuing times.

Scientists - record experiment results, analyse results, make predictions.

Engineers - perform complex calculations and rely on them being correct.

Sales People - keep track of items for sale and values of items, total profit made, commission earned. The Many Uses of Spreadsheets: What can Spreadsheets be Used for? Manipulating Complex Data Presentation Requirements Supporting Decision Making Checking for Duplicates using the EXACT Command: Allows the contents of two cells to be compared.

Returns a true or false value.

Is case sensitive.

Useful when changes in data need to be found. Splitting a Field into Several Fields: E.g. Splitting columns that contain first and last names.

Text to Columns Wizard. The LEN Function: Returns the length of a string.

Commonly used with other functions, e.g. LEFT, RIGHT.

Can be used to check for a middle initial. Using the FIND/SEARCH and REPLACE Functions: FIND and SEARCH both locate one string inside a larger string, showing details on its position.

If nothing is found, #Value is displayed.

FIND is case sensitive and SEARCH is not.

FIND does not allow the wildcards * and ?, whilst SEARCH does.

All are helpful for formatting and keeping data consistent. Using the CONCATENATE Function: Allows multiple strings to be pulled together into one string.

Can be used to put separated names back together again into one field. Cell Formatting Colours: Changing the Size and Font of Text: Adding Borders: Text Alignment & Orientation: Formatting Dates & Numbers: Cell and text colours may be altered in order to make data stand out.

Data must be legible. Can be adjusted to make information stand out.

Title size can be increased.

Fonts used must be consistent and legible.

Bold, italic and underline.

Merge and Centre option. Makes Spreadsheet presentable and easy to read.

Can be put around cells containing numbers - makes information easier to digest.

Can separate rows/columns. Can make reading the Spreadsheet easier.

Titles must be centred.

Days of the week can be rotated. Dates and numbers must be formatted in order for correct display.

40330 - June 1, 2010

Percentages, currency, etc. Inserting Graphs/Charts: Chart Wizard makes creating graphs easy.

Line graphs, pie charts, bubble charts, scatter diagrams, etc.

Will presenting the data in a chart make it easier to understand? Consider audience. Using IF Statements: Colour of cell can be changed depending on criteria.

Draw attention to specific data. Analysis of Data: Pivot Tables and Charts allow data to be analysed easily and quickly. Goal Seeking: Opposite to WHAT IF analysis.

Used to find a certain answer when unsure of the necessary input values. Scenarios: Can change values in order to achieve new results.

Found under "What-If-Analysis" heading in Excel.

E.g. "Best Case", "Worst Case" and "Most Likely Case". Regression: Linear Regression - a statistical tool for determining whether or not variables are linearly related.

"Analysis ToolPak". Data Mining: Extracting hidden, predictive information patterns.

Useful when large amounts of data are present.

Enables future trends to be predicted.

"XLMiner" add-in. Complex Problems that can be Solved by Spreadsheets Cash Flow Forecasting: Used to predict net cash flow of a business over a future period.

Estimates cash inflows and outflows of bank.

Rolling calculations, FORECAST function, WHAT IF scenarios. Budget Control: Actions carried out according to a budget plan.

Can determine whether or not a manager is rewarded, or if future budget plans need to be modified.

Tallies, SUM function, comparing expenses to income, comparing actual expenses and income to anticipated expenses and income. What-If Scenarios: Used to examine how spending, saving or investing money will affect future finances.

Scenarios, Data Tables and Goal Seek.

Can be used to determine the effect different costs/investments have on profit.

Can be used to analyse financial effects of different pricing models, warehouse options, employee numbers and raw materials. Sales Forecasting: Projection, prediction or estimation of expected sales over future time period.

Companies will base sales forecasts on sales records/market research, and use them to find patterns that will maximise revenue/cash flow.

Spreadsheet software will create organised, easily analysable sales forecast.

Analysis ToolPak - no formulas needed for analysing data. Statistical Analysis: Analysis ToolPak makes developing complex statistical analyses easy and fast.

The tools are able to use able to use the appropriate statistical macro functions and display results in an output table.

Anova, Correlation, Exponential Smoothing, Histogram, Moving Average, Random Number Generation, Regression and Sampling. Trend Analysis: Form of comparative analysis used to identify current and future movements of an investment.

Functions and Tools can be used to look for trends and forecasting purposes.

Linear Trend functions - Forecast, Trend, Slope, Intercept, Linest.

Exponential Trend functions - Logest and Growth. D1: How can Organisations use Interpretation Methods

to Analyse Data? Comparison of Totals: Mathematical Functions: SUM SUMIF AVERAGE COUNT SUBTOTAL RAND ROUND Other Useful Functions: MAX MIN SYD RATE FV Lists: Data Analysis and Results: Conclusions: Charts and Graphs: Trend Analysis: Pie Chart Column

Chart Bar

Chart Line

Graph Data in lists can be sorted or filtered. Sorted List Filtered List No conclusions can be drawn at this stage. The data user must then find the facts. Conclusions can be drawn once data has been analysed and interpreted.

Full transcript- P1 & D1 - By Rachael Jay Why are Spreadsheets so Important? Can be used for complex problem solving.

A powerful and versatile business tool that can store and provide the user with valuable information.

Compatible with other programs such as Word and Powerpoint.

No wonder so many people use Spreadsheets! The Many Uses of Spreadsheets: Who uses Spreadsheets? Accountants - keep track of money, calculate profits, predict performance of business, calculate staff wages.

Schools - keep track of grades/marks, classroom activities, taking registers.

Supermarkets - keep a record of finances, analyse queuing times.

Scientists - record experiment results, analyse results, make predictions.

Engineers - perform complex calculations and rely on them being correct.

Sales People - keep track of items for sale and values of items, total profit made, commission earned. The Many Uses of Spreadsheets: What can Spreadsheets be Used for? Manipulating Complex Data Presentation Requirements Supporting Decision Making Checking for Duplicates using the EXACT Command: Allows the contents of two cells to be compared.

Returns a true or false value.

Is case sensitive.

Useful when changes in data need to be found. Splitting a Field into Several Fields: E.g. Splitting columns that contain first and last names.

Text to Columns Wizard. The LEN Function: Returns the length of a string.

Commonly used with other functions, e.g. LEFT, RIGHT.

Can be used to check for a middle initial. Using the FIND/SEARCH and REPLACE Functions: FIND and SEARCH both locate one string inside a larger string, showing details on its position.

If nothing is found, #Value is displayed.

FIND is case sensitive and SEARCH is not.

FIND does not allow the wildcards * and ?, whilst SEARCH does.

All are helpful for formatting and keeping data consistent. Using the CONCATENATE Function: Allows multiple strings to be pulled together into one string.

Can be used to put separated names back together again into one field. Cell Formatting Colours: Changing the Size and Font of Text: Adding Borders: Text Alignment & Orientation: Formatting Dates & Numbers: Cell and text colours may be altered in order to make data stand out.

Data must be legible. Can be adjusted to make information stand out.

Title size can be increased.

Fonts used must be consistent and legible.

Bold, italic and underline.

Merge and Centre option. Makes Spreadsheet presentable and easy to read.

Can be put around cells containing numbers - makes information easier to digest.

Can separate rows/columns. Can make reading the Spreadsheet easier.

Titles must be centred.

Days of the week can be rotated. Dates and numbers must be formatted in order for correct display.

40330 - June 1, 2010

Percentages, currency, etc. Inserting Graphs/Charts: Chart Wizard makes creating graphs easy.

Line graphs, pie charts, bubble charts, scatter diagrams, etc.

Will presenting the data in a chart make it easier to understand? Consider audience. Using IF Statements: Colour of cell can be changed depending on criteria.

Draw attention to specific data. Analysis of Data: Pivot Tables and Charts allow data to be analysed easily and quickly. Goal Seeking: Opposite to WHAT IF analysis.

Used to find a certain answer when unsure of the necessary input values. Scenarios: Can change values in order to achieve new results.

Found under "What-If-Analysis" heading in Excel.

E.g. "Best Case", "Worst Case" and "Most Likely Case". Regression: Linear Regression - a statistical tool for determining whether or not variables are linearly related.

"Analysis ToolPak". Data Mining: Extracting hidden, predictive information patterns.

Useful when large amounts of data are present.

Enables future trends to be predicted.

"XLMiner" add-in. Complex Problems that can be Solved by Spreadsheets Cash Flow Forecasting: Used to predict net cash flow of a business over a future period.

Estimates cash inflows and outflows of bank.

Rolling calculations, FORECAST function, WHAT IF scenarios. Budget Control: Actions carried out according to a budget plan.

Can determine whether or not a manager is rewarded, or if future budget plans need to be modified.

Tallies, SUM function, comparing expenses to income, comparing actual expenses and income to anticipated expenses and income. What-If Scenarios: Used to examine how spending, saving or investing money will affect future finances.

Scenarios, Data Tables and Goal Seek.

Can be used to determine the effect different costs/investments have on profit.

Can be used to analyse financial effects of different pricing models, warehouse options, employee numbers and raw materials. Sales Forecasting: Projection, prediction or estimation of expected sales over future time period.

Companies will base sales forecasts on sales records/market research, and use them to find patterns that will maximise revenue/cash flow.

Spreadsheet software will create organised, easily analysable sales forecast.

Analysis ToolPak - no formulas needed for analysing data. Statistical Analysis: Analysis ToolPak makes developing complex statistical analyses easy and fast.

The tools are able to use able to use the appropriate statistical macro functions and display results in an output table.

Anova, Correlation, Exponential Smoothing, Histogram, Moving Average, Random Number Generation, Regression and Sampling. Trend Analysis: Form of comparative analysis used to identify current and future movements of an investment.

Functions and Tools can be used to look for trends and forecasting purposes.

Linear Trend functions - Forecast, Trend, Slope, Intercept, Linest.

Exponential Trend functions - Logest and Growth. D1: How can Organisations use Interpretation Methods

to Analyse Data? Comparison of Totals: Mathematical Functions: SUM SUMIF AVERAGE COUNT SUBTOTAL RAND ROUND Other Useful Functions: MAX MIN SYD RATE FV Lists: Data Analysis and Results: Conclusions: Charts and Graphs: Trend Analysis: Pie Chart Column

Chart Bar

Chart Line

Graph Data in lists can be sorted or filtered. Sorted List Filtered List No conclusions can be drawn at this stage. The data user must then find the facts. Conclusions can be drawn once data has been analysed and interpreted.