Microsoft Excel Job Performance Task Creating Spreadsheets
4.3 (2 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
18 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Microsoft Excel Job Performance Task Creating Spreadsheets to your Wishlist.

Add to Wishlist

Microsoft Excel Job Performance Task Creating Spreadsheets

Creating Excel Spreadsheets Providing You With On The Job Experience Using Excel
4.3 (2 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
18 students enrolled
Created by Achilles Carroll
Last updated 2/2017
English
Curiosity Sale
Current price: $10 Original price: $20 Discount: 50% off
30-Day Money-Back Guarantee
Includes:
  • 5 hours on-demand video
  • 30 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • • Immediately apply for jobs specifying the usage of Microsoft office. • Gain first-hand knowledge and confidence on how to create high level excel spreadsheets that projects clear and concise information. • Create dashboards which are one of the highest level of use by excel. • Create tables, charts, and pivot tables. • Perform operations with formulas and functions. • Analyze and assess data using quick analysis. • Design excel spreadsheets to present decision impact data. • Format and organize excel spreadsheets for data clarity. • Use formulas and functions to calculate and solve problems. • Add and format charts to visualize data. • Use quick analysis to analyze and present data.
View Curriculum
Requirements
  • You need a PC and Internet Access. You will need access to Microsoft Excel 2007, 2010, 2013, or 2016. If you do not have access and want to take this course, you may obtain access to Microsoft Excel 2016 by subscribing with Office 365.
Description

MICROSOFT EXCEL JOB PERFORMANCE TASK 

  1. In this course, you will discover the confidence to handle Excel.  
  2. You will take the take the high road to learning Excel.  
  3. You will create spreadsheets at the highest level and that is summarizing data that reflects a picture in which the viewer can look at the picture and readily make decisions about the data.
  4. With this course, you will easily learn how to do that. 
  5. You want the viewer to immediately gain knowledge from the spreadsheet.

BUILD YOUR EXCEL SKILL LEVEL PERFORMING THE TASKS THROUGHOUT THE COURSE. YOU WILL USE THE FOLLOWING EXCEL TABS AND COMMANDS:

  1. THE HOME TAB INCLUDING
  • CLIPBOARD
  • FONT    
  • ALIGNMENT
  • NUMBER            
  • STYLES 
  • CELLS    

 2. THE INSERT TAB INCLUDING

  • PIVOT TABLES
  • RECOMMENDED CHARTS
  • SPARK LINES
  • CHART STYLES
  • PIVOT TABLE STYLES
  • DESIGN TAB
  • OPTIONS TAB

3.  THE DATA TAB INCLUDING

  • DATA VALIDATION

4.  THE FORMULAS TAB INCLUDING

  • LOGICAL             
  • TEXT     
  • DATE & TIME
  • LOOKUP & REFERENCE
  • MATH & TRIG
  • STATISTICAL

 GET STARTED TODAY 

  • Once you complete the course, you can apply for jobs requesting experience with Microsoft Office.  This course will create a lot of success for you in the job market, because Microsoft Office and Excel supports thousands of jobs in the job market.  
  • This increases your chances of getting hired using Microsoft Office. Just google one of the major job sites and enter Microsoft Office, and you will see thousands of job opportunities.
  •  You are welcome to preview some of the lectures for free, below and then, you will be able to enroll with confidence!

WHAT YOU WILL FIND IN THIS COURSE

Here is what you are going to discover in side this detailed, step-by-step course ….

The top goal for you in this course is to take the high road to learning Excel.  The highest level of Excel is to be able to create spreadsheets that summarize data that reflects a picture in which the viewer can look at the picture readily make decisions about the data. With this course, you will easily learn how to do that.

 

For each Microsoft Excel Performance Task, you will have a Goal video, a Requirements video, the step by step tasks, and at the end each task, a conclusion video. These three videos will point you in the right direction in accomplishing the task.

 

You will easily learn how to create a table of data with the supplies and quantities and a chart to display the supplies and quantities.  This is the Supplies and Quantities On-Hand Task.

 

You will easily learn how to create a spreadsheet showing data that require the use of the basic math formulas of addition, subtraction, multiplication, division, along with the charts to reflect the data. This is the Balance Sheet Task with Assets and Liabilities, and two charts.

 

You will easily learn how to create a spreadsheet showing data with different categories, different formulas, defining data names, range names, and charts to reflect the nature of the data.  This is the Breakeven Analysis Task, with various categories of data, and three charts to reflect the data.

 

You will easily learn how to create a spreadsheet showing monthly budget data, including calculations for detail data, pivot tables, slicers, and pivot charts. This is the Monthly Budget Task.

 

You will easily learn how to create a spreadsheet dashboard using data validation, conditional formatting, pointers, references, match and index formulas. The is the Financial Report Task.

 

For each spreadsheet, you will learn easily learn how to test each spreadsheet to ensure that it updates with new data.

 

For each spreadsheet, you will learn how to setup the spreadsheet for printing.

Who is the target audience?
  • This course is for beginners and those who want to get into the Information Technology Job Market or enhance their job skills with Excel.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
84 Lectures
05:05:24
+
Microsoft Excel Job Performance Task Introduction
1 Lecture 04:54
+
The Excel Computer Supplies On Hand Job Performance Task
11 Lectures 26:44

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.
THE GOAL OF COMPUTER SUPPLIES ON HAND TASK
00:43

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.


THE REQUIREMENTS FOR THE COMPUTER SUPPLIES ON HAND TASK
00:48

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.


CREATING THE TABLE OF SUPPLIES AND QUANTITIES
03:32

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.


FORMATTING THE TABLE OF SUPPLIES AND QUANTITIES
03:50

Create A Basic Excel Chart

  1. Use the Insert Tab, And Recommended Chart Tab to Insert a Chart
  2. Navigate Through the Different Excel Chart Styles to Select a Chart
  3. Move and Align the Chart
  4. Resize the Chart
Preview 03:17

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.


VIEWING AND SELECTING CHART OR GRAPH ELEMENTS
03:22

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.


CHANGING THE CHART ELEMENTS TO DESCRIBE THE CHART
05:50

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.
TESTING THE COMPUTER SUPPLIES ON HAND SPREADSHEET FOR UPDATING CAPABIL
02:07

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.
SETTING UP THE SPREADSHEET FOR PRINTING
01:28

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.
SUMMARIZING THE RESULTS OF THE COMPUTER SUPPLIES ON HAND TASK
01:03

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.
COMPUTER SUPPLIES EXERCISE
00:44
+
The Excel Financial Balance Sheet Job Performance Task
15 Lectures 53:45

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.
THE GOAL OF THE BALANCE SHEET TASK
01:18

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.


THE REQUIREMENTS OF THE BALANCE SHEET TASK
01:19

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.
CREATING THE CURRENT ASSETS FOR THE BALANCE SHEET TASK
04:33

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.


CREATING THE OTHER ASSETS FOR THE BALANCE SHEET TASK
04:17

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.


CREATING THE CURRENT LIABILITIES FOR THE BALANCE SHEET TASK
04:07

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.


CREATING THE OTHER LIABILITIES DATA FOR THE BALANCE SHEET TASK
03:56

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.

MAKING ADJUSTMENTS AND CALCULATING THE TOTALS
05:55

Create the Four Key Metrics for The Excel Spreadsheet

  1. Perform Calculations to Create the Quick Ratio Metric.
  2. Perform Calculations to Create the Working Capital Metric.
  3. Perform Excel Calculations to Create the Current Ratio
  4. Perform Calculations to Create the Current Ratio
  5. Format the Ratios Using the Format Cells Dialog Box.


Preview 06:00

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.
CREATING THE TOTAL ASSET CHART FOR THE BALANCE SHEET
06:06

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.


CREATING THE TOTAL LIABILTIES CHART FOR THE BALANCE SHEET
03:10

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.


MAKING CHANGES TO THE BALANCE SHEET
05:28

Test the Excel Spreadsheet for Updating Capabilities

  1. Change the Cell Data to Ensure the Spreadsheet Updates.
  2. Update the Total Assets.
  3. Update the Total Liabilities.
  4. Observe that the Excel Asset Chart expanded or contracted.
  5. Observe that the Liabilities Chart expanded or contracted.
  6. Observe that the Totals Changed for the Liabilities and the Assets.
Preview 02:35

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.


SETTING UP THE BALANCE FOR PRINTING
02:17

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.
SUMMARIZING THE RESULTS OF THE BALANCE SHEET TASK
02:03

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.


Balance Sheet Exercise
00:41
+
The Excel Breakeven Analysis Job Performance Task
13 Lectures 54:25

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.
THE GOAL OF THE BREAKEVEN ANALYSIS TASK
02:11

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.
THE REQUIREMENTS OF THE EXCEL BREAKEVEN ANALYSIS TASK
01:05

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.
CREATING THE EXCEL BREAKEVEN ANALYSIS SALES DATA
04:42

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.
CREATING THE EXCEL BREAKEVEN ANALYSIS VARIABLE COST DATA
06:22

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).
CREATING THE EXCEL BREAKEVEN FIXED COSTS PER PERIOD DATA
08:02

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.


CREATING THE DATA NAMES USED TO CREATE THE SALES VOLUME ANALYSIS DATA
05:24

  1. Create an Additional Data Source to Use in Excel Calculations.
  2. Create a Multiplier to Calculate Excel Data.
LESSON 30 CREATING THE MUTIPLIER FOR THE SALES VOLUME ANALYSIS DATA - PART 2
02:06

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).
CREATING THE EXCEL SALES VOLUME ANALYSIS DATA
06:13

