1-25-2016: Reflection on My Experience

This blog post accounts for both today and last Friday. I have spent both days finalizing my model and working on my presentation.

I have planned my presentation to list each of the steps of my experience in my independent study, from learning about Microsoft Excel, to interviewing local business professionals, to the complicated building process. I will also be practicing my presentation today before the intensive fair begins.

Looking back at my independent study, I feel like I have accomplished a lot. I went from knowing nothing about finance to learning the basics. I learned more about business and leadership, and I learned different ways that business professionals use to make decisions for their companies. Things that I learned from my interviews with business professionals have been adapted into my model. I also learned a lot about Maumee Valley and realized there is an entire hidden side of the school in business, finance, and accounting that most people never see. I figured out the intricacies of building the model by myself, and went from knowing nothing about Microsoft Excel to having a pretty good grasp of it. I also appreciate all of the time and help that Kim Spurgeon, my sponsor, has given me over these past three weeks.

The idea for the independent study came from Mrs. Spurgeon. When I decided that I was going to take a leap of faith to do this independent study, I had no idea what I was getting myself into. I had no interest or prior experience in business or finance, but I knew that I loved Maumee Valley. I am happy to say that my independent study went well, and I learned a lot of things that I wouldn’t have otherwise learned had I not done this independent study. Although some parts of my independent study weren’t fun, there were many more parts of it that I enjoyed. I am a lot more interested in business and finance now, and I look forward to continuing to be involved in business and finance as a member of the Business Opportunities Task Force.

I am very happy with what I was able to do during my intensive independent study, and I am looking forward to showing you what I have learned at Intensive Fair, today at Maumee Valley, from 1:00 PM to 3:00 PM. Those who stop by my table will be able to actually play with my model! I appreciate the time that the readers have taken to read my blog, and I hope that all of you enjoyed the journey as much as I did!

1-21-2016: Formulas, Calculations, More Formulas, More Calculations

I spent another day working on the same thing as yesterday: the calculator for Tuition Revenue Generated from the ELC. I made huge strides with this today.

Mrs. Spurgeon took a look at the calculator that I worked on yesterday, and she gave me a lot of suggestions and ideas to take it further. I worked today to implement her suggestions and tried to figure out how to fix the mistakes that I had made yesterday. I also incorporated more ways for the calculator to still work properly even if users mistakes, by adding more formulas.

A lot of what I have said from the past several days is difficult to understand without actually seeing it. Therefore, I’ve made up some sample data so I can actually show you parts of my calculator.

Here is a picture of the calculator’s inner workings. I used Microsoft Excel’s Trace Precedents and Trace Dependents features, which show all data coming to and from each cell on my page. Each blue line represents “travel” of data to another cell, and the arrowheads indicate where the data is going. You can see how complicated it is from the inside based on all of the arrows you see and there are still more parts of it. At the top of the screen, you’ll also see the formula for one of my cells at the bottom of the screen, D151. This is what I’m talking about when I mentioned yesterday that the formulas in the calculator are long and difficult.

Here is a picture of the calculator with the same information, but from the perspective of the user. You can see that all of the fields that user fills in (the ones that are blue and purple) lead to another place (most of them go directly inside the calculator). Everything goes back to the cell that contains the answer (not shown) that is calculated completely.

The reason why the calculator is so complicated is because it allows flexibility for the user. The blue cells that you see in the user view of the calculator have drop-down menus that allow the user to select two or more options. This is good for me, not only because it limits the answers that the user can give to the model (thereby eliminating errors in input), but also because it allows the user to choose his or her preferred method of presenting information to the model. For example, the question in cell A11 asks the user which way he or she would like to present data to the model, and the user can choose to type in values for each subsequent year (if you will the picture more closely by clicking on it, it will make sense), or the user could choose to set an increase in enrollment of a certain number of students each year (and if the user were to select this option, different text would appear, prompting the user to input the number of students to add for each year). However, everything becomes more complicated when I build in flexibility, which is why by the end of the day, I still wasn’t finished working on the calculator.

