2021 NFL Draft Game Spreadsheet Template

It’s been one year since the COVID-19 pandemic began. Last year, since March Madness and other sports at the time were cancelled, I started thinking about what other things I could do to fill in the void of not having any March Madness brackets to fill out. The answer came in a suggestion from a reader to create an NFL Draft game spreadsheet. I’ve updated the template for this year.

Inside this template I've listed the top 100 draft prospects according to ESPN. Each draft game player (and the template is currently setup to handle ten players) are randomly assigned ten future NFL players by using a randomize macro. The earlier your players get drafter the better, as the draft position counts for points and the lowest number of points wins!

The random number macro is pretty simple:

'define range of cells for random numbers

Dim Player1 As Range

Set Player1 = Range("AN2:AN101")

Player1.ClearContents

For Each a In Player1

Do

a.Value = (Int((100 * Rnd + 1)))

Loop Until WorksheetFunction.CountIf(Player1, a.Value) < 2

Next

Based on some good user feedback I added the option where each player can now try to predict which team will select their players for additional bonus points. Well, negative bonus points that is, as it subtracts points from your total score (remember, lowest score wins).

See how the Draft Game spreadsheet works in the video below:

Even if you have no interest in the NFL, football, or drafts, you can still learn a bit about Excel by examining the random number generator macro, or the vlookup and sumif formulas used. The scoreboard uses a “rank without ties” formula:

 =(IF(D3<>"",(RANK(D3,$D$3:$D$52)+COUNTIF(D$3:D3,D3)-1),""))

For now, the spreadsheet is setup to handle ten players. To add more, the formulas and macro will need to be modified. If enough people are interested in using this sheet, I will work on making it scalable so it can automatically adjust to the exact number of players.

2021 NFL Draft Game Spreadsheet Template.xlsm 

Let me know if you like this game or if you have any suggestions or questions.

Personal Business Management Spreadsheet Template

We’re in the middle of tax season here in America so it comes as no surprise that some of the most requested Excel spreadsheets this time of year are personal and business finance and accounting templates. In my everyday life I use three primary spreadsheets to help track my finances and will be writing a post about each one.

First up is what I like to call my business accounting spreadsheet. I call this a “business” but it’s pretty basic seeing as how I’m the only employee. Maybe a better name would be web site management spreadsheet or even personal project management (or project tracking) – because that’s what this is, a personal project, after all!  Also please note, this template is relatively new and is continuously evolving as I add new features to my web site and want to analyze the data in new ways.

The Concept

So what is this so called business or project? This blog uses the Blogger platform, which is free to use but requires .blogspot to be added at the end of the domain name. I’ve always wanted to create my very own web site and finally did so - how to learn to write CATIA macros. The site contains several free articles with tips and advice about VB scripting in CATIA, a 3D CAD program.

However, creating and maintaining my own web site costs money. I decided to treat my site like a business. I keep track of all expenses and revenue because the goal is to have the site pay for itself through the sale of an eBook I wrote on the same topic. If the site is not profitable over time I will abandon it. I guess you could classify this type of web site as a “niche profit site.”

Total Expenses

The first sheet I have in my template is labeled Total Expenses. This where I keep track of any products or services I have to buy to keep the web site up and running, as well as the initial start up fees. For example, I purchased the domain name www.scripting4v5.com through NameCheap at $10.87 for an entire year. In the month column I use the MONTH function to return the month of a date as a number, which will be used later on in my monthly report worksheet. I use HostGator (exceptional customer service – I speak from experience!)  to host my web site, a monthly expense.

For my CMS (content management system) I decided to go with Wordpress because it’s user friendly and free (thus not included as an expense). In addition, I purchased a new theme called Socrates due to its number of built in features which again are very easy to use. The onetime fee is added to the expense sheet.

In order for customers to purchase and download my eBook, I needed a way to protect the download link so it couldn’t be copied and shared with other users. I bought a program called WP File Lock one another onetime fee of $47.

