Lemonade Stand: Using Excel to Calculate Business Expenses

If you have not already, download my Business Plan to see what your formating should look like.

In your Business Plan Excel document, rename Sheet 3 to "Expenses"

• In column A, enter the following (the parenthesis tell you which cell to enter the text in):
• Employee #1 (cell # A2)
• Employee #2 (A3)
• Rent (A4)
• 10 [product you are selling] to stock (A5)
• Leave row 6 blank
• Total Expenses (A7)

• In row 1, enter the following headings:
• Hourly (B1)
• Weekly (B2)
• Monthly (B3)
• Yearly (B4)

• First expense: employees
• You will have two full time employees
• Check my Business Plan to see where you should enter the information.
• Find out what the minimum wage is in your selected location.
• I Googled "Boulder CO minimum wage"
• The first hit was a news article from 2010 that had the minimum wage ($7.24) in the title, but I wanted to confirm with a government source.
• The sixth hit was from colorado.gov, so I knew it was a government site that would probably have current information.
• I clicked on the link and confirmed that the current minimum wage in Boulder is $7.65.  It's a good thing I double checked the government website!
• Notice that I entered in the minimum wage in the Hourly column on the row labeled with my two employees.
• Use the Sum function to automatically calculate the weekly wage of an employee by multiplying the hourly wage by 40.  The average work week is 40 hours.  
• This is the formula you should use to multiply a cell by 40:
• =SUM(B2*40)
• Use the Sum function to automatically calculate the monthly and yearly salaries of your employees.

• Second expense: rent
• Determine how much space your product needs
• 100 sq. ft - Small (Beads, Comic Books) $500/month
• 1000 sq. ft - Medium (Blenders, Baseball Gloves) $5,000/month
• 5000 sq. ft - Large (Cars, Furniture) $25,000/month
• I think I will be able to fit enough bikes in to a medium space, so will enter my monthly rent as $5,000/month.
• Notice that I used the sum function key to automatically calculate the yearly amount.  This will be useful later if I need to move into a larger space.

• Third expense: products
• You can't open a store with zero products in it, can you?
• In Step 3 of the last post, we calculated the cost of our products.  The average price of a mountain bike in Boulder is $2,399.20, so the cost is half that at $1,199.60.
• Add the price and cost of your product to your expenses sheet.
• I put mine in cell D9 and D10.
• As a one time expense, let's buy 10 products to fill our shelves.
• Remember, it's best to use the Sum key to automatically calculate formulas in Excel.
• Use this formula to automatically calculate the cost of 10 products: 
• =SUM(D10*10)

• Total your expenses using the Sum key.  When selecting which cells to sum, be sure you have the correct cell selected!
• Explore my document to be sure yours looks like mine.

• Ask a neighbor to glance over your expenses, and ask if your Expenses sheet matches Mr. LaTorre's.

• Add your Total Yearly expenses to the Lemonade Stand Project Spreadsheet.