Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo

Table of contents
  1. Preface
  2. 1 Technology in Everyday Life and Business
    1. Chapter Scenario
    2. 1.1 Computing from Inception to Today
    3. 1.2 Computer Hardware and Networks
    4. 1.3 The Internet, Cloud Computing, and the Internet of Things
    5. 1.4 Safety, Security, Privacy, and the Ethical Use of Technology
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  3. 2 Essentials of Software Applications for Business
    1. Chapter Scenario
    2. 2.1 Software Basics
    3. 2.2 Files and Folders
    4. 2.3 Communication and Calendar Applications
    5. 2.4 Essentials of Microsoft 365
    6. 2.5 Essentials of Google Workspace
    7. 2.6 Collaboration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  4. 3 Creating and Working in Documents
    1. Chapter Scenario
    2. 3.1 Navigating Microsoft Word
    3. 3.2 Formatting Document Layout in Microsoft Word
    4. 3.3 Formatting Document Content in Microsoft Word
    5. 3.4 Collaborative Editing and Reviewing in Microsoft Word
    6. 3.5 Document Design
    7. 3.6 Navigating Google Docs
    8. 3.7 Formatting Layout and Content in Google Docs
    9. 3.8 Collaborative Editing and Reviewing in Google Docs
    10. 3.9 Versions and Version History
    11. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  5. 4 Document Preparation
    1. Chapter Scenario
    2. 4.1 Microsoft Word: Advanced Formatting Features
    3. 4.2 Working with Graphics and Text Tools in Microsoft Word
    4. 4.3 Managing Long Documents in Microsoft Word
    5. 4.4 Google Docs: Enhanced Formatting Features
    6. 4.5 Working with Graphics and Text Tools in Google Docs
    7. 4.6 Managing Long Documents in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  6. 5 Advanced Document Preparation
    1. Chapter Scenario
    2. 5.1 Creating Different Document Types in Microsoft Word
    3. 5.2 Mail Merge in Microsoft Word
    4. 5.3 Creating Forms in Microsoft Word
    5. 5.4 Creating Different Document Types in Google Docs
    6. 5.5 Creating Forms in Google Docs
    7. 5.6 Advanced Collaboration in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  7. 6 Preparing Presentations
    1. Chapter Scenario
    2. 6.1 Presentation and Design Essentials
    3. 6.2 Designing a Presentation in Microsoft PowerPoint
    4. 6.3 Formatting Microsoft PowerPoint Slides: Layout and Design Principles
    5. 6.4 Adding Visuals and Features to Microsoft PowerPoint Slides
    6. 6.5 Designing a Presentation in Google Slides
    7. 6.6 Creating Google Slides: Layout and Text
    8. 6.7 Adding Visuals and Features to Google Slides
    9. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  8. 7 Advanced Presentation Skills
    1. Chapter Scenario
    2. 7.1 Effective Presentation Skills
    3. 7.2 Finalizing a Slide Collection
    4. 7.3 Preparing a Microsoft PowerPoint Collection for Presentation
    5. 7.4 Preparing a Google Slides Collection for Presentation
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  9. 8 Content Management Systems and Social Media in Business
    1. Chapter Scenario
    2. 8.1 What Are Content Management Systems?
    3. 8.2 Common Content Management Systems
    4. 8.3 Creating Content with a Content Management System
    5. 8.4 Search Engine Optimization
    6. 8.5 Social Media in Business
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  10. 9 Working with Spreadsheets
    1. Chapter Scenario
    2. 9.1 Microsoft Excel Basics
    3. 9.2 Text and Numbers in Microsoft Excel
    4. 9.3 Calculations and Basic Formulas in Microsoft Excel
    5. 9.4 Formatting and Templates in Microsoft Excel
    6. 9.5 Google Sheets Basics
    7. 9.6 Text and Numbers in Google Sheets
    8. 9.7 Calculations and Basic Formulas in Google Sheets
    9. 9.8 Formatting and Templates in Google Sheets
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  11. 10 Advanced Excel Formulas, Functions, and Techniques
    1. Chapter Scenario
    2. 10.1 Data Tables and Ranges
    3. 10.2 More About Formulas
    4. 10.3 Using Arithmetic, Statistical, and Logical Functions
    5. 10.4 PivotTables
    6. 10.5 Auditing Formulas and Fixing Errors
    7. 10.6 Advanced Formatting Techniques
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  12. 11 Advanced Excel Spreadsheets: Statistical and Data Analysis
    1. Chapter Scenario
    2. 11.1 Understanding Data, Data Validation, and Data Tables
    3. 11.2 Statistical Functions
    4. 11.3 What-If Analysis
    5. 11.4 PivotTables/Charts
    6. 11.5 Data Analysis Charts
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  13. 12 Using Excel in Accounting and Financial Reporting
    1. Chapter Scenario
    2. 12.1 Basic Accounting
    3. 12.2 Financial Functions in Microsoft Excel
    4. 12.3 Integrating Microsoft Excel and Accounting Programs
    5. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  14. 13 Understanding and Using Databases
    1. Chapter Scenario
    2. 13.1 What Is a Database?
    3. 13.2 Microsoft Access: Main Features and Navigation
    4. 13.3 Querying a Database
    5. 13.4 Maintaining Records in a Database
    6. 13.5 Creating Reports in Microsoft Access
    7. 13.6 Creating Forms in Microsoft Access
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  15. 14 Advanced Database Use
    1. Chapter Scenario
    2. 14.1 Advanced Queries in Microsoft Access
    3. 14.2 Multiple Table Forms
    4. 14.3 Customizing Forms
    5. 14.4 Customizing Reports
    6. 14.5 Using Macros
    7. 14.6 Data Analysis and Integration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  16. 15 Integrating Applications
    1. Chapter Scenario
    2. 15.1 Microsoft 365: Collaboration and Integration
    3. 15.2 Microsoft Word: Integration with Microsoft Excel and Microsoft Access
    4. 15.3 Microsoft Word and Microsoft PowerPoint Integration
    5. 15.4 Microsoft Excel and Microsoft PowerPoint Integration
    6. 15.5 Microsoft Excel and Microsoft Access Integration
    7. 15.6 Integrating Data from Other Programs into Google Workspace
    8. 15.7 New Developments: The Role of Artificial Intelligence
    9. 15.8 Mastering Workplace Software Skills: A Project
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
  17. Index

