Microsoft Excel Job Performance Task Creating Spreadsheets

Describes the Objective of the Computer Supplies On Hand Task

1. Understand What is Required to Produce this Excel Task
2. View the Excel supply requirement. View the Excel chart requirements.
3. View the Excel chart requirements.

Describes What is Required to Create the Computer Supplies On Hand Task

1. Understand What is Required to Produce this Excel Task.
2. View the Excel supply requirement.
3. View the Excel chart requirements.

In This Lesson, You Will Create A Table Of Supplies And Quantities

• Navigate the Excel User Interface to Create the Excel Table.
• Create the Excel Supplies and Quantities Workbook Enter Supply Data into Cells.
• Insert and Delete Rows and Columns.

Format the Excel Spreadsheet

1. Select the Home Tab to Use the Fill Bucket, Font, And Border Icons
2. Create Excel Borders Using the Border Icon
3. Create Colors for The Data Using the Fill Bucket Icon
4. Increase or Decrease the Font Size Using the Font Icon
5. Use the Shortcut Menus to Select Fonts, Borders, And Colors.

View the Excel Chart Elements to Enhance the Chart to Provide More Information.

1. Select the Chart Axis.
2. Select the Data Labels.
3. Select the Data Table.
4. Remove the Grid Lines.
5. Select the Legend.

Add the Excel Chart Elements to Display More Information in The Chart

1. Change the X Axis Title.
2. Change the Y Axis Title.
3. Change the Main Chart Title.
4. Change the Bar Colors.
5. Change the Data Point Size.
6. Change the Color of The Data Points.

Test the Excel Spreadsheet for Updating Capabilities

1. Change the Cell Data to Ensure the Spreadsheet Updates.
2. Update the Supply Quantities.
3. Update the Data Points on Top of The Chart Bars.
4. Update the Data Bar Sizes.

Set Up the Excel Spreadsheet for Printing

1. Set A Print Area.
2. Select Landscape Orientation.
3. Select Scaling.
4. Select Fit Sheet to One Page.

Summary of Task Performed to Create the Excel Supplies on Hand Spreadsheet

1. Created A Table of Data.
2. Created A Basic Chart.
3. Added the Chart Elements.
4. Selected a Chart Style.
5. Formatted the Data Using the Font, Fill Bucket and Border Icons.
6. Formatted the Cell Data with Colors.
7. Formatted the Chart with Colors.

When You Complete This Exercise,  In General You will be able to:

1. Create a table of data display computer supplies.
2. Create a chart displaying the computer supplies.
3. Format the data.
4. Format graphic elements.
5. Resize charts, add and modify chart elements, apply chart layouts and styles, move charts to a chart sheet.
6. Use the Insert Tab to select a recommended chart.
7. View and select a range of chart styles.

Describes the Overall Objective of The Excel Balance Sheet Task Including:

1. A Picture of The Excel Spreadsheet to Be Created.
2. The Data to Be Entered the Spreadsheet
3. The Total and Ratio Formulas to Create the Summary Data.
4. Describes the Asset Chart to Be Created.
5. Describes the Liabilities Chart to Be Created.

Describes the Requirements to Create the Excel Balance Sheet

In This Task, You Will Create an Excel Spreadsheet Showing the Following:

1. Assets.
2. Liabilities.
3. Working Capital.
4. The Ratios.
5. The Assets Chart.
6. The Liabilities Chart.

Enter Data in The Excel Spreadsheet for The Current Assets

1. Enter the Asset Categories and Amounts.
2. Perform Excel Calculations to Summarize the Data.
3. Format the Data to Reflect Currencies.

Enter Data in The Excel Spreadsheet for The Other Assets

1. Enter the Other Asset Categories and Amounts.
2. Perform Excel Calculations to Summarize the Data.
3. Format the Data to Reflect Currencies.

Enter Data in The Excel Spreadsheet for The Current Liabilities

1. Enter the Liabilities categories and Amounts.
2. Perform Excel Calculations to Summarize the Data.
3. Format the Data to Reflect Currencies.

Enter Data in The Excel Spreadsheet for The Other Liabilities

1. Enter the Other Liabilities Categories and Amounts.
2. Perform Excel Calculations to Summarize the Data.
3. Format the Data to Reflect Currencies.

Make A Few Adjustments to The Excel Balance Sheet and Calculate Totals

Insert and Delete Rows

Insert and Delete Columns

