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 as follows:
a. Group the U.S., Canada, and Mexico worksheets.
b. In cell F5, insert a formula using the SUM function that totals the Mini sales amounts for Quarters 1–4 (range B5:E5).
c. Fill the range F6:F7 with the formula in cell F5 to display the totals for the other types of portable speakers.
d. Ungroup the worksheets and then check to confirm that all three worksheets reflect the changes you made in this step.
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 as follows:
a. In cell B5, enter a formula using the SUM function and 3-D references that totals the Mini sales values (cell B5) in Quarter 1 from the U.S., Canada, and Mexico worksheets.
b. Fill the range C5:E5 with the formula in cell B5 to total the Mini sales for Quarters 2–4.
c. Fill the range B6:E7 with the formulas in the range B5:E5 to total the sales for the other products in Quarters 1–4.
3. 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 and use defined names as follows:
a. Create a defined name for the Waterproof sales amounts (range B7:E7) using Waterproof_Total as the name.
b. In cell F5, enter a formula using the SUM function to display the total of the sales amounts in the Mini_Total range.
c. In cell F6, enter a formula using the SUM function to display the total of the sales amounts in the Voice_Activated_Total range.
d. In cell F7, enter a formula using the SUM function to display the total of the sales amounts in the Waterproof_Total range.
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 as follows:
a. Open the workbook Support_EX19_CS5-8a_2020.xlsx.
b. Return to the All Locations worksheet in the original workbook.
c. In cell G5, enter a formula using an external reference to display the total sales of Mini products in 2020 (cell F5).
d. In the formula in cell G5, change the absolute reference to a mixed reference, with a relative reference to the row number.
e. Fill the range G6:G8 with the formula in cell G5, filling without formatting.
f. Close the workbook Support_EX19_CS5-8a_2020.xlsx.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