Create Two Excel Breakeven Analysis Charts for The Spreadsheet

  1. Create Variable Costs Per Unit Chart.
  2. Create Total Variable Costs Excel Chart.
  3. Use the Design Tab to Select a Chart Style for Both Charts.
  4. Format the Chart to Include Cost and Percentages for Both Charts.
  5. Re-size the Chart.
  6. Move the Charts to A New Location.
Preview 05:31

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.


CREATING THE EXCEL BREAKEVEN CHART FOR THE BREAKEVEN DATA
04:33

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.

  


TESTING THE EXCEL BREAKEVEN SPREADSHEET FOR UPDATING CAPABILITIES
02:30

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.


SUMMARIZING THE RESULTS OF THE EXCEL BREAKEVEN ANALYSIS TASK
04:58

  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.
Breakeven Analysis Exercise
00:48
+
THE EXCEL MONTHLY BUDGET REPORT JOB PERFORMANCE TASK
13 Lectures 54:05

Create an Excel Spreadsheet Showing the Following:

  1. A Pivot Table.
  2. An Excel Pivot Chart.
  3. A Slicer.
  4. Key Totals.
THE GOAL OF THE MONTHLY BUDGET REPORT JOB PERFORMANCE TASK
01:40

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.


THE REQUIREMENTS OF THE MONTHLY BUDGET REPORT
02:05

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 DETAIL DATA FOR THE MONTHLY BUDGET REPORT
03:06

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.
CREATING THE PIVOT TABLE FOR THE MONTHLY BUDGET REPORT
09:04

Create the Balance Data in The Excel Spreadsheet

  1. Calculate the Balance Totals.
  2. Calculate the Income Totals.
  3. Calculate the Expense Totals.
CREATING THE BALANCE DATA FOR THE MONTHLY BUDGET REPORT
05:02

Create A Slicer for The Excel Monthly Budget Report

  1. Insert A Slicer.
  2. Modify the Slicer Column Size.
  3. Adjust the Slicer Size.
  4. Test the Slicer.


Preview 02:32

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.
CREATING THE PIVOT CHART FOR THE MONTHLY BUDGET REPORT
04:41

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.


TESTING THE SPREADSHEET FOR UPDATING CAPABILITIES
05:10

Format the Excel Spreadsheet for Presentation

  1. Add Lines and Borders.
  2. Add Colors.
  3. Adjust the Rows and Columns.
FORMATTING THE DASHBOARD FOR PRESENTATION
09:45

Summarizing the Results of The Excel Monthly Budget Report

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


SUMMARIZING THE RESULTS OF THE MONTHLY BUDGET REPORT
04:43

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 THE PIVOT TABLE EXERCISE
04:41

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 THE SLICER EXERCISE
00:48

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.
CREATE THE PIVOT CHART EXERCISE
00:48
+
THE ANNUAL FINANCIAL REPORT JOB PERFORMANCE TASK
23 Lectures 01:26:27

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.
THE GOAL OF THE ANNUAL FINANCIAL REPORT JOB PERFORMANCE TASK
02:37

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.
THE REQUIREMENTS OF THE ANNUAL FINANCIAL REPORT
01:56

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.
VIEWING AND FORMATTING THE DETAIL DATA
02:16

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.
CREATING THE SELECTION LIST FOR THE METRICS
04:28

Create Excel References and Pointers to The Financial Data and Metric Years

  1. Create Data Names.
  2. Create Excel Row Pointers to The Metrics Using the Match Formula.
  3. Create Pointers to The Financial Data Using the Index Formula.
Preview 03:59

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.
CREATING THE SELECTION LIST FOR THE METRIC YEARS
04:14

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.
CREATING THE KEY YEARS TO USE FOR THE DASHBOARD
03:16

Create the Excel Key Metric Years

  1. Create the Excel Years.
  2. Create the Calculation Point for Each Year.
CREATING THE METRIC YEARS BASED ON THE YEAR SELECTED
04:18

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.
CREATING THE POINTERS TO THE METRIC YEARS BASED ON THE YEAR SELECTED
04:31

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.
CREATING THE INDEXES FOR THE FIVE KEY METRICS TO DISPLAY THE FINANCIA
07:11

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.
CREATING THE ANNUAL FINANCIAL REPORT DASHBOARD PART 1
02:34

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.
CREATING THE ANNUAL FINANCIAL REPORT DASHBOARD PART 2
03:30

Create the Percentages for the Excel Dashboard

  1. Calculate the Percentages.
  2. Insert the Percentages into the Dashboard.
CREATING THE ANNUAL FINANCIAL REPORT DASHBOARD PART 3
03:59

Format the Excel Dashboard

Use Conditional Formatting.

Create Conditional Iron Sets.

Create Sparklines.

CREATING THE ANNUAL FINANCIAL REPORT DASHBOARD PART 4
04:01

Format the Excel Dashboard for Presentation

  1. Create the cell borders, colors, and fonts.
  2. Use the Alignment Group to Align the Dashboard data.
CREATING THE ANNUAL FINANCIAL REPORT DASHBOARD PART 5
05:24

Create the Remaining Excel Financial Metrics

  1. Create the Index Pointers to The Financial Data.
  2. Create the Financial Data Using Autofill.
CREATING THE ANNUAL FINANCIAL REPORT DASHBOARD PART 6
05:14

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.
CREATING THE ANNUAL FINANCIAL REPORT DASHBOARD PART 7
03:27

Create the Excel Detail Financial Data for the Metrics

  1. Insert the References to the Financial Data.
  2. Insert the Related Percentages.
  3. Use Auto Fill to Complete the Data.



Preview 02:35

Create the Spark lines

Use the Spark line Icon to Insert Spark lines.

Create a 5 Year Trend Line for Each Financial Metric.

CREATING THE FIVE YEAR TREND LINE FOR THE METRICS
05:54

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.
TESTING THE ANNUAL FINANCIAL REPORT FOR UPDATING CAPABILITIES
03:19

Configure Excel Worksheets for Printing

  1. Set A Print Area.
  2. Set Print Scaling.
  3. Set Orientation.
  4. Set Margins.
SETTING UP THE REPORT FOR PRINTING
01:45

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.
SUMMARIZING THE ANNUAL FINANCIAL REPORT JOB PERFORMANCE TASK
05:11

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.
Create a Drop Down List of Metrics Exercise
00:48
+
Job Performance Task Conclusion
1 Lecture 04:41

A Conclusion of What Was Performed Throughout the Course

Conclusion
04:41
+
Extra Curricula Extension Tasks
7 Lectures 20:23

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.


Working with Text Formulas
02:42

Understand How the Excel VLOOKUP And Hlookup Formulas Work

  1. Use the VLOOKUP Formula to Lookup Data Vertically.
  2. Use the Hlookup Formula to Lookup Data Horizontally.
Preview 02:46

Understand How Excel Logical Formulas Work

  1. Create the IF Formula.
  2. Create the AND Formula.
  3. Create the OR formula.
Working with Logical Formulas
04:01

Work with the Quick Analysis Tool to Analyze Data

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


Working with Quick Analysis
03:15

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.


Create and Manage Tables
01:48

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.
Working with Date Formulas
03:04

Work with Conditional Formulas

  1. Use the SUM IF Formula.
  2. Use the Average IF formula.
  3. User several other formulas.
Working with Conditional Formulas
02:47
About the Instructor
Achilles Carroll
4.3 Average rating
2 Reviews
18 Students
1 Course
Software Trainer

Achilles Carroll, Innovation – Creativity – Imagination

If you want to be successful with Microsoft Excel, then this course is for you. 

This course will provide you with the knowledge and confidence in creating Excel spreadsheets.

My mission is simple: To enhance your skill level so that you can begin or transition into an Information Technology job, complete with benefits, such as, paid time off, paid holidays, life insurance, etc. 

The information technology job market is the largest job market in the world and after you get started in the field, you can transition from different career aspects of technology.

You can transition from Excel to several information technology fields, such as, transitioning from Excel to Database Administration, Configuration Management, ITIL, or Software Testing, etc. 

Here’s a little background on my experiences, so that you can get a sense of who will be teaching and mentoring you:

Twenty-five years of experience in working with the various Information Technology mainstream products, such as, Database Administration, Microsoft SQL, Oracle SQL, SharePoint, Microsoft Access, Excel, and PowerPoint Integration, ITIL Data Management.

You can see my certifications here:

 1.  ITIL               IT Service Management                                     March        2016

 2.  MCPD          SharePoint Application Developer                      April           2013

 3.  MCTS           SharePoint Application Designer                        April           2013

 .4  MCT              Microsoft Certified Trainer                                  April            2013

  5. MS-SQL        Microsoft Database Administrator                     May             2011

  6. Oracle SQL   Oracle Certified SQL Expert                              March         2011

  7. Security +      CompTia                                                             February    2012

  8. MCSD.NET   Microsoft Certified Solution Developer.NET       March        2005

  9. MCAD.NET    Microsoft Certified Application Developer.NET September 2004

10. MCP                Microsoft  Certified Professional                       February    2003

You can earn certifications through preparation that we will keep you employed in the

job market for years to come and  with  a higher than average income. 

With certifications, you will never have to worry about being unemployed.