Spreadsheet Project
You have learned about spreadsheets, and you've learned a bit about business along the way. Now is the time to put your practice into use on a business model of your choosing.
For your final project with spreadsheets, you are the starting an imaginary business that buys and sells consumer goods, and you employ a staff of five people.
You might be a ski-doo dealership or a stationary shop, a bakery or a hardware store, an outdoor gear shop or a auto parts store. Please pick something school-appropriate or it won't be marked.
For your business you will create one workbook (a single Excel file) with 6 related sheets. Here is the criteria for each sheet:
We will assume that you are starting your business on January 1st and you will create a series of spreadsheets for the following fiscal year (to the end of December).
Sheet 1 - Business Equipment: Spend exactly $500 000 on equipment for your business. Use formulas to calculate the costs of items (cost per unit x number of units purchased) and use a sum total formula to help you hit $500 000 exactly. You might need computers, a cell phone, a laptop, stationary, office equipment, or even a new car or truck. You could even purchase a building (or a sculpture for the lobby) for the exact amount that you need to take you to $500 000.
Sheet 2 - Inventory: In January, you purchase stock for your business. Please note: You purchase items for your inventory at the WHOLESALE PRICE, and then you sell these items at the RETAIL PRICE. The difference between the wholesale and retail price is the profit you generate with each sale.
Purchase at least 25 different items (with an appropriate number of units for your business) to add to your inventory. You will need to include the unit price and the number of units purchased as we. Calculate the total spent on each item, the total spent on inventory, the totals of the items in your inventory, and the total value of your inventory.
Sheet 3 - Sales: Month by month sales for the year. Concepts that should appear in this sheet include:
***Bonus: Include a column (and use a formula) to show your remaining stock of each item. Use the cell reference (not the number) for the inventory numbers on Sheet 2 as you create your formula.
Sheet 4 - Wages: Wages for one year (divided into weeks/months) for your five employees. Include the employee name, title, hourly wage, hours worked, and total wages earned. You should also calculate the total wages paid for this month. You can use fixed cell referencing for the wage so you can adjust this number and see how small changes affects a person's total salary in a year.
Sheet 5 - Expenses: Expenses for your business for one year. Expenses might include the following:
Sheet 6 - Profits: Using the information from cells on your other spreadsheets, calculate your total profits for the year.
Your mark for this assignment will be based on the presentation and organization of your data on each sheet as well as the clever use of formulas to accomplish your tasks.
May your business be ever profitable.
For your final project with spreadsheets, you are the starting an imaginary business that buys and sells consumer goods, and you employ a staff of five people.
You might be a ski-doo dealership or a stationary shop, a bakery or a hardware store, an outdoor gear shop or a auto parts store. Please pick something school-appropriate or it won't be marked.
For your business you will create one workbook (a single Excel file) with 6 related sheets. Here is the criteria for each sheet:
We will assume that you are starting your business on January 1st and you will create a series of spreadsheets for the following fiscal year (to the end of December).
Sheet 1 - Business Equipment: Spend exactly $500 000 on equipment for your business. Use formulas to calculate the costs of items (cost per unit x number of units purchased) and use a sum total formula to help you hit $500 000 exactly. You might need computers, a cell phone, a laptop, stationary, office equipment, or even a new car or truck. You could even purchase a building (or a sculpture for the lobby) for the exact amount that you need to take you to $500 000.
Sheet 2 - Inventory: In January, you purchase stock for your business. Please note: You purchase items for your inventory at the WHOLESALE PRICE, and then you sell these items at the RETAIL PRICE. The difference between the wholesale and retail price is the profit you generate with each sale.
Purchase at least 25 different items (with an appropriate number of units for your business) to add to your inventory. You will need to include the unit price and the number of units purchased as we. Calculate the total spent on each item, the total spent on inventory, the totals of the items in your inventory, and the total value of your inventory.
Sheet 3 - Sales: Month by month sales for the year. Concepts that should appear in this sheet include:
- The number of units sold and the total sales for each month
- Profit (Retail price - unit price) for each item
- Total profit
***Bonus: Include a column (and use a formula) to show your remaining stock of each item. Use the cell reference (not the number) for the inventory numbers on Sheet 2 as you create your formula.
Sheet 4 - Wages: Wages for one year (divided into weeks/months) for your five employees. Include the employee name, title, hourly wage, hours worked, and total wages earned. You should also calculate the total wages paid for this month. You can use fixed cell referencing for the wage so you can adjust this number and see how small changes affects a person's total salary in a year.
Sheet 5 - Expenses: Expenses for your business for one year. Expenses might include the following:
- Telephone/Internet/Cell phone plans
- Building Costs (Rent/Mortgage)
- Electricity
- Advertising
- Wages
- Office supplies
- Cleaning/Maintenance
Sheet 6 - Profits: Using the information from cells on your other spreadsheets, calculate your total profits for the year.
- Total Sales minus the following
- Inventory costs
- Wages (remember to multiply your monthly cost from spreadsheet 4 by 12)
- Business expenses (be careful not to count your wages twice)
Your mark for this assignment will be based on the presentation and organization of your data on each sheet as well as the clever use of formulas to accomplish your tasks.
May your business be ever profitable.