Excel Spreadsheet
Assignment 3
Open Excel (StartProgramsMicrosoft 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:



A1  Blue's Cafe  Bold, Merge Cells A1O1 
A3  Daily Sales & Expenses  Bold, Place Border Around A3C3 
A5  Hot Chocolate/Cost Per Cup to Make  Bold, Place Border Around A5D5 
A6  Hot Chocolate/Number of Cups  Bold, Place Border Around A6D6 
A7  Lemonade/Cost Per Cup to Make  Bold, Place Border Around A7D7 
A8  Lemonade/Number of Cups Made  Bold, Place Border Around A8D8 
A9  Daily Cost for Advertising  Bold, Place Border Around A9D9 
A10  Daily Cost for Rent  Bold, Place Border Around A10D10 
A12  Total Expenses  Bold, Place Border Around A12D12 
A15  Daily Revenues  Bold, Place Border Around A15C15 
A17  Lemonade/Number of Cups Sold  Bold, Place Border Around A17D17 
A18  Lemonade/Price Per Cup  Bold, Place Border Around A18D18 
A19  Hot Chocolate/Number of Cups Sold  Bold, Place Border Around A19D19 
A20  Hot Chocolate/Price Per Cup  Bold, Place Border Around A20D20 
A22  Total Revenue  Bold, Place Border Around A22B22 
A23  Total Profit  Bold, Place Border Around A23B23 
A25  Starting Funds  Bold, Place Border Around A25B25 
A26  Money Left  Bold, Place Border Around A26B26 
A27  Weather  Bold, Place Border Around A27 
A28  Windy, Snowy, Cloudy, Rainy, Sunny,  Bold, Place Border Around A28D28 
A30  Number of Cups of Hot Chocolate Sold on a Windy Day  Bold, Place Border Around A30F30 
A31  Number of Cups of Hot Chocolate Sold on Snowy Days  Bold, Place Border Around A31F31 
A32  Number of Cups of Hot Chocolate Sold on Cloudy Days  Bold, Place Border Around A32F32 
A33  Number of Cups of Hot Chocolate Sold on Rainy Days  Bold, Place Border Around A33F33 
A34  Number of Cups of Hot Chocolate Sold on Sunny Days  Bold, Place Border Around A34F34 
A36  Number of Cups of Lemonade Sold on Windy Days  Bold, Place Border Around A36F36 
A37  Number of Cups of Lemonade Sold on Snowy Days  Bold, Place Border Around A37F37 
A38  Number of Cups of Lemonade Sold on Cloudy Days  Bold, Place Border Around A38F38 
A39  Number of Cups of Lemonade Sold on Rainy Days  Bold, Place Border Around A39F39 
A40  Number of Cups of Lemonade Sold on Sunny Days  Bold, Place Border Around A40F40 
D2  Day  Bold and Centered 
E2Y2  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:
E22  =E17*E18+E19*E20 
E23  =E22E12 
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.
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.
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:
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 1121 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 partsDays 110 and Days 1121. Make sure to enter the following when constructing your chart:
Graphs to make: (Remember to choose days 110 for one chart then 1121 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: