Assignment 1

Getting Started

Visit the spreadsheet at this link and make your own copy, as via File > Make a copy or by via File > Download as > Microsoft Excel.

Using the YouTunes data, solve the following business problems. The answers should be expressed as formulae in the spreadsheets; highlight the cells containing your answer(s) in yellow.

Then, in the submission form, on the space provided for each question, briefly explain how you solved the problem (or, if you got stuck, explain where you got stuck and what you wanted to do).

Question 1*

You’ve recently determined how much it costs you to fulfill an order. Now it’s time to calculate your average invoice amount. In the Invoices sheet, add a new column between columns H and I and fill it in with the total value of each invoice. Then, compute the average value of all the invoices.

Question 2

You are considering establishing a customer loyalty program; identify your top 5 customers (in terms of total revenue from their invoices).

Question 3

Part 1

It’s time to allocate all your sales employees to customers. Let’s begin by assigning each member of the sales team to some customers. We’d like to distribute them equally among the team, so we’ll do this alphabetically. First, in cell A15 of the Employees sheet, compute the number of employees whose Titles place them in the sales organization. (No fair just counting – solve this as if you had thousands of employees.) Give this cell the name SalesReps.

Part 2

Next, we need to figure out how to evenly partition our customers alphabetically. Sort the Customers sheet alphabetically by company and by last name if there is no company.

Part 3

Create a cell named NCustomers and set the value equal to the number of Customers on the sheet.

Part 4

Add a column to the Customers sheet, between columns I and J (between the PostalCode and Phone number). Label that column SalesRep. Now, using the same formula in every cell in the column, assign representatives to customers so that Nancy gets the first quarter of the customers, Jane gets the next quarter, Margaret gets the next quarter and Steve gets the last quarter. Remember, each cell must contain the same formula!

Question 4

Add a column, E, to the Songs sheet that includes the price of the song. Hint: This is not simple to do in a spreadsheet so remember if you have spent over 20-30 minutes on the problem, stop and just write a short paragraph explaining what you tried and conceptually, what you need to do to answer the query.