I still have five more formulas to write before I can hopefully be done with it! Another day working on the calculator tomorrow!

1-20-2016: User-Friendly Interface, but Not User-Friendly for Me

Today, my assignment was to take what I had learned yesterday and try to figure out how to make a user-friendly interface for my model. I spent the whole day doing this, as it was a big leap into the unknown.

As stated in yesterday’s post, my goal for today was to figure out how to actually create the interface. No step of today’s building process was user-friendly for me. Every step required me to use data validation tools, advanced functions, and more complicated formulas to build what I wanted.

I spent most of the morning trying to figure out dependent drop-down menus. As discussed yesterday, these drop-down menus allow users to fill in an answer to a question. I had to do some research to find out how to do exactly as I wanted. Eventually, this made sense, and then I began combining these drop-down menus with formulas.

I also went searching for functions that I would need to use in conjunction with these drop-down menus. Although I went through a course on Excel formulas and functions, the online videos weren’t enough, and I had to do some searching on my own. I learned about some logical functions, such as AND, IF, NOT, and OR. I tried to see if any of these would help me in creating the user-friendly features; I eventually included IF and OR in some of my formulas. These functions are very helpful and can give the user different results depending on what information is inputted into the model.

I built the interface with a lot of trial and error. It wasn’t easy trying to jump into something that I didn’t know about (and something that even Mrs. Spurgeon didn’t completely know about). By the end of the day, I had finished a part of the new interface.

Writing the formulas was probably the most difficult part of today’s work. Microsoft Excel requires functions and their arguments to be inputted in only one form. I frequently ran into syntax errors, in which I would have my formula that I had thought through and worked hard on. I would then tell Microsoft Excel to calculate what I had written, but Excel would get nothing. I would then have to go back into the formula and find the one or two hard-to-find mistakes and fix them. But sometimes, I wasn’t able to find an error even though I was told that one existed. All of the time that I spent searching, experimenting, and writing formulas ended up leaving me frustrated by the end of the day. And after that, I realized in the late afternoon that I had made a mistake in one part of the new interface, and that I would need to figure out how to fix it tomorrow.

Since that’s really all I have to talk about for today, I thought I would do a quick reflection on my independent study goals.

In my proposal for my independent study advisor, I indicated that my goals were to do research on business opportunities and create the financial model for the Business Opportunities Task Force. I have done research on business opportunities and potential expansions. I have done this by talking to Mrs. Spurgeon, learning as I went, in addition to having met with business professionals in the Toledo area. I can prove I have done this from the blog posts I have written. My model is also completed, and the only thing that I’m doing now is taking a step further by attempting to create a user interface that is easier to use. Because I’ve already met all the goals as outlined in my proposal, we have decided that the last days of my independent study would be dedicated to cleaning and simplifying the model, and I have already begun to do this yesterday.

Enough for today; off to more work tomorrow…

1-19-2016: Model Building, Moving Forward

Today, I met with Mrs. Spurgeon on campus to discuss my model as it is and how to take it a step further.

My model right now makes sense, and anyone who knows basic finance and has background knowledge can easily use my model. But there’s an issue: the goal is for the model to be as user-friendly as possible, but what about it being easy enough for someone who has no basic knowledge about finance? For instance, I’ve learned a lot about finance since I began my independent study, and if I didn’t have the knowledge that I have now, I would have no idea how to use such a model that I have created. Today, we discussed how to make the model even easier to use than it is now.

We discussed two ways of doing this: either creating drop-down menus directly within the model, or creating a simplified table in which information can be added. I’ll use the example of revenue earned from tuition in an expansion to illustrate both methods of simplifying the model.

