Tammie Bolling; Angela Mitchell; Tanya Scott; Nyrobi Wheeler

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