Forum Discussion

vwinston's avatar
vwinston
Copper Contributor
Jun 02, 2020

re-organize the first column to create separate columns

Hello, I am trying to reorganize a list of data, to create separate columns, so that I can calculate total sales per type of item, total amount owed. I'm attempting to use the VLookup function or Ind...
excel
Excel on Mac
Formulas and Functions
vwinston's avatar
vwinston
Copper Contributor
Jun 02, 2020

 

Hello, thank you for responding. I'm a Career Tech teacher, and I'm working on a few projects to teach myself how to use Excel. Due to teacher shortages, I may be asked to teach these functions in the Fall. I'm new to using Excel to solve problems, and need some help. I'm working on two different problem sets today.
 
Problem 1:
 
Troop 137 from Girl Scouts of North Central Alabama has been selling Girl Scout cookies. As the troop leader hires you help analyze her data from last year’s sale. Access data in the spreadsheet tabs “Cookie Cost Data” and “Cookie Sale Data” and do the following tasks: 
 
1.  Under Part 1-Q1 tab, re-organize the first column to create separate columns to represent Girl Scout Type (Daisy, Brownie, etc.), Girl Scout Name (Daisy #1, Daisy #2, …., Brownie #1,…, etc.), and Order number (Order 1, Order 2, …, etc.) Your final product should look similar to the first four columns of Part 1- Q2, highlighted in yellow. 
 
2. The troop leader is interested in total Girl Scout cookie sales per Girl Scout. Using the data under Part 1-Q2 tab, 
a. Calculate the total boxes of each cookie type by each girl scout; 
b. Calculate the total boxes of each cookie type by each girl scout type (e.g., total daisy sales, total brownie sales, etc); 
 
3. The troop leader pays the council for the total amount of boxes her troop sells. 
 
a. Using the “Cookie cost data,” calculate the total amount of money the troop owes the council under Part 1-Q3. 
b. If the troop sells less than 200 boxes per Girl Scout (on average), then troop gets a pay out of 55 cents per box sold from the council. Otherwise, if the average number of boxes per Girl Scout is larger than 200 boxes, then the troop makes 85 cents per box sold. How much will the troop make from the total sales? 
 
4. Each Girl Scout earns rewards for making sales. If a Girl Scout sells more than 1000 boxes, she earns a GS sleeping bag set; if she sells more than 500 boxes, she earns a GS umbrella; if she sells more than 100 boxes, she earns a GS t-shirt; and finally, if she sells any amount, she earns a GS cookie badge. Using the VLOOKUP function, 
 
a. Calculate the reward for each Girl Scout under Part 1-Q4 tab of the spreadsheet. 
b. How many t-shirts and umbrellas should the troop leader receive? 
 
Problem 2:
A Paintball Supply (BPS) manufactures paintballs at its facility in Texas and sells them for $8.00 per case. There is a big surge in demand for paintballs during the holiday season and BPS has the capacity to produce and store up to 10,000 cases of paintballs for the holiday sales. BPS has a fixed cost of $10,000 and spends $5.00 in variable cost for each case it produces. BPS also has an outsource supplier that can provide up to 5,000 cases at a cost of $6.50 per case. The holiday sales are uncertain, but BPS expects to receive orders for between 9,000 to 17,000 cases this season. BPS must give a fixed order quantity to its supplier at the beginning of the season and then any additional orders are filled from its own internal production. 
 
1. Build a spreadsheet model for this problem and as a base case assume that BPS orders 2,000 cases from its supplier and receives orders from its customers for 15,000 cases. 
 
2. Given that the holiday sales are uncertain, it is a wise idea to create a best case and worst case scenario and compare their profitability. Consider the following best case scenario with a demand of 17,000 units and assume that BPS orders 4,000 units. Also, consider the worst case scenario with a demand of 9,000 units and assume that BPS again orders 1,000 units. What is the range of profit across these case? Please use a scenario manager to evaluate these cases. 
 
I would really appreciate any guidance in solving these problems,
 
Thank you!
VWinston
 

 

mathetes 

mathetes's avatar
mathetes
Gold Contributor
Jun 02, 2020

vwinston 

 

Valencia -- I'm, frankly, conflicted here when it comes to helping you. Why? Because you'd learn a lot more by struggling through figuring out how to resolve these on your own. I'll make a couple of recommendations or suggestions, but it'd be a lot better if you made some more efforts at figuring out your own solutions.

 

You mentioned in your first message that "The Text Manager is not giving me the columns in the way that I have them set up." I'm not even sure what you're referring to as "the Text Manager"--excel Help does not return any results from that term. There ARE text manipulation functions and I've used a few of the basic ones here in the attached revision of your sheet, to resolve the very first of the problems. These are the functions LEFT, MID, and RIGHT.

 

These all operate on Cell A4, which contains the text: "Daisy Girl #1 O# 1"

 
  • LEFT will get text beginning at the left end of a string, going for a specified number of characters. In this case, I used FIND (and in others) to determine where to stop, the answer to which is, stop when you come to a space. The resulting formula looks like this.
    • =LEFT(A4,FIND(" ",A4)-1)
  • RIGHT is similar, expect it begins at the right end of the string. I "cheated" in this case, because I could tell by looking at your data that the Order Number, which is what we want, is never more than one digit in length, so I could simply say, in effect, "Get the one character at the right end of the cell's string."
    • =RIGHT(A4,1)
  • The most complicated formula is the one to get text out of the middle of the string, the name of the scout, and it consists, in effect, if two words, "Girl #1" in this case. So the formula has to tell Excel where to start and where to stop. It starts right after the first space, and counts the number of spaces between that first space and the occurrence of the letter "O" which is the beginning of "O#" or, in some of the rows, the word "Order"
  • This formula does work for all of data elements in your exercise, but if the data were more inconsistent, it would have to be made more sophisticated. If, for example, there were real names in there, and there was a girl (or many) with names that began with or contained the letter "O"
  • Anyway, it works for this, so we didn't need to get more fancy:
    • =MID(A4,FIND(" ",A4)+1,FIND("O",A4)-FIND(" ",A4)-2)

 

Having done this, I will tell you that VLOOKUP should work for the rest of the columns, where you're supposed to find the number of different cookies each girl sold.

 

And if you're really interested in teaching yourself, I'd highly recommend a book by Smitty Smith (a Microsoftie), a book entitled Excel 2019, Business Basics & Beyond. It's available on Amazon.

https://www.amazon.com/Excel-2019-Business-Basics-Beyond/dp/1615470611/ref=sr_1_1?dchild=1&keywords=%22Excel+2019+business+basics+%26+beyond%22&qid=1591135091&sr=8-1

Not all books on Excel are easy to read; this one is a good and readable introduction to the kinds of problems you've been given.

 

As I said, though, if you make a few stabs and find yourself up against it, by all means come back and post questions. It's just that the more you hang in and struggle with it, the more you'll actually be able to understand and explain it. Having somebody else do it for you is helpful in some ways, but not the best way to learn.

Resources