Practice Exercises

16 .

WorldCorp sells servers to small and medium technology companies. WorldCorp offers different features for each model of server, but the average unit price is $1,500. Last year, they sold 550 servers in the Fairfax, Virginia, area. The product VP wants to calculate a forecast of how much revenue the company will make depending on how many they sell in the coming year. Revenue is determined by multiplying the Price * Quantity Sold. The VP wants you to complete a table with the revenue outputs. The VP wrote these numbers on a paper, and gave it to you to put into Excel. Use these numbers (units sold) for your autofilled data table.

A list of data on the left shows 350, 400, 450, 500, 550, 600, 650, 700. A table on the right shows Price, $1,500; Quantity Sold, 550; Revenue, $825,000.
17 .

WorldCorp builds TVs, and the best-selling model is the E-900. As you can see in the table, each model has a different sales price. Define a data range in this exercise. Choose one that you think is appropriate. Describe the steps you had to follow.

A table lists column headings Model, Inches, Price, Units Sold. Row 1 lists E-900s, 32, $170.00, 8752. Row 2 lists E-900m, 42, $380.00, 10563. Row 3 lists E-900l, 55, $550.00, 9543. Row 4 lists E-900xl, 65, $780.00, 4326.
18 .

WorldCorp manufactures 32-inch LCD screens that cost them an average of $85 per unit. The COO wants to save money by decreasing the manufacturing cost per average unit and thus the total Costs of Good Sold (COGS). Total COGS is determined by Unit Cost * Quantity Sold. The COO has started a table of possible savings by decreasing the unit cost, but wants an analyst to finish the table. What Excel tool or combination of tools would you use for completing this table? Describe your process, then complete the table. Lastly, define the second table using a data range method learned in this chapter.

Part a shows a table with Unit Cost, $85; Quantity Sold, 22,540; COGS, $1,915,900. Part b shows a table where column 1 lists blank, 15,000, 17,500, 20,000, 25,000. Columns 2 through 5 list $70, $75, $80, $85 in the first row. The remaining cells are blank.
19 .

The WorldCorp sales team uses a Customer Relationship Management (CRM) software to keep track of customer data. It tells them when they should call their customers again, how much the customer has ordered in the past, and other notes about the customer. The sales agents receive a salary, but if their customer orders are over $20,000, they are given an additional 5 percent commission. Use the information and the data in the figure to design a formula that can calculate the total commission the sales agent will receive and an overall total for commissions paid.

