NewSight Consulting Worksheet
PROJECT STEPS 1.Benicio Cortez is a financial consultant with NewSight Consulting in Denver, Colorado. He is working with DIG Technology, a company that sells five models of portable speakers to consumers in North America. In an Excel workbook, Benicio is analyzing the performance of each speaker model and projecting sales for a new product. He asks for your help in completing the analysis. Go to the U.S. worksheet. Benicio has received annual sales worksheets from the main offices in the three countries where DIG Technology sells products: the United States, Canada, and Mexico. The worksheets for the countries have a similar structure. Complete the worksheets by using a function to total the Mini sales amounts for Q1 through Q4 in the U.S., Canada, and Mexico worksheets. Fill the formula down to display the remaining totals for each type. 2.Go to the All Locations worksheet, where Benicio wants to summarize the quarterly and annual totals from the three locations for each type of product. Consolidate the sales data from the three locations. Use a function to total the Mini sales values in Q1 from the U.S., Canada, and Mexico worksheets. Use the same formula to consolidate and fill the remaining product totals in the table through Q4. Benicio started to define names for cells and ranges in the All Locations worksheet to make it easy to identify the total sales for each product. He wants you to add a defined name for the Waterproof sales amounts and then find the total annual sales for each product.Create a defined name for the Waterproof sales amounts from Q1 through Q4 using Waterproof_Total as the name.Format the PivotTable to make it easier for Benicio and others to interpret.Change the number format of the value field to the Currency number format with 0 decimal places and the $ symbol. Use Sales (000s) as the custom name of the value field, and in the appropriate cells, use Country to replace the text "Row Labels", and Channel to replace the text "Column Labels".Go to the Suppliers worksheet, where Benicio wants to determine whether subcontracting could reduce the cost of the new virtual assistant product. Run Solver to solve this problem. Set the objective as minimizing the total cost, and use the units produced values as the changing variable cells. Adjust the number of units produced by each supplier using the following constraints:Set the total number of virtual assistants produced as greater than or equal to 11,000, DIG Technology's minimum production goal.Set the total cost to be less than or equal to 925,000, the maximum total cost DIG Technology wants to spend.Set the number of virtual assistant units produced by each supplier (range B5:D5) to be less than or equal to 4000 to balance the production among the suppliers. Also, make sure that this same range contains integers since DIG Technology cannot sell a fraction of a product. b.Use a function to display the total of the sales amounts in the Mini_Total range in the Total column. Use the same function to total the Voice_Activated_Total range and the Waterproof_Total range in the appropriate cells. 4.Benicio wants to compare the sales of each product in 2021 with the sales in 2020. He has the 2020 sales data stored in a separate workbook. Add the 2020 sales data to the All Locations worksheet. Use a reference to pull in the total sales of Mini products in 2020 from the Support_EX19_CT_CS5-8c_2020.xlsx workbook. Make sure the formula always references the same column but not the same row, and then fill the rest of the 2020 totals with the formula, but not the formatting. 5.In the appropriate cell, use a function to look up the total sales for Q1. Retrieve the performance rating from the table showing the revenue amounts and ratings. The formula should always use the exact range and find an approximate match. Use the formula to display the performance ratings for the rest of the quarters. 6.Benicio listed information about the managers of the DIG Technology main offices in the U.S., Canada, and Mexico. He needs to add a link to the email address of the U.S. manager. In the appropriate cell, create a link to Todd Fenton's email address without changing the display text. 7.Benicio also wants to make it easy to access more detailed information about the managers, which he has stored in a Word document. In the cell containing the text "Management Details", create a link to the Word document Support_EX19_CT_CS5-8c_Management.docx without changing the display text. Use Access manager details as the ScreenTip text. 8.Go to the Current Sales worksheet, which contains a table listing sales data for January, 2022. Benicio wants to use the table data in formulas. To make it easy to refer to the data, rename the table "Monthly Sales: January 2022" using Sales as the name. 9.In the column labeled "Promotion?", Benicio wants to indicate whether DIG Technology should send the customer a promotional offer. Customers are eligible for the offer if they purchased a Mini 2 in the U.S. Use a function and structured references to determine if the first customer is eligible. (Hint: The formula will automatically return TRUE when the conditions are met, or else FALSE.) Fill the formula to the rest of the column, if necessary. 10.The Sales table is currently sorted by the values in the Sale ID column, but Benicio wants to sort the table by date, then amount to make it easier to track the data. Sort the Sales table first by date from oldest to newest, and then by amount in ascending order. 11.Benicio wants to make sure that the Sales table does not contain any duplicate records, which would make any sales analysis incorrect. Use conditional formatting to identify any duplicate sales ID numbers, and highlight them using Light Red Fill with Dark Red Text. Delete the second duplicate record from the table. (Hint: Do not delete the row from the worksheet.) 12.Benicio might want to filter the Product Details data, so he asks you to format it as a table. Format the data under the cell containing "Product Details" as a table using Light Gray, Table Style Medium 14 to match the Sales table. 13.Benicio wants to examine the January, 2022 sales by country and channel. On a new worksheet, insert a PivotTable based on the data in the Sales table, and use January Pivot as the name of the worksheet. Use Location as the rows, Channel type as the columns, and sum the Amount values. b.Change the PivotTable style to White, Pivot Style Medium 14 to coordinate with the tables on the Current Sales worksheet. 15.Benicio wants to isolate sales for each product. Insert a slicer based on the Product field. Move and resize the slicer so that it covers the range F3:G12. 16.Benicio also wants to compare the sales data by country in a visual format, and then display data only for the Mini 1, the company's best-selling product. Insert a Stacked Column PivotChart based on the data in the PivotTable. Move and resize the PivotChart so that it covers the range A10:E25. 17.Change the colors of the PivotChart to Monochromatic Palette 6 to coordinate with the PivotTable. Use the slicer to display sales data for only the Mini 1 in the PivotTable and PivotChart. 18.Go to the Sales Projections worksheet, which provides a profit analysis of a new virtual assistant product DIG Technology is planning to develop. Benicio wants to make sure the total expense per unit sold calculation does not produce a divide by zero error. Add a function to the formula for the total expense per unit sold to display the message Divide total expenses by units manufactured in case of an error. 19.Benicio has already calculated that the gross profit for the new product could be nearly $295,000 based on a unit price of $159.99 and unit sales of 22,000. He wants to see how the gross profit changes if the price or unit sales are different. Under the cell labeled Virtual Assistant: Gross Profit Analysis, create a two-variable data table using the price per unit and the units sold as the row and column input cells. d.Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model in the range A14:A21. 21.Run Solver again, create an Answer report, and then close the Solver Parameters dialog box. 22.Go to the Product Mix worksheet, which calculates the profit from each model of portable speaker. Benicio wants to compare three scenarios: one with the current prices and costs, another with the prices raised $10.00, and a third that reduces the variable costs per unit by $5.00. He has already created the first two scenarios and defined names for cells and ranges. Create a third scenario using Reduced Costs as the name. Use the same changing cells as the other two scenarios. Reduce each variable cost per unit value by $5.00. Create a Scenario Summary report that summarizes the effect of the three scenarios. Use the profit per unit sold from Mini 1 to Waterproof as the result cells. 23.Return to the Product Mix worksheet. Benicio wants to provide a visual way to compare the scenarios. Use the Scenario Manager to create a Scenario PivotTable report using the profit per unit sold from Mini 1 to Waterproof as the result cells. Remove the Filter field from the PivotTable, and change the number format of the value fields to Currency with 2 decimal places and the $ symbol. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. The Answer Report 1 worksheet and Scenario Summary worksheet have intentionally not been shown.
