Excel Spreadsheet

Assignment 3
Open Excel (Start-Programs-Microsoft Excel).  Click on file, then page setup.  Choose landscape, then OK.  Save as Lemonade Income Statement.  Do not change the size of your cells in order to fit in the information.  You will go back later and format, position, and add formulas once we have entered the text.   The first thing you will do in order that your information is more "readable'" is to set your page setup to landscape.  To do this go to File, then Page Setup, and choose the Landscape radio button. Type in the following information in the cell indicated:
 
Cell
Information to Enter
Format
A1 Blue's Cafe Bold,  Merge Cells A1-O1
A3 Daily Sales & Expenses Bold, Place Border Around A3-C3
A5 Hot Chocolate/Cost Per Cup to Make Bold, Place Border Around A5-D5
A6 Hot Chocolate/Number of Cups Bold, Place Border Around A6-D6
A7 Lemonade/Cost Per Cup to Make Bold, Place Border Around A7-D7
A8 Lemonade/Number of Cups Made Bold, Place Border Around A8-D8
A9 Daily Cost for Advertising Bold, Place Border Around A9-D9
A10 Daily Cost for Rent Bold, Place Border Around A10-D10
A12 Total Expenses Bold, Place Border Around A12-D12
A15 Daily Revenues Bold, Place Border Around A15-C15
A17 Lemonade/Number of Cups Sold Bold, Place Border Around A17-D17
A18 Lemonade/Price Per Cup Bold, Place Border Around A18-D18
A19 Hot Chocolate/Number of Cups Sold Bold, Place Border Around A19-D19
A20 Hot Chocolate/Price Per Cup Bold, Place Border Around A20-D20
A22 Total Revenue Bold, Place Border Around A22-B22
A23 Total Profit Bold, Place Border Around A23-B23
A25 Starting Funds Bold, Place Border Around A25-B25
A26 Money Left Bold, Place Border Around A26-B26
A27 Weather Bold, Place Border Around A27
A28 Windy, Snowy, Cloudy, Rainy, Sunny,  Bold, Place Border Around A28-D28
A30 Number of Cups of Hot Chocolate Sold on a Windy Day Bold, Place Border Around A30-F30
A31 Number of Cups of Hot Chocolate Sold on Snowy Days Bold, Place Border Around A31-F31
A32 Number of Cups of Hot Chocolate Sold on Cloudy Days Bold, Place Border Around A32-F32
A33 Number of Cups of Hot Chocolate Sold on Rainy Days Bold, Place Border Around A33-F33
A34 Number of Cups of Hot Chocolate Sold on Sunny Days Bold, Place Border Around A34-F34
A36 Number of Cups of Lemonade Sold on Windy Days Bold, Place Border Around A36-F36
A37 Number of Cups of Lemonade Sold on Snowy Days Bold, Place Border Around A37-F37
A38 Number of Cups of Lemonade Sold on Cloudy Days Bold, Place Border Around A38-F38
A39 Number of Cups of Lemonade Sold on Rainy Days Bold, Place Border Around A39-F39
A40 Number of Cups of Lemonade Sold on Sunny Days Bold, Place Border Around A40-F40
D2 Day Bold and Centered
E2-Y2 1, 2, 3, 4, up to 21 Bold and Centered
Z11 Total Bold and Centered
AA11 Expenses Bold and Centered
Z21 Total Bold and Centered
AA21 Revenue Bold and Centered
Y24 Money Bold and Centered
Y25 Left Bold and Centered

Now that all your text is entered into the proper cells, formulas need to entered in order to keep a running tally of your expenses.  This will automatically calculate data for you without having to do the math yourself.  How Cool!!!!!

Math operation conversions:
 
Add +
Subtract -
Multiply *
Divide /
Percentage %
Exponentiation ^
Equal =
Greater than >
Less than <
Greater than or equal to >=
Less than or equal to <=
Not equal to <>

In order to enter formulas, follow the following steps:

Enter the rest of the formulas the same way in the following cells.  Remember to copy the formulas across the rows following the steps above.
 
