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!