If I were to add a series of drop-down menus and fill-in boxes, the user would open the menu or fill in a numerical answer into a box to answer the question. For example, a question may ask, “how many students do you expect?” The user would fill in the box with the expected number of students. The next question would ask the expected change in number of students over a ten year period. A subsequent question may even ask the amount or percentage of tuition increase per year. After the user answers all of these questions, the model uses these answers, along with the average net tuition paid per student, to calculate the amount of tuition generated over the next ten years. This allows the user to do even less work in learning how to use the model. The end result for this is that the model would be able to easily calculate the amount of tuition revenue generated, just by answering these questions. This result gets inputted into the main portion of my model which calculates financial metrics and ratios.

The other option is to add another table to my model that would prompt users to fill in data for amount of expected students, percent increase in tuition, and the current net tuition per student. This is the same as doing what I described above, as all of the same questions are being asked and users are giving the model data; they are just two different, but equally good ways of doing this. Once again, the end result would be the ability to easily find the total amount of revenue earned from tuition, just by filling in a couple boxes with information.

I would be doing this so that the model can calculate other important information pertinent to an expansion, such as the cost of teachers’ salaries.

Besides talking to Mrs. Spurgeon about my model, I also spent some of my day cleaning it up and doing some more experimenting. There are some other parts of my model that need to be cleaned up and refined. Hopefully, I will be able to send a draft of my model to the Business Opportunities Task Force by the end of the week for feedback, so I can present them my completed, final draft model at the next meeting.

By the end of the day, I understood what I would be working on, and I will be figuring out the details of this tomorrow. I know the point of what I’m doing, but I will have to figure out how to work out how to actually create the drop-down boxes by myself. So this is my assignment for tomorrow!

1-15-2016: Mark Frasco, COACT

This afternoon, I met with Mark Frasco, president of COACT. COACT provides business consulting and marketing services to a wide variety of companies. Mr. Frasco is a parent of a Maumee Valley alumnus and a former member of Maumee Valley’s Board of Trustees. Mrs. Spurgeon thought that meeting with Mr. Frasco would be a good idea so that I could see the perspective of an entrepreneur and marketing professional in terms of looking at and evaluating business opportunities.

Of course, with each business professional whom I interviewed during this independent study, I asked which three financial metrics are most important to each person when considering a business opportunity. Mr. Frasco said that he primarily uses just two metrics, which are profit and amount of revenue per dollar spent.

The biggest thing that I took away from talking to Mr. Frasco was the importance of core values and mission statements of a company or an organization. When I asked him how he maintains the integrity of his company in business opportunities, he says that he always goes back to COACT’s mission statement. I could see how important this was to him because it was framed and hanging on the wall of his office. He showed it to me, saying that he is always thinking about the mission statement, from when he leaves for work every day, to making a deal, to making decisions. As a leader in his company, he said that if he and other company leaders truly believe the mission statement, others will follow their lead and also believe the mission statement. He said that as long as the leaders of an organization stick to their mission statement, everything will be OK and the organization’s integrity will be upheld. I will be thinking about this when attending future Business Opportunities Task Force meetings.

Mr. Frasco also showed me a model that has to do with overcoming change. His model is a simple equation:
DVF > R
R = resistance to change
D = shared dissatisfaction among leaders of the organization
V = shared vision among leaders of the organization
F = first steps towards overcoming a change
The idea is that one cannot overcome resistance to change without a shared dissatisfaction, a shared vision, and first steps toward making a change. All three parts of the left side of the equation must be present. If one of them is not there, an organization will never be able to overcome the resistance to change, even two of the other parts are, because a zero substituted for either D, V, or F makes the entire left side of the equation equal to zero.

All of my interviews with Toledo area business professionals were very interesting and left me thinking. I am looking forward to finishing my model next week!

1-15-2016: Fred Deichert, Midwest Terminals

This morning, I met with Fred Deichert, chief financial officer of Midwest Terminals. Midwest Terminals, founded in 1999, is a company based in the Toledo area that loads and unloads cargo. The transportation modes in which the company specializes are rail, vessel, truck, and pipeline. Mr. Deichert is a parent of two Maumee Valley alumni, a member of Maumee Valley’s Board of Trustees, and past president of the board. Mrs. Spurgeon thought it would be good if I met with him, as he is a successful business professional who is connected to Maumee Valley.