Finally, I use an email newsletter service called Aweber to manage my email subscribers. This service is a monthly fee of $16.33. That’s it for my expenses. I know it sounds like a lot but really I only have two only monthly bills (email newsletter, and hosting) and one yearly expense (domain name).

Total Revenue

Now, let’s look at the next tab in my workbook, Total Revenue, where I list all my revenue generated from the web site. At this time I am using Google Adsense to place one banner of ads across the top of the site as well as Kontera ads within the text.

The main revenue stream is from selling VB Scripting for CATIA V5 eBook. I have a referrer column to indicate whether I sold the eBook or if it was sold through one of my affiliates. Yes, if you have a Clickbank account you can earn a 50% commission for selling my book for me!

Once again, I use the MONTH function to return the number of the month, as in cell F2, =IF(E2="","",MONTH(E2)). At the bottom of the sheet I add the totals for each of my site revenue streams, as in cell B20 I have =SUMIF(A2:A15,A20,D2:D16).

Monthly Report

Finally, on the third sheet I can look at my total expenses and revenue by month. This gives me a great snapshot of how the site is doing. I use the SUMIF formula on my Monthly worksheets where I can view total expenses, revenue, and if I have made or lost money for the month. For example, in cell B2,

=SUMIF('Total Expenses'!F2:F9,2,'Total Expenses'!C2:C9) I also use conditional formatting to highlight when I've spent more money than I’ve made in red and highlight the text in green when I have made a profit.

Summary

In review, I was able to setup my first web site at an initial cost of $209.11, shown here on my spreadsheet. My expected monthly recurring expenses are $26.28. So now what? The purpose of the site is to sell my eBook. When a sale is made I add it to my revenue column. It will take a few sales to cover my initial expenses but then I should only need to sell one eBook a month in order to pay for the site every month.

Sorry for the long article, but that was my “business” spreadsheet in full. Next, I’ll cover the Excel spreadsheet template I use to track all of my real-world and online income, then we’ll look at how I keep track of bills and other living expenses.

DOWNLOAD PERSONAL BUSINESS PORJECT TRACKING TEMPLATE.xls

*Full disclosure: Some of the links in this article are for affiliates. I earn a commission if you purchase the product having following the link. I only name products that I actually use and fully endorse.

·         Tags: personal finance excel spreadsheet, monthly finance spreadsheet, Free excel project sheet

How to use the SUMIF formula in Excel

SUMIF is a useful formula to know how to use when creating Microsoft Excel spreadsheet templates. An example I can give is from the world of engineering. Say you have to create a part list or a bill of material (BOM for an assembly, such as a snow blower. The snow blower has multiple parts with have standard parts, such as washers, bolts, nuts, etc,, attached to it. At the bottom of my BOM I want to sum all of the parts for my top level assembly but I don’t want to include the number of standard nuts and bolts. In this case I use the SUMIF formula along with some nifty formatting.

 

 

To count the number of parts in a specified column use the =SUMIF() formula. In my case, =SUMIF(K5:K17,">0").

If a number exists in a column which is greater than zero then sum the given range in the column.

One thing that I did was to change the standard part counts, which used to be entered as (1), (2), (3), etc. (entered '(1)). I have since changed this to a new format: -1, -2, -3... yet the entries still look like (1), (2), (3).  This way the SUMIF formula can count the numbers since they are not being recorded as text.  With the numbers being negative it can also make it so the formulas can selectively count them in the different parts of the spreadsheet.  So, when entering parts like weld nuts, nut plates, pin, collars, etc. enter the count as a negative (-) number.  This can either be done as -1 or (1), both will mean the same. I also put in a custom number format so you  no longer have  to type "-1", "-2", etc.  Just enter 1, 2, 3, etc. and the "-" will be placed in front automatically. Little improvements like this go a long way in improving the time it takes to compile one of these massive spreadsheets.

Let me know if you have any questions or concerns and if this format is more or less helpful and an improvement in function and readability.