Date Item Description Quantity FOB$ 5% Commission for sales
3/29/2021 Tablets, 7in 143 $10,582.00 $2,736.00
3/27/2021 Computer Server Accessories 2500 $21,350.00 $3,552.00
3/22/2021 Headphones 542 $26,016.00 $10,140.00
3/21/2021 LED TVs, 55in and 65in 53 $14,575.00 $432.00
3/17/2021 LCD TVs, 32in and 45in 97 $64,505.00 $480.00
3/15/2021 Cellular Phone Accessories 3000 $22,920.00 $30,800.00
3/10/2021 Home Stereo Systems 74 $7,252.00 $10,920.00
3/7/2021 Blu-Ray Players 127 $10,922.00 $768.00
3/5/2021 HDTV Antennas 768 $21,504.00 $18,240.00
1/13/2021 Plasma 65 $855.00 12 $10,260.00
1/13/2021 Headphones $48.00 85 $4,080.00
1/15/2021 Headphones $48.00 158 $7,584.00
1/17/2021 LCD 42 $380.00 55 $20,900.00
1/17/2021 Headphones $48.00 115 $5,520.00
1/21/2021 Plasma 65 $855.00 11 $9,405.00
1/22/2021 Headphones $48.00 15 $720.00
1/25/2021 LCD 42 $380.00 54 $20,520.00
1/27/2021 QLED 55 $620.00 10 $6,200.00
1/30/2021 LED 55 $550.00 10 $5,500.00
2/4/2021 LCD 32 $170.00 43 $7,310.00
2/7/2021 QLED 55 $620.00 15 $9,300.00
2/9/2021 OLED 45 $400.00 37 $14,800.00
2/11/2021 Plasma 65 $855.00 34 $29,070.00
2/15/2021 DLP 32 $250.00 97 $24,250.00
2/18/2021 LED 65 $780.00 54 $42,120.00
2/21/2021 LED 55 $550.00 21 $11,550.00
2/25/2021 LCD 42 $380.00 54 $20,520.00
2/27/2021 LCD 32 $170.00 14 $2,380.00
Table 10.1
20 .

The Fairfax regional manager wants to know some Key Performance Indicators (KPIs) about their sales team. They need the average quantity sold, the top invoice, and the lowest invoice of all the sales agents. Use an array formula to figure out these metrics for each sales agent using the WorldCorp data.

Date Agent Item Description Quantity FOB$ 5% Commission for sales
3/29/2021 Antonio Tablets, 7in 143 $10,582.00 NO
3/27/2021 Izabelle Computer Server Accessories 2500 $21,350.00 YES
3/22/2021 Antonio Headphones 542 $26,016.00 YES
3/21/2021 Izabelle LED TVs, 55in and 65in 53 $14,575.00 NO
3/17/2021 James LCD TVs, 32in and 45in 97 $64,505.00 YES
3/15/2021 James Cellular Phone Accessories 3000 $22,920.00 YES
3/10/2021 Antonio Home Stereo Systems 74 $7,252.00 NO
3/7/2021 Izabelle Blu-Ray Players 127 $10,922.00 NO
3/5/2021 James HDTV Antennas 768 $21,504.00 YES
Table 10.2
21 .

The Fairfax location’s regional manager wants to know the maximum of the units sold per order, and the minimum FOB $. Use functions to find these values.

Date Agent Item Description Quantity FOB$
3/29/2021 Antonio Tablets, 7in 143 $10,582.00
3/27/2021 Izabelle Computer Server Accessories 2500 $21,350.00
3/22/2021 Antonio Headphones 542 $26,016.00
3/21/2021 Izabelle LED TVs, 55in and 65in 53 $14,575.00
3/17/2021 James LCD TVs, 32in and 45in 97 $64,505.00
3/15/2021 James Cellular Phone Accessories 3000 $22,920.00
3/10/2021 Antonio Home Stereo Systems 74 $7,252.00
3/7/2021 Izabelle Blu-Ray Players 127 $10,922.00
3/5/2021 James HDTV Antennas 768 $21,504.00
Max Units
Min $
Table 10.3
22 .

The Fairfax regional manager is comparing two customers. The manager wants to know when the units fall below the average for the customer and when the dollar per order is below the average. Use the Insert Function tool to find out which functions you can use for this purpose, then calculate these values. If one of the customers is more variable than the other, use an IF function to tell the manager either if it’s customer A or B.