The first question I asked was which three financial metrics are most important to him when considering a business opportunity. For him, these are cash flow, cost of capital, and size of the project.

However, Mr. Deichert says that there are also nonfinancial aspects of a business opportunity to think about; his three most important ones are the proximity from home, the competencies needed to successfully deliver the project, and the time of year. In regards to the competencies needed to successfully deliver the project, he gave the example of Magic Wok deciding that it wanted to bring in ingredients for its restaurants by vessel. He says that this would be difficult because of how perishable goods are being transported. Midwest Terminals has no prior experience transporting perishable goods or knowledge of food safety, so the company would have to think very hard about these challenges in deciding if they wanted to move forward with this business opportunity.

His point about being close to home relates to what the Business Opportunities Task Force was discussing at Tuesday’s meeting. Mr. Deichert’s reason for a business opportunity close enough to home is so that the business opportunity could be effectively managed. Our task force discussed the implications of a Maumee Valley expansion outside of the country, which could be geographically very far. To me, it seems like such an expansion for Maumee Valley would require someone from the school to directly oversee day-to-day operations in order for the expansion to be everything we want. Mr. Deichert seems to feel the same about business and what he does with his work. Obviously, it is much easier to manage something when it’s closer to home, which is why it is one of his most important nonfinancial aspects of a business opportunity.

When I asked him how he makes sure that a business opportunity is consistent with his company’s long-term goals, he had a very simple philosophy: if it meets the three most important financial and nonfinancial aspects discussed above, then it would probably be OK. He said that the company will occasionally try something new, but he and his colleagues will take a look at the opportunity very closely before moving forward with it.

Overall, I learned a lot from interviewing Mr. Deichert today and I hope to incorporate his thoughts into my model in one way or another. It was very interesting to see the similarities and differences in perspectives of what a controller would do (see blog post 1-6-2016: Amy Yustick, Dana Holding Corporation) and what a chief financial officer would do when evaluating business opportunities.

1-14-2016: Expanding the Model

The first step of my model was to create a balance sheet, in addition to linking data to formulas to instantaneously calculate financial ratios. After finishing this first step of my model yesterday, I worked today on expanding the model. This involved adding more to the model that I already have.

To review the basics of my model, the user fills in specified boxes with specific data, and all inputted data is used to calculate financial ratios and other financial metrics on a different page. But what if we projected the next ten years and wanted to also include data about this on the spreadsheet? This is where things get a little more complicated, and I had to expand upon everything that I had already created. The following picture shows the spreadsheet in which the user inputs data (the user fills in as many purple cells as possible that match the labels on the left):

Click picture to enlarge

It seems simple: I had a table with a column for data, so all I would have to do is add nine more columns, so that there would be ten empty columns in which data could be entered, with one column for each year of data. However, when trying to add columns to an Excel table, the formulas won’t always do what one would them to. I had to spend a lot of time changing formulas and switching around how they would calculate the data. This basically meant going back through all of the work that I did yesterday and changing all of the formulas so that they would work for a different method of calculating data.

Besides adding columns and fixing all of the formulas on my data entry sheet, I proofread all of my formulas on the spreadsheet that gives the results and improved the layout of the spreadsheet. Otherwise, there isn’t anything else to explain about what I did today, as most of today’s work was repetitive.

Here is a picture of the results page that is given after data has been inputted (there are no actual results shown here because the first page does not have any inputted data):

Click picture to enlarge

Other things that still need to be added to the model are the net present value and internal rate of return. After doing a bit of research, I still couldn’t figure out how to use these functions in Microsoft Excel. I will be asking Mrs. Spurgeon to help me with this the next time that I work on the model. Additionally, we will most likely condense the model somewhat to make the model even easier to use and understand.

Tomorrow, I will be taking a break from modeling to interview business professionals. I will resume working on my model on Tuesday.

Just to be clear, my model is not done yet; rather, it’s almost ready to be taken a step further!