10.10: Chapter Review - Practice Exercises
-
- Last updated
- Save as PDF
Practice Exercises
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.
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.
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.
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 |
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 |
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 $ |
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 |
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.
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! |
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 |
Replicate the table in this figure using a Theme and Style of your choice. Explain your process step-by-step.