E22 =E17*E18+E19*E20
E23 =E22-E12
E25 100.00
Do not copy this formula to neighboring cells.
E26  =E25+E23
Do not copy this formula to neighboring cells.
F26 =E26+F23
AA12 Click on the function key (f*), choose sum from the dialog box and choose cells E12:Y12
AA22 Click on the function key (f*), choose sum from the dialog box and choose cells E22:Y22
G30 Click on the function key (f*), choose sumif from the dialog box; choose cells E28:Y28 for the Range; type
Windy in the Criteria box; choose cells E17:Y17 for the Sum_range box. Do not copy across cells.
G31 Click on the function key (f*), choose sumif from the dialog box; choose cells E28:Y28 for the Range; type
Snowy in the Criteria box; choose cells E17:Y17 for the Sum_range box. Do not copy across cells.
G32 Click on the function key (f*), choose sumif from the dialog box; choose cells E28:Y28 for the Range; type
Cloudy in the Criteria box; choose cells E17:Y17 for the Sum_range box. Do not copy across cells.
G33 Click on the function key (f*), choose sumif from the dialog box; choose cells E28:Y28 for the Range; type
Rainy in the Criteria box; choose cells E17:Y17 for the Sum_range box. Do not copy across cells.
G34 Click on the function key (f*), choose sumif from the dialog box; choose cells E28:Y28 for the Range; type
Sunny in the Criteria box; choose cells E17:Y17 for the Sum_range box. Do not copy across cells.
G36 Click on the function key (f*), choose sumif from the dialog box; choose cells E28:Y28 for the Range; type
Windy in the Criteria box; choose cells E19:Y19 for the Sum_range box. Do not copy across cells.
G37 Click on the function key (f*), choose sumif from the dialog box; choose cells E28:Y28 for the Range; type
Snowy in the Criteria box; choose cells E19:Y19 for the Sum_range box. Do not copy across cells
G38 Click on the function key (f*), choose sumif from the dialog box; choose cells E28:Y28 for the Range; type
Cloudy in the Criteria box; choose cells E19:Y19 for the Sum_range box. Do not copy across cells.
G39 Click on the function key (f*), choose sumif from the dialog box; choose cells E28:Y28 for the Range; type
Rainy in the Criteria box; choose cells E19:Y19 for the Sum_range box. Do not copy across cells.
G40 Click on the function key (f*), choose sumif from the dialog box; choose cells E28:Y28 for the Range; type
Sunny in the Criteria box; choose cells E19:Y19 for the Sum_range box. Do not copy across cells.

The next step is to format cells to reflect the data the way you want others to see them.  (Example:  currency to show dollar amounts, general to show where numbers will be placed.  In order to do this follow the directions below.
 

Enter the rest of the formats the same way in the following cells. Remember to copy the formats across the rows following the steps above.
 
 
E7, E9, E10, E12, E18, E20, E22, E23, E25, E26,  Currency with 2 decimals
AA12, AA22, Currency with 2 decimals (Do not format across rows)
E6, E8, E17, E19 Number with 0 decimals
E28 Text

Now that we have our formulas and text entered, apply formatting to make your table more presentable.  Add borders around text, apply bold, fill color, or other formatting techniques, to make your table easier for others to follow.

Open your Daily Expenditures and Revenue Worksheet and fill in your table.  If you see this in a cell ######, you need to widen your cell.  You do this by click between the Letters at the top of the chart until an arrow appears.  Left click and drag until you see numbers appear.  If you still see something other than numbers in your cell, make sure your formula was entered in the cell correctly.

Next click on the worksheet tabs located on the bottom of your screen.  You must double click on tab in order to name them.

Excell Spreadsheet Assignment

Assignment 4

Now that you have your Excel chart designed and all your formulas in place, go to your daily log sheet and insert your information into the chart. If your formulas are in the chart correctly, you should see your information being calculated as you enter your data.

Before moving on to assignment 5, go back to the Excel chart to do one more calculation. I would like to know the average selling price per cup of hot chocolate and lemonade, as well as other averages. In order to calculate the average selling price per cup of hot chocolate, follow the directions below:

Calculate the averages for the following:

Excell Spreadsheet Assignment

Assignment 5

Now that you have done all the hard stuff, lets have a little fun with charts and graphs. To do this, follow the steps below:

Repeat the steps above for days 11-21 by following these simple steps:

Follow the above steps to create graphs that show the following data: Make sure you divide the charts into two parts-Days 1-10 and Days 11-21. Make sure to enter the following when constructing your chart:

Graphs to make: (Remember to choose days 1-10 for one chart then 11-21 for the other chart)

The following information only contains one data a set. To display this type of data in a chart, follow the steps below:

Blue's Cafe