Customer A
Date Port Item Description Quantity Unique Quantity Code FOB $
1/22/2021 Portland, ME Headphones 15 PCS $720.00
1/17/2021 Portland, ME Headphones 115 PCS $5,520.00
1/15/2021 Portland, ME Headphones 158 PCS $7,584.00
1/13/2021 Portland, ME Headphones 85 PCS $4,080.00
1/8/2021 Portland, ME Headphones 16 PCS $768.00
1/5/2021 Portland, ME Headphones 10 PCS $480.00
1/3/2021 Portland, ME Headphones 9 PCS $432.00
12/28/2020 Portland, ME Headphones 74 PCS $3,552.00
12/20/2020 Portland, ME Headphones 57 PCS $2,736.00
Customer B
Date Port Item Description Quantity Unique Quantity Code FOB $
1/20/2021 Portland, ME Headphones 43 PCS $2,064.00
1/15/2021 Portland, ME Headphones 39 PCS $1,872.00
1/12/2021 Portland, ME Headphones 45 PCS $2,160.00
1/9/2021 Portland, ME Headphones 38 PCS $1,824.00
1/4/2021 Portland, ME Headphones 47 PCS $2,256.00
12/27/2020 Portland, ME Headphones 31 PCS $1,488.00
12/20/2020 Portland, ME Headphones 44 PCS $2,112.00
12/18/2020 Portland, ME Headphones 34 PCS $1,632.00
12/15/2020 Portland, ME Headphones 35 PCS $1,680.00
Table 10.4
23 .

The WorldCorp Fairfax, Virginia, location has one client who purchased various TV technologies in 2021. The product manager for TVs wants to gather some metrics on which technology sells best for this customer, as well as the revenue per product type. Use the raw data in the "worldcorp_tv_data" tab of the downloadable Chapter 10 data file workbook to construct a PivotTable that shows the metrics according to what the product manager wants to see.

24 .

WorldCorp collects thousands of datasets every week. A large part of the managers’ jobs is monitoring this data, and they cannot monitor it efficiently if a workbook has mistakes. See this figure for a current dataset with some #VALUE! errors. What does this #VALUE! error code mean? What would you recommend doing in the formula audit process? What are the tools you would use, and how would you do it? Explain.

Operational Budget for the Fairfax, VA Manufacturing Plant
Component Projected Cost Actual Cost Difference
Direct Materials $52,536,474.63 $56,436,363.00 $3,899,888.37
Direct Labor $64,236,234.33 $67,436,356.00 $3,200,121.37
Manufacturing Overhead $53,386,433.99 $52,453,476.00 #VALUE!
Maintenance $535,252.00 $446,736.00 $(88,516.00)
Utility Services $4,526,236.00 $5,736,363.00 $1,210,127.00
Administrative Costs $4,256,236.00 $4,156,477.00 $(100,296.00)
Sales Costs $7,755,674.64 $7,537,363.00 $(218,311.64)
Depreciation $85,670.00 $86,845.00 $1,175.00

$187,318,748.59

$137,853,616.00

#VALUE!
Table 10.6
25 .

WorldCorp is buying an industrial robot that will pack small- to medium-sized consumer electronics in sealed plastic bags. The new machine will increase productivity because it will be faster than human workers. The financial manager is taking out a loan from the bank to pay for this machine; the loan will be paid out over the course of ten years. WorldCorp will pay 15 percent of the total cost up front, but the rest will be the principal for the loan. The total cost of the machine is $7,327,749. The interest rate is 5 percent. The manager has designed a repayment schedule, but clearly there are some mistakes in the table. How would you find the errors? Which tool would you use to fix them? Explain which tools you ultimately used to find and solve the errors.

Year Payment Principal Interest Balance
1 $806,630.47 $495,201.13 $311,429.33 $5,733,385.52
2 $806,630.47 $519,961.19 $286,669.28 $5,213,424.32
3 $806,630.47 $545,959.25 $260,671.22 $4,667,465.07
4 $806,630.47 $1,040,003.72 $233,373.25 $3,627,461.35
5 $806,630.47 $625,257.40 $181,373.07 $3,002,203.95
6 $806,630.47 $656,520.27 $150,110.20 $2,345,683.69
7 $806,630.47 $(46,107,043.25) $46,913,673.72 $48,452,726.93
8 $806,630.47 $(1,616,005.88) $2,422,636.35 $50,068,732.81
9 $806,630.47 $(1,696,806.17) $2,503,436.64 $51,765,538.99
10 $806,630.47 $(1,781,646.48) $2,588,276.95 $53,547,185.47
Table 10.7
26 .

Replicate the table in this figure using a Theme and Style of your choice. Explain your process step-by-step.

A table titled Average Percent Discount for Computer Servers shows the heading Discount Rates. Row 1 lists Month, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. Row 2 lists Average % discount, 0.5%, 2%, 3.5%, 2.5%, 4.15%, 4.28%, 5.32%, 2.87%, 1.8%, 5%, 12%, 15%.
Citation/Attribution

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.