Workplace Software and Skills

# Practice Exercises

### 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. 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. 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.

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.