Align the Excel Total Asset Lines with The Total Liabilities Lines.

Create the Excel Total Asset Chart

1. Use the Insert and Recommended Chart Tab to Select a Chart.
2. Select a Chart Style in The Chart Styles Group.
3. Add Excel Chart Colors and Patterns
4. Format the Axis Using the Format Axis Dialog Box.
5. Format the Data Series Using the Format Data Series Box.
6. Resize and Move the Chart.

Create the Excel Total Liabilities Chart for the Balance Sheet

1. Use the Insert and Recommended Chart Tab to Select a Chart.
2. Select a Chart Style in The Excel Chart Styles Group.
3. Add Excel Chart Colors and Patterns.
4. Format the Axis Using the Format Axis Dialog Box.
5. Format the Data Series Using the Format Data Series Box.
6. Resize and Move the Chart.

Move the Charts and Format the Data

1. Relocate the Ratios to Another Area of The Excel Spreadsheet
2. Relocate the Charts to Another Location.
3. Insert and Delete Rows to Adjust the Chart Location.
4. Insert and Delete Columns to Adjust the Chart Location.

Set Up the Excel Spreadsheet for Printing

1. Set A Print Area.
2. Select Landscape Orientation.
3. Select Scaling.
4. Select Fit Sheet to One Page.

Summary of Tasks Performed to Create the Excel Balance Spreadsheet

1. Enter Cell Data for The Total Assets.
2. Enter Cell Data for The Excel Total Liabilities.
3. Perform Calculations to Calculate the Totals.
4. Perform Calculations to Calculate the Ratios.
5. Create an Excel Assets Chart.
6. Create A Liabilities Chart.
7. Format the Charts for Presentation.
8. Resized the Charts
9. Relocated the Ratios.
10. Relocated the Charts.

In this exercise, you will 

1. Perform operations with formulas.
2. Create ratios.
3. Create the totals and sub-totals for the assets and liabilities.
4. Format the asset chart using the chart elements.
5. Format the liabilities chart using the chart elements.
6. Resize charts, add and modify chart elements, apply chart layouts and styles, move charts to a chart sheet.
7. Create a new chart, add additional data series, switch between rows and columns in source data.

Describes the Goal of The Creating the Excel Break Even Analysis Spreadsheet 

1. Calculate the Totals for The Various Break Even Categories.
2. Create Data Names for The Data Used in The Calculations.
3. Create Special Excel Data Categories to Use in The Calculations.
4. Calculate the Sales Volume Data.
5. Create Two Pie Charts.
6. Create A Line Chart.

Describe the Requirements of The Excel Break-Even Analysis Task

1. Perform Calculations for The Following:
2. The Total Sales Amount.
3. The Variable Costs.
4. The Fixed Costs Per Period Calculation.
5. An Excel Chart Displaying the Unit Contribution Margin.
6. A Chart Displaying the Variable Costs.
7. A Chart Displaying the Sales Volume Data.

Create the Sales Data for The Excel Breakeven Analysis Spreadsheet

1. Enter the Titles.
2. Enter the Sales Data.
3. Create the Excel Formulas to Calculate the Total Sales Amount.
4. Format the Data Using the Currency Selection.

Create the Variable Cost Data for The Excel Breakeven Analysis Spreadsheet

1. Enter the Titles.
2. Enter the Variable Cost Data.
3. Create Excel Formulas to Calculate the Variable Cost Total.
4. Format the Data Using the Currency Selection.
5. Calculate the Unit Contribution Margin.
6. Calculate the Gross Margin.

Create the Fixed Costs Per Period Data for The Excel Breakeven Spreadsheet

1. Enter the Titles.
2. Change the Font Size.
3. Enter the Cost Data.
4. Calculate the Total Fixed Costs.
5. Format the Costs to Show Dollars and Cents.
6. Calculate the Net Profit (Loss).
7. Calculate the Break-even Point (Units).

Create the Excel Data Names to Use in Calculating the Sales Volume Data

1. Assign Data Names to The Key Metrics.
2. Use the Excel Data Name Command to Create the Data Names For 9 Key Metrics.
3. Create A Multiplier Used to Calculate the Sales Volume Per Period Data.

1. Create an Additional Data Source to Use in Excel Calculations.
2. Create a Multiplier to Calculate Excel Data.

Create the Sales Volume Analysis for The Excel Breakeven Analysis Spreadsheet

1. Calculate the Data for The Following:
2. Sales Volume Per Period (Units).
3. Sales Price Per Unit.
4. Fixed Costs Per Period.
5. Variable Costs.
6. Total Costs.
7. Total Sales.
8. Net Profit (Loss).

Create an Excel Break-Even Analysis Chart for The Sales Volume Data

1. Insert A Line Chart Design Using the Recommended Chart Command.
2. Use the Design Tab to Select a Chart Style for The Charts.
3. Re-size the Chart.
4. Move the Chart to A New Location.

Test the Excel Breakeven Analysis Spreadsheet for Updating Capacities

1. Change the Data Within the Key Metrics.
2. Check to Determine If the Charts Reflect the New Data.
3. Check to Determine If the Sales Volume Analysis Data Changed.

Summarizing the Results of The Excel Breakeven Analysis Job Performance Task

In This Lesson, You Learned How To:

1. Create Data Names Using the Data Name Command.
2. Insert Charts Using the Insert Tab and Recommended Chart Command.
3. Format the Charts for Presentation.
4. Re-Size the Charts for Relative to The Data Presented.
5. Change the Chart Elements.
6. Use Data Names to Calculate Totals.

1. Perform various calculations using formulas.
2. Create data names used to perform accurate calculations.
3. Create two pie charts.
4. Create one line chart with four lines of data.
5. Change the chart elements
6. Resize and adjust the charts for presentation.
7. Format the chart data to add percentages.

Create an Excel Spreadsheet Showing the Following:

1. A Pivot Table.
2. An Excel Pivot Chart.
3. A Slicer.
4. Key Totals.

Describes What Is Required to Develop the Excel Monthly Budget Report

1. The Projected Balance.
2. The Actual Balance.
3. Calculate the Difference Between Projected Spending Versus Actual Spending.
4. Create and Format the Excel Pivot Table.
5. Create and Format the Pivot Chart.

Provides the Detail Data to Create the Excel Monthly Budget Report

1. Calculate the Difference Between the Projected Cost Versus the Actual Cost.
2. Formatted the Data as Currency.

Creating the Excel Pivot Table for The Monthly Budget Report

1. Select the Data for The Pivot Table.
2. Create the Excel Pivot Table.
3. Format the Pivot Table.
4. Change the Pivot Table Style.
5. Change the Pivot Table Calculation Selections.

Create the Balance Data in The Excel Spreadsheet

1. Calculate the Balance Totals.
2. Calculate the Income Totals.
3. Calculate the Expense Totals.

Create an Excel Pivot Chart for The Monthly Budget Report

1. Select the Pivot Table.
2. Insert the Pivot Table Chart.
3. Select A Chart Style.
4. Modify the Chart for Presentation.

Test the Excel Spreadsheet to Ensure That It Updates

1. Change the Projected or Actual Cost in The Detail Data.
2. Observe the Pivot Table Changes.
3. Observe the Balance Changes.
4. Observe the Pivot Chart Changes.

Format the Excel Spreadsheet for Presentation

1. Add Lines and Borders.
2. Add Colors.
3. Adjust the Rows and Columns.

Summarizing the Results of The Excel Monthly Budget Report

1. The Pivot Table.
2. The Pivot Chart.
3. The Balance Data.

Summarize, Analyze, Explore, And Present Your Excel Data.

1. Work with The Pivot table Fields List.
2. Manually Drag-And-Drop Any Available Item into Any of The Pivot table Fields.
3. Rearrange Field Items.
4. Summarize Values by Sum.

Create A Slicer for The Monthly Budget Report

1. Create A Drop-Down List in Cells.
2. Use the Data Tab and Data Validation Command.
3. Format the Drop Down List to A Horizontal Presentation.
4. Re-size the Slicer.
5. Select A Slicer Design.
6. Filter The Pivot Table Using the Slicer.

Create an Excel a Pivot Chart to Show the Data Series, Categories, And Chart Axes.

1. Use the Insert Tab and Recommended Charts Tab to Select a Pivot Chart Style.
2. Add or Change Chart Elements Such as Titles or Data Labels.
3. Use the Analyze, Design, And Format Tabs to Format the Pivot Chart.

Describes What You Will Accomplish in The Excel Financial Report Tasks.

1. Provides an Overall View of The Tasks to Create the Financial Report.
2. View the Report Dashboard.
3. View the Detail Data Required for The Excel Dashboard.

