Excel Spreadsheet

Assignment 3

Assignment 4

Assignment 5

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:

• Click in the cell you want your answer to appear.  Example, in cell E12 I want to multiply the cost per cup by the number of cups made for both lemonade and hot chocolate  and then add the cost of  daily rent and advertising..  Click in cell E12
• Click the = sign in the formula bar at the top of the page, the blank space just above the C column.
• Either type in E5 or click on E5 in your worksheet
• Type * (the operation for multiplication)
• Either type in E6 or click on E6 in your worksheet
• Type + (the operation for addition)
• Either type in E7 or click on E7 in your worksheet
• Type * (the operation for multiplication)
• Either type in E8 or click on E8 in your worksheet
• Type + (the operation for addition)
• Either type in E9 or click on E9 in your worksheet
• Type + (the operation for addition)
• Either type in E10 or click on E10 in your worksheet
• Click OK
• A quick way to enter this formula throughout the entire row is to click in the cell E12
• Click Edit on your standard menu bar, select copy
• Click in the F12 cell, while holding the left mouse button down, drag through the row until you have selected all row cells through Y12.  Release the mouse button.
• Click Edit  on your standard menu bar.  Select paste special and click the formula radio button, then OK.  You have now copied the formula to the other cells without entering each cell separately.

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.
 

• Click on cell E5.  On the standard toolbar click format, then select cells.  Select currency weigh 2 decimal places, then OK.
• Click on cell E5.  On the standard toolbar click Edit, then copy.
• Click in Cell F5, hold down the left mouse button and drag across the row selecting it until you reach Y5.
• Click Edit, then paste special.  Click the Format radio button, then OK.  This copies the currency format to the neighboring cells.

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.

• Rename the first worksheet-Daily Income Statement
• Rename the second worksheet-Charts and Graphs

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:

• In cell AA4 type "average"
• Click in cell AA5
• Click the function button (f*)
• Choose average
• Choose cells E5:Y5
• Click OK
• The average cost for hot chocolate should now be displayed in cell AA5

Calculate the averages for the following:

• Number of cups of hot chocolate made
• Cost per cup of lemonade to make
• Number of cups of lemonade made
• Number of cups of hot chocolate sold
• Price per cup of hot chocolate
• Number of cups of lemonade sold
• Price per cup of lemonade

• In order to compare information graphically, we must choose the data to reflect in our graphs. I want to compare the number of cups of hot chocolate made to the number of cups of hot chocolate actually sold. If I choose all 21 days, the data would look cluttered, so we will choose to display our day in Days 1-10 and Days 11-21.
• To choose the first set of data, click in cell E6. While holding the left mouse button down, drag over cells until you reach N6.
• To select the other data for your graph, hold down the Ctrl key. This lets the computer know that you are selecting other data that is not adjacent to that cell. Now select cells E19 and drag over until you reach cell N19.
• Click the Chart Wizard button on the standard toolbar (It looks like a bar graph.
• Choose chart type, then subtype. Click next.
• Name Series 1 "Cups Made"
• Name Series 2 "Cups Sold"
• Title your chart "Number of Cups of Hot Chocolate Made Vs. Sold."
• In the X Axis type "Days 1-10"
• In the Z Axis type "Number of Cups Sold." Click Next.
• In step 4 you will choose to place your chart in "As object in" the choose "Charts and Graphs"
• Drag your chart to the upper left hand corner of your worksheet

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

• Click in cell O6, hold the left mouse button, drag over cells until your reach Y6
• Select the other data by holding down the Ctrl key and selecting O19 to Y19
• Click the Chart Wizard
• Choose chart type and subtype
• Name Series 1 "Cups Made"
• Name Series 2 "Cups Sold"
• This time you want Days 11-21 to appear on the X Axis. Before clicking Next at the Chart Wizard Dialog Box, click the red arrow on the text box next to "Category X Axis Values" and then select 11-21 in the (O2-Y2). Click the red arrow again to return to the Chart Wizard Dialog Box. The numbers 11-21 should now appear at the bottom of your chart.
• Title your chart "Number of Cups Sold."
• Name the X Axis type "Days 11-21"
• Name Z Axis type "Number of Cups Sold"
• Step 4-choose place your chart in as object in and choose "Charts and Graphs"
• Drag your chart next to the chart already placed in your worksheet. Compare it to the one already done and see if it resembles the information you are trying to display.

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:

• Name series 1 and 2 with names that represent the data you are trying to display
• Title your chart according to the data chosen
• Name your X and Z Axis types with titles that represent the data you are displaying
• Place your chart in the Chart and Graphs Folder in your workbook and drag it in your worksheet so that all your graphs are visible

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

• Number of Cups of Lemonade Made Vs. Sold
• Total Expenses Vs. Total Revenue
• Daily Profit Vs. Daily Expense

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

• To compare the number of cups of hot chocolate vs. lemonade sold on windy days, highlight the two rows that contain this information, then follow the above steps in creating a graph After completing this graph, construct graphs of the following data
• Number of cups of hot chocolate vs. lemonade sold on snowy days
• Number of cups of hot chocolate vs. lemonade sold on cloudy days
• Number of cups of hot chocolate vs. lemonade sold on rainy days
• Number of cups of hot chocolate vs. lemonade sold on sunny days