Collaborate on Excel Pro Forma Template for Administration with Ease Using airSlate SignNow

Excel pro forma template for Administration

hello thank you for taking time to watch this short video my name is Bruce Kaufman and this is one of a few videos I am creating to demonstrate the Excel projects I have done over the past few years for clients of mine and today I'm going to talk about a custom application I created for a financial advisory firm that was used by this firm to help their clients create financial statements that would be used to raise money for the company let me tell you a little bit quickly about the project my client was a financial advisory firm and what they wanted was a tool that they could sell to their clients who were entrepreneurs which would help these entrepreneurs raise capital for their business and what you're about to see is the initial version of the project that they sold on their website several years ago a few things that we wanted to keep in mind as we built this were first of all that the target user was a non Financial Manager and what that basically meant was we wanted to recognize they had a minimal knowledge of Excel and we wanted them to spend their time putting data into the form not trying to figure out how to use Excel so we wanted it to be easy for them to enter data and we wanted the financial statements to be created automatically when the customer was finished doing the product okay so let's go ahead and take a look at the product and you can see okay so this is the product and we're going to flip over here to the first page and the first thing to notice is that we have these color coded tabs that make it easy to hop back and forth yellow is input blue is output deal is deal structure and the idea here was I didn't want the user to have to search down here looking at the spreadsheet tabs trying to figure out where they had to go I want to put in I can just hop from one page to the next without any issue at all I'll show you some of the other features as we go the first page on General data was just some basic assumptions and was set up in a way for them to provide information financial information which was needed but which you would not necessarily need know that you needed to have if you didn't have a financial background so we had questions about fundraising questions about your stock what you have now what you want to raise what your starting assets will be some expectations about your expenses and then some calculations that will be used later down here on this page on unit sales this was set up on the assumption that they would be providing products and it's set up so that the user could go in up to 10 categories and type in what they were going to do so let's say the first year I was going to make gadgets you can see that right here that gets translated down to here the years that you see down here are based on the data that's put in this cell with a little help tip on there okay and in the first year let's say we decide well the first year I'm not going to be selling anything well if they decided that they were going to put zero they get a message because we wanted them to show the year that they actually started producing and selling you have to have a quantity of one and you can't have zero in the cell so we're going to have one here so let's say they decided 100,000 in the first year you see down here unit sales unit price of one I'm going to sell them for $125 and I already have in here I'm going to estimate an increase in unit sales of 5% a year and 3% on the price and you can see the price is reflected here in these columns at that rate what you could do in the next year you say I'm going to do ygs widgets but let's say I was going to do those in year three and I was going to sell 75,000 to start and I'm going to sell them for 75 and I'll leave these the way they are and what you see is you'll notice that that 75,000 comes but it doesn't start until their third year of operations cost of goods sold this is where it allowed them to build the individual cost and the way this plays out on the income statement is they have gadgets revenues gadgets cost of goods sold so they can have a direct relationship to the extent they're able to figure that out between the two and got them thinking in terms of what is the cost to produce these items and what you would do is you just and again you can change anything you wanted to here if you want to put a different category you can do that and it gets carried through all right but for the gadgets let's say it's $125 and my advertising was going to be 75 cents and my labor is going to be 50 cents and my indirect labor is going to be 25 cents and what happens is you see the first of the warnings you get two kinds one tells you if your cost is going to exceed your price and the second tells you if you're going to have an oper operating loss in that year we focused on operating loss because we wanted investors to see if the company was actually going to make money just to demonstrate a little further if I put a dollar here all right but I took this one out here what you can see is in this year the cost exceeds price but because of the way the revenues escalate they don't start incurring losses if they decided they made a mistake and said oh I wanted to put a different price they could just change the price and it would go away this was a good time to introduce this little button right up here what this does is a little popup that they could keep open as they worked shows the revenue gross profit operating profit and every time they change something on here the numbers get changed up here as well okay so they could not only see if they were having losses but they could see exactly what the what the price was was and again here you see cost is each price but they're not actually incurring any losses in any of these years okay so that's how that works and so the similar concept let me go ahead and just leave this on here for a minute because I want you to see that this also this also showed up when they were having losses okay if they were having losses in these years that also showed up on where they would put in unit sales and and GNA expenses so they would know they could adjust it so the combination of this warning sign having this financial results up here allow them to play with the various numbers until they got good operating results and gave them a realistic view of what it would take in order to produce the kind of business that they were trying to do okay so we'll go back here we'll take this off we'll take this off we'll take this off and then the only other things that we'd want to show you is these are the financial statements income statement balance sheet cash flow sources and uses also failed to mention just let me show you two more things capitalized assets they could go in here useful life for depreciation purposes the year and the amount they were going to buy it okay this played out on the income statement down here in the depreciation category and it's also showing in the cash flow and the sources and uses this button the user could click and it would send an email to me in case they were having trouble and on this one we have a little help screen I've done it full screen so you can see we made it look like a Windows help screen so there'll be something they comfortable with before you start some information they needed to know information about the different Navigation tabs and some other information about using the program so that's the example of the creating proor of financial statement thank you for watching this video