Excel

MS Excel for Managers

Case Study

Gachibowli Sales Corporation Gachibowli Sales Corporation (GSC) is a global sales agency that specializes in high-end electronic goods. It operates in India, China, USA and UK. GSC has an army of Sales Executives (currently 40 in number), each assigned to one country. The number of executives in a country depends upon its market size. Sales Executives have a performance-linked compensation and are often referred to by a Sales Executive Code to maintain anonymity. The executives are also assigned an annual target at the beginning of the year that they have to meet. Failure to do so may result in a penalty (though the management may be a little flexible in its rules). The sales orders (referred to in the company as Transactions) are recorded on a daily basis. Every transaction has a unique OrderID, the Sales Executive Code, transaction Date and the Order Value. As the manager of GSC, you head the sales team, monitoring the regional sales and performance of each executive. For the lack of a better option, you decide to use Microsoft Excel for most of the analysis (☺). You will also build a Sales Entry application and a Dashboard, monitoring the performance of GSC and its employees.

Instructions 1. Go to http://workshops.theexcelpedia.com/pgp-msexcel or http://tiny.cc/lw5oq. 2. Click on the Get Started link to go to the course page. 3. Download Problems.zip to your laptop. 4. Extract the contents to C:\MSExcel.

Page 1 of 5

MS Excel for Managers

Problems

Problems I. Formulae & Functions 1. Modify the Sales Worksheet to show the Name of the Sales Executive and Country along with the other order details. 2. Find if E05 and E07 have made any transactions in the year. 3. Given Avg. Order Value and No. of Orders by Country, find Total Sales 4. Find total sales by E05 5. Count the number of transactions made by employee E05. 6. Total sales by employee E05. 7. Assign a code ‘P’ to a transaction if Order Value > $200 else...