This is Part 5 of the Global Bicycle Inc. (GBI 2.0) story, as related by an intern. The full series begins with Global Bicycle Inc.: An Intern Adventure.
A Slight Issue, Random Items, & Financial Statements
It was at this point in time that we had the entire master data figured out, or figured out as much as master data can be figured out without actually entering it into the system. We had not yet received access to a client to begin testing, so in the brief interlude, we moved on to other tasks. It was during this time that Dr. Magal stopped by one day. “Corey” he begins, “we have a slight issue.” “Oh boy,” I thought. It’s never good when your supervisor tells you you have an issue, even a slight one. “The Professional Touring Bike,” he begins, “you have listed here for a selling price of $3000. GBI has a 50% mark up, so reduce $3000 by 50%, and we arrive at a standard price of $1500 for the bike. Now half the standard price is raw material cost, and the other half is for labor and overhead. Do you follow?” Sure I followed, where he was going with this though, I didn’t know. “Yes I follow” I replied. “However,” he went on, “if you add up all of the cost of the Raw Materials needed to produce this bike, you arrive at about $5000. That’s more than GBI sells the bike for. We lose money on each bike.” Oh, so that’s where he was going with it. “Well,” I replied, “that certainly could be a problem.” It might only be a training environment, but it still had to be realistic.
Soon I figured out what the issue was. When I had done initial research for the materials, I went out to the web and found prices for all of the materials. These prices, however, were retail prices. They were the prices you and I would pay as consumers if we just went out and purchased the one component for our bike. They were not the prices, say, a national company would pay if they were purchasing in bulk. I opened up Excel, began another spreadsheet, and started to reconcile prices. If I was doing research for an actual company, I would have been calling or e-mailing all of these various companies to get actual prices. Since this was a fictional training environment I had a bit of leeway. I began to reduce the prices of all of the materials until it began to make more sense and all of the numbers crunched. Since all of my previous master data spreadsheets had the old prices on them, I then had to go through and update all of the prices on those spreadsheets. There’s nothing like data integrity to brighten up your day.
It was also during this time that we got to work on some “presentation” materials. I had found a website that allowed you to input a series of addresses, and it would show those addresses on a map. I input all of GBI’s customer addresses and adjusted the map until the entire continental United States was visible. Suddenly, it was put into great perspective exactly where they are all located. I printed it to a pdf, and viola, a great visual aide. I repeated the process for the Vendor’s, and for the three GBI plants.
A link was forwarded to us from a member of the GBI Steering Committee of a website that we utilized to create bicycle diagrams. The diagrams showed the main basic parts of the bicycle: the handle bar, the fame, the wheel, etc. These would be used to help show the students exactly what the various parts were. That those material numbers they kept seeing went to actual parts, a physical tangible object, not just an abstract set of letters and numbers. The diagrams we created were without a doubt preliminary. They were, however, the foundation of later diagrams.
We had to create a diagram of the Dallas plant layout. Where the office was located, where the assembly lines were, where shipping and receiving was, the various parts of the assembly lines, etc. To be able to do this, we first had to learn how exactly a bicycle was produced. What steps were needed to take the raw materials, put them together, and end up with a fully functioning bicycle. To do this, we went back to the internet. We found a YouTube video from “How Its Made” (Season 1, Episode 6, Part 3) that detailed how exactly this was done. We took notes and used the various steps in the video to allocated space and equipment in the plant layout.
It was also at this time that we were given a preliminary list of all of the employees GBI US would have. This list included their names, plant they worked at, and occupation. We had to determine their salary. Once again, we went back out to Google. We ended up locating a website that if the position was entered, it would tell us the average salary. This information went into yet another spreadsheet. In later times, we would determine that we would need some more employees. At that point, we got to create the names for these new employees. To keep things completely random (and realistic), we used a name generator we located online to help expecting parents determine possible names for the soon-to-be-born child. We then gave them their respective salary. As we went about the process, we had to tweak salaries here and there. Give this position a raise, give that position a drop, etc. until our numbers crunched correctly. It was also at this point that we had to organize all of these employees into a hierarchy chart. Various forms were tried and used (Word, Excel, Visio) until we had a readable, functioning organization chart.
The final item we got started on during this period was a Balance Sheet / Income Statement for GBI for the year 2009. It was understood that GBI went SAP live on January 1, 2010. The thought was that the B.S/I.S would be created for the year 2009, and then somehow entered into the system. This way, there would already be some accounting data in the system for Professors and students to look at. I, the non-accountant, chose this project over creating the organizational structure and salaries (I gave that one to Kevin Coolman). Why did I choose the B.S./I.S.? I thought it would be the easier project. I was wrong.
It began simple enough. I started by what was now common practice- I went back to SSB and took a look at the financial statements created for that. They had 15 line items for the I.S. and 16 line items for the B.S. I opened a new Excel sheet, copied the format, and utilized the same line items. The easy part ended there.
I had a tab to figure out each of the following: sales per month, salaries, production schedule, inventory, purchases and accounts payable, property plant & expenses, and other expenses. Salaries was easy, I got that data from Kevin. Some line items I simply made as a percentage of total sales or expenses or some such thing. I figured out these percentages by looking at the SSB financial statements.
The real fun began when I got to the production schedule. It was determined by the GBI Steering Committee that in one eight hour work day either 15 bikes could be produced, or 50 wheels and 10 bikes could be produced. It was determined that one wheel assembly could be produced in 3.6 minutes, and one bike produced in 30 minutes, and thus it was extrapolated out to an eight hour work day. Using this information, I created a production schedule for the year 2009. I took into account information such as the exact number of days for each month, not working on Saturday and Sunday, and that bikes cannot be produced without wheels, etc.. This allowed the exact number of bikes that could be produced on one assembly line to be determined.
This allowed me to determine how much inventory would have to be purchased, if the sales targets had to be adjusted, etc. A “purchasing and accounts payable” tab was created where I attempted to determine how much would have to be spent in raw materials to meet the production schedule. It was decided that 50% of purchases would be paid in the month of the purchase and 50% would be paid the next month. This information was taken into account.
A PP&E tab was created were I calculated the expense of paying for the property and the equipment. I went out to Google and searched for an actual factory for sale in the Dallas area to get its listing price. I did the same thing for Miami and San Diego. I then estimated the amount that would have to be spent on equipment. I added all these up, divided by 12 and I had the PP&E number. I did not take into account interest, or that fact that in real life a $27 million dollar factory would not be paid off in one year.
Finally, I had to determine the “Other Expenses.” Electric, water, natural gas, and “other.” I know how big the house that I own is, so I figured out what I paid per square foot. Then I increased that rate by a bit to account for residential vs commercial, and multiplied by the square foot of the factory. Viola.
This was an item that was worked on over a period of several months. After a while I handed it off to another intern who was actually in accounting. He opened up the excel sheet, took one look at it and said, “This is intense.” He worked on it for quite a while, and in his quest to make it as accurate and realistic as possible ended up making it much more complicated that I had. Unfortunately our work on this matter came to nothing as of yet (to my knowledge); it got lost in the shuffle of the various to-do lists. In the future perhaps. Lesson learned: its’ much easier to do this kind of thing with actual data, than it is to try and make it up. Indeed, at one point we actually considered sitting down and generating all of the necessary purchase orders, production orders, and journal entries to try to get the financial statement. In the end, we decided that was more work than what we were doing.
If any of these presentation materials are currently being utilized is beyond my knowledge. If they are or not is beside the point. At the time they were relevant and helpful, and as always, an exceptional learning experience.