Create A Drop Down Selection List to Select the Metric Year.

1. Create The 5 Key Metrics on The Dashboard.
2. Format the Metrics for Dollars and Cents.
3. Create A 5 Year Trend Line for Each Metric.
4. Create an Excel Dashboard.

Format the Detail Excel Data for Presentation

1. Create an Excel Data Name for The Metric Years.
2. Add the Colors.
3. Change the Font Size.

Create A Drop Down List for The Years Metrics

1. Create A Data Name for The Years.
2. Use the Data Tab.
3. Use the Data Validation Command.
4. Use Autofill to Fill Cell Data.

Create A Drop Down List for The Years Metrics

1. Create A Data Name for The Years.
2. Use the Data Tab.
3. Use the Data Validation Command.
4. Use Autofill to Fill Cell Data.

CREATE THE EXCEL YEAR DROP DOWN LISTS

1. Create A Data Name for The Years.
2. Use the Data Tab.
3. Use the Data Validation Command.
4. Use Autofill to Fill Cell Data.

Create the Excel Key Metric Years

1. Create the Excel Years.
2. Create the Calculation Point for Each Year.

Create Excel References and Pointers to The Metric Years

1. Create Data Names.
2. Create Excel Column Pointers to The Metrics Using the Match Formula.

Create Excel References and Pointers to The Financial Data

1. Create Data Names.
2. Create Pointers to The Financial Data Using the Index Formula.
3. Use AutoFill to Complete the Data.

Create Excel References and Pointers to The Key Dates

1. Create Data Names.
2. Create Pointers to Dates Using the Match Formula.
3. Use Autofill to Complete the Data.

Create the Financial Data for the Excel Dashboard

1. Create the pointers to the Financial Data.
2. Autofill the Data Vertically.
3. Autofill The Data Horizontally.

Create the Percentages for the Excel Dashboard

1. Calculate the Percentages.
2. Insert the Percentages into the Dashboard.

Format the Excel Dashboard

Use Conditional Formatting.

Create Conditional Iron Sets.

Create Sparklines.

Format the Excel Dashboard for Presentation

1. Create the cell borders, colors, and fonts.
2. Use the Alignment Group to Align the Dashboard data.

Create the Remaining Excel Financial Metrics

1. Create the Index Pointers to The Financial Data.
2. Create the Financial Data Using Autofill.

Create the Excel Previous Year and Current Year Financial Data

1. Create the Previous Year References to The Financial Data.
2. Create the Current Year References to The Financial Data.

Create the Spark lines

Use the Spark line Icon to Insert Spark lines.

Create a 5 Year Trend Line for Each Financial Metric.

Test the Excel Financial Report to Ensure Its Accuracy

1. Select and Change the Financial Data.
2. Check to Ensure that the Dashboard Metrics Update with New Data.

Configure Excel Worksheets for Printing

1. Set A Print Area.
2. Set Print Scaling.
3. Set Orientation.
4. Set Margins.

Summarizes the Excel Task Performed in Creating the Annual Report

1. Using the Data Tab and Data Validation to Create the Drop Down List.
2. Using the Match Formula.
3. Using the Index Formula.
4. Using Data Names.
5. Using Conditional Formatting.

Create an Excel Data Validation List

1. Use Data tab, in the Data Tools group, click Data Validation.
2. Select List in the Allow Selection.
3. Select the List Source.

A Conclusion of What Was Performed Throughout the Course

Understand and Use Excel Text Formulas

1. Use the Concat Formula.
2. Use the Len Formula.
3. Use the Upper Formula.
4. Use Several Other Text Formulas.

Understand How Excel Logical Formulas Work

1. Create the IF Formula.
2. Create the AND Formula.
3. Create the OR formula.

Work with the Quick Analysis Tool to Analyze Data

1. Use Quick Formatting.
2. Use Quick Charts.
3. Use Quick Totals.

Create and Manage Excel Tables

1. Turn A Range of Cells into A Microsoft Office Excel Table 
2. Manage the Data in The Table Rows and Column.
3. Convert A Table to A Cell Range.

Understand How Date Formulas Work

1. Create Fiscal Year Dates.
2. Use the Data Formulas.
3. Calculate the Days Between Two Dates.
4. Use Other Date Formulas.

Work with Conditional Formulas

1. Use the SUM IF Formula.
2. Use the Average IF formula.
3. User several other formulas.