Activity # 7

Spread Sheet Unit
Microsoft Excel

Spreadsheet #7 - Company Profit Sheet

Part 1

This is your final company spreadsheet. You will need to use all of your spreadsheet and word-processing knowledge to complete this assignment.

1. Begin by setting up a new spreadsheet similar to spreadsheet #6 from yesterday. You should have the title of “Monthly Profit Sheet” as it is on the spreadsheet on the back of this paper.

2. Using the same method as yesterday, list at least 25 of the products that your company sells.

3. When you are coming up with the quantity number, think about how many of those items you think you will sell in one month.

4. When you are creating the selling price, think about how much you would pay for the item in a similar store. The unit price is usually 10% – 25% lower than what you are selling it at.

5. Create the formulas to figure the “Cost of Goods Sold”, “Income”, “Profit from Goods Sold”, and “Total Profit From Goods Sold.”

6. When Finished, Save as SS7 and Print a copy with the formulas showing.

PART 2

1. Once you have finished the spreadsheet, you need to transfer it to your letterhead you have created. Follow the directions below very carefully.

2. Be sure that the formulas are NOT showing, and that the columns are only wide enough so that there is no extra space.

3. Minimize your spreadsheet, and open you letterhead assignment.

4. Go to “File – Page Setup” and click on the Paper Size tab, and select Landscape under Orientation.

5. You will need to select you letterhead using the arrow tool and move it to the top center of the page.

6. Go back to your spreadsheet and highlight only the cells with your information in them.

7. Go to “Edit – Copy”, then switch back to your letterhead assignment.

8. Create a blank text box under your letter head, and go to “Edit – Paste.”

9. If your entire spreadsheet fits, Go to SAVE AS and call it “Profit Sheet” and skip to step 11.

10. If it does not fit - With the text box still selected, go to “Edit – Select All” then “Format – Font” and change the size to 8, then “Table – Cell Height and Width.” Click on the “Row” tab and change where it says “at least” to “Auto.”

11. PRINT PREVIEW AND PRINT IF ALL FITS ON ONE PAGE.

Click here for an example sheet

Part 3

Now that you have a spreadsheet that calculates your “Total Profit From Goods Sold”, you need to take that number and subtract your other business expenses to find out how much money you actually will make.

1. Open your SS7 from part 1 and begin entering the information below your previous information as shown below. The gray area is from part 1.

2. The Formula for “Gross Income” should just move the amount from “Total Profit From Goods Sold” to the Gross Income cell. To do this you just put = and the cell address of where you want the number to move from.

3. Under expenses, your first expense is the monthly payments on the loan you needed to start the company. To calculate this follow use the loan calculator found under "Online Resources" and follow the directions below.

· Type in the loan amount you requested in your business letter earlier in the quarter.. If you didn’t ask for a particular amount, use the amount $300,000.

· Change the “Term in Months” to 360, giving you a 30 year loan.

· Click on calculate and Use the amount listed under “Monthly Payment”

4. You should have at least 8 expenses related to your company. Examples are: Rent, Salary, Insurance, Advertising, Office Equipment, and Gas expense if you use a vehicle in your business. These expenses are usually 3-10% of your Gross Income. Estimate the numbers.

5. You should then create a formula to total your expenses

6. The final formula should give you how much money you have left after subtracting your total expenses.

7. Insert a header with your company name on it, and a footer with your name and period on it.

8. When you are finished, Save again as SS7.

9. Go to “File – Page Setup” and change the page orientation to Landscape.

10. Make sure that the columns are narrow enough so that it will print on one page.

11. Print one copy with the numbers showing, and one copy with the formulas showing.

911	30	$50,000.00	$1,500,000.00	$80,000.00	$2,400,000.00	$900,000.00
928	40	$20,000.00	$800,000.00	$45,000.00	$1,800,000.00	$1,000,000.00
944	45	$15,000.00	$675,000.00	$30,000.00	$1,350,000.00	$675,000.00
959	5	$75,000.00	$375,000.00	$150,000.00	$750,000.00	$375,000.00

				Total Profit From Goods Sold		$2,950,000.00

Monthly Net Income Sheet

Gross Income	<Formula>

Expenses
Loan	<Value>
Ads	<Value>
Rent	<Value>
Insurance	<Value>
Salaries	<Value>
Other Expense	<Value>
Other Expense	<Value>
Other Expense	<Value>

Total Expenses	<Formula>

Net Income	<Formula>

Previous Activity - Home Page