Guidelines: Open the spreadsheet titled, “LOGICAL FUNCTIONS” Using the SUM function, compute all totals for column B through H Using the AVERAGE function, compute all AVERAGES for column B through H Computer the BONUS column using the IF function:All Reps get a 10% (0.1) BONUS IF their QUARTER sales are GREATER THAN the QUOTA value. If the Quarter sales are NOT GREATER THAN the QUOTE, there should be no BONUS added to the TOTAL (Column H). This is the function/formula to use: =IF(E8>F8,E8*0.1,0) Compute the TOTAL column:=E8*0.1+G8 Add a CLUSTERED COLUMN CHART with a Chart title (Claremont Sporting Goods – 1st Quarter Sales) When you have completed all computations, use the FILTER feature to SORT by Rep Name (A to Z) Merge and Center the Titles across the worksheet (from A through I) Add a HEADER that has your NAME in the TOP RIGHT CORNER RENAME the worksheet Sales: Qtr1 Save and Upload

Discussion Topic: Reflection on the Course As you conclude the course, it is important that you reflect on your learning, as this reflection is an enhancement of learning and comprehension of material. Reflect to show how this course can enhance your ability to function as a professional, and be sure that this discussion includes material from the current module. Also response each posted #1 to 3 down below Posted 1I can honestly say that I have learned quite a bit in this class even after having been involved in the auditing process with respect to several public companies. I have always been a tax professional so I was only involved in the audit of the tax provision which may have taken one day. I saw a small piece of all of the fieldwork actually being done. Auditors do a lot and have to know a lot. They also take on a lot of risk when they audit a client and have so many things to consider (the client's industry, the client's business, the integrity of management, the client's internal controls, the client's financial reporting process, etc.) and they even have to re-perform tasks and observe tasks being performed. They also have to consider not only the audit time frame but also subsequent events and the disclosures related to such events. They also have to be on the constant look out for fraud. I especially enjoyed reading all of the issues and cases in the Contemporary Auditing book. Some of it was unbelievable - from fake businesses, to fraudulent journal entries, to embezzlement. If not for auditors, a company could represent anything in its financial statements. That professional assurance is needed for investors, creditors and the general public. I gained a greater awareness of why SOX was needed especially after the failures and fraud at companies like Worldcom and Enron but still miss my first employer, Andersen. his class also helped me confirm that I made the right career choice to become a tax professional but opened my eyes to other opportunities as an entrepreneur. I own a licensed CPA firm but mostly do consulting and tax work. I have been approached by potential clients regarding compilation engagements, and now feel comfortable that I can provide that service since it doesn't require auditing or providing assurance about the financials. I also feel more knowledgeable about reviewing a company's financials. I've recently gotten into stock options trading, and now I can review a company's filings and analyze what's in those reports using financial ratios and other analytical tools I've learned in this class to be a better investor. I serve on the board of an entity that requires an audit, and now I feel a lot more confident in my ability to ask relevant questions about the audit, have an intelligent discussion with our external auditor, and understand our internal controls and what might be lacking. I also understand how to write and review the audit and attestation letters and what should be included in the CPA letter provided at the conclusion of an audit, agreed upon procedures, a compilation and a review. Though this class has been challenging and outside of my comfort zone, I have really enjoyed it. I don't know how I passed the audit portion of the CPA exam but can now say I truly have an understanding of what an auditor does and the profession itself which makes me a better CPA. Posted 2 Hello Professor and Class, As I continue to take classes at the University, I try to relate what I do at work with valuable educational information that is like my job. I am not an internal auditor, but I do a lot of auditing for quality and quantity. We warehouse parts for a production facility, and they must be audited by our team and then we pass the information onto management at the facility. I audit parts as they leave our warehouse and get loaded onto trailers to go over to the plant. So, I do like to consider myself as an internal auditor for the company I work for. Who knows, the facilities accountant may be asking management at the facility to make sure we are doing counts on inventory in our warehouse. What I would like to do at the end of the year is look at the facilities annual statement. I have the annual statement from the first quarter of the year for 2020. When I get the last quarter of this year, I would like to look over the financial details and do some analytical evaluations on the 2. I want to see what is going on with the financial side of the company I do not get to see. What I want to see is how much money the company is making, their profit, revenue, assets, and many more accounts that are reported on their annual report. Then I would like to take what I learned in this class and show the employees I work with everyday the interesting information that makes up the financial information they never see or have no idea that it exists to begin with. They might like to know how the facilities assets and profit stand up to other company’s in the market. I could perform some ratios and let them know whether the facility is doing good or is just making it by with their shoelaces untied. It has been a difficult year for all markets, I would like to see how this last quarter stacks up to maybe the last quarter from 2019. The COVID-19 pandemic has made a mess of just about every corner of the economy, not many made it out untouched. I want to compare annuals and find out what parts of the company were affected, or which accounts took big hits. Posted 3 Reading articles about past fraud cases during this course has opened me eyes to how obvious fraud might be, but how easily it can also slip through for many reasons. It is important to follow standard auditing processes even when you think you can slide something through that does not come off as fraudulent. Spending the time, using the detail, and following the standard guidelines is necessary for every situation, and is not to be overlooked just because something or someone does not come off as a concern during an audit. This concept is true for many examples in my work life. It is always so important to not assume anything, but to do the detailed work. I am always surprised to find how many mistakes I make when I double check.

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.