Please answer the questions below:

John Sousa is an accounting/finance major who is interning for the summer at a small, national computer manufacturing company. He has been provided with a workbook of three Excel tables and asked to analyze the following questions for the Sales Manager of the company. - What was the total sales and prots for the company across all regions relative to the data provided? - How do these results \"break down\" by each region? What would be a good way to display these results to the regional sales managers that would be easy to understand? - There is a need to evaluate the eectiveness of each salesperson. For the data available, prepare a ranking of sales by salesperson and also show the commission due for each salesperson so that the manager can evaluate the distribution of bonuses. - Most importantly, one question that has been on the Sales Manager's mind is whether salespeople who have more experience actually sell more? John has been asked how to analyze this topic in an efcient manner and then provide his ndings and support thereto. The company is considering a new program that will pair senior sales executives with junior sales team members on a rotational basis. The program is costly, so insight into what types of benefit might be anticipated from such an e'ort are important to management's \"go\" or \"no go\" decision. Lastly, there is little room for errors in John's analysis due to the timing of the request and the fact that Shelby will be presenting to a group of people who have a low tolerance for mistakes. She has asked that John think about inherent quality control. While, in the past, she has indicated that this might include a printout of cell reference sheets (e.g., with color coding to indicate any cells that are \"hardcoded) she much prefers that everything be formula based...and she has always encouraged the use of hidden columns or rows to show that everything \"foots\" correctly. These are items she has mentioned in passing, but is always open to additional ideas when it comes to improving the quality of the work. Her expectation is that John will provide not only the answers and exhibits germane to the above questions, but also will be able to provide a one to two page "write-up" that explains his analysis {i.e., what Excel technique you selected to answer the various questions as well as the results) and what conclusions he has reached from his analysis of whether experience seems to be a signicant explanatory variable to relative performance of individual sales executives. John's deliverable is expected to include a paragraph, or so, that addresses the proper (1) documentation, [2) design thinking, and (3}, quality control considerations above as she would like to see your thought processes to make sure John gave due consideration to each. As John's hope as an intern is to make a good impression to potentially secure a full time offer upon graduation, John is looking to at least meet if not exceed Shelby's expectations. The expectation of the Sales Manager, Shelby Mills, is that John will use Excel to undertake the analysis and that, as a junior level student at a well-respected university; he is experienced with not only basic Excel skills, but also skills such as V-lookups and pivot tables. Shelby is also expecting the work product to be well "Documented\". As the company often uses historical analysis of this type for comparative - and other purposes - over time, she is concerned that once John's internship ends he will not be accessible to answer questions about how the spreadsheets and exhibits were prepared. Accordingly, it is necessary that John prepares his work product in such a way that next summer's intern (or others) can pick up John's work and clearly understand what John did (i.e., underlying assumptions, use of clear titlesotes/footnotes, etc.). John has been told that he is free to work with another intern (and/or have that intern check your work) on this request, but John's exhibits should seem like they were prepared by one person (i.e., a consistent format). She has also asked John to really think about the layout of his exhibits and summary sheets relative to how she will need to present this information to the regional sales teams. In additional to accurate and interesting insights, these folks really value clarity and they like to get to the point quickly. Ideally, John's results are provided in an Excel workbook that can be emailed to her due to her travel schedule. John can anticipate that she may ask why his layout was designed a certain way. That is to say, she always believes that design thinking (i.e., putting oneself in the \"shoes\" of the reader/decision maker) is important when it comes to spreadsheets, and that execution should follow only after a well thought out design is developed. Salesperson ID Product Sold ID Quantity Sold Region Sold In 103 132 24 North 11b 2:3 52 South 12:: 4:15 12 East 13d 6f7 25 West 14e 8f9 34 East 15f 2:3 36 West 163 2:3 42 South 17h 4d5 12 East 18i 132 15 West 19] 2:3 62 South 20k 4d5 12 North 103 132 36 East 11b 2:3 45 South 12c 4d5 43 West 13d 132 23 East 14e 6f7 6 South 15f 8f9 23 North 163 4d5 53 North 17h 8f9 13 South 18i 6f7 34 South 19] 4d5 54 South 20k 8f9 18 West 103 2:3 19 East 103 6f7 38 South 11b 4d5 27 South 11b 8f9 10 North 11b 132 26 East 15f 4d5 16 North 15f 6f7 27 East 15f 4d5 63 South 17h 4d5 12 West 19] 132 27 North 18i 8f9 25 East 18i 18i 163 163 11b 11b 11b 14e 14e 163 18i 10a 163 20k 15f 17h 163 4d5 3f9 1a2 2C3 1a2 4d5 3f9 2C3 6f? 4d5 3f9 1a2 4d5 3f9 4d5 1a2 3f9 16 North 26 North 23 East 54 South 52 North 12 East 53 North 19 West 53 South 12 North 26 East 37 North 43 South 16 North 17 West 12 West 14 North Salesperson ID First Name Last Name Years at Company Commission Rate 10a Tom Smith 4 5% 11b Jane Anderson 7% 12c Emily Zhang 3 5% 13d Jim Jones 19 10% 14e Bob Wade 13 15% 15f Mary Murphy 17 15% 16g Rob Mcdonald 2 5% 17h Will House 7% 18i Anna Birch 10 10% 19j Taylor Chang 3 5% 20k Justin Lewis 7%Product ID Product Name List Price Cost 1a2 Desktop Computer un 499.00 S 231.00 2c3 Laptop S 999.00 S 357.00 4d5 Phone 699.00 253.00 6f7 Tablet 399.00 $ 142.00 8f9 Projector 799.00 $ 598.00