Multiple Worksheets and Workbooks

• I find multiple worksheets in a workbook to be an outstanding tool.
• You can have computations cross both worksheets and workbooks.
• Displaying multiple worksheets and workbooks
  • I don't know why he starts with this section, I would start with the next and come back and view this one after the others.
  • This shows how to have multiple windows visible at the same time.
• Renaming, inserting and deleting sheets,
  • Basic skills for using multiple worksheets.
  • Notice, deleting sheets can not be undone.
• Moving, copying and grouping sheets
  • More basic skills with using sheets.
  • I would be really careful working with grouped sheets.
• Using formulas to link worksheets and workobooks
  • This is not too hard but it will be essential.
  • References across sheets.
  • Notice the recommendation "Do not type sheet names"
• Locating and maintaining links
  • This is critical if you are using multiple workbooks.
  • We will examine this further in the future.
  • For now you may skip this section.
• We will also look at the first part of the next section, Using If functions and relational operators.
  • the if functions allow us to make decisions.
  • If takes a yes/no question
  • And the value to put in the cell if the answer is yes
  • And the answer to put in the cell if the answer is no.
  • Operators, > >=, < <=, =, <>

Cookies!

• For this exercise, we will be working with this file.
• Rename sheet1 to be Orders
• Rename sheet2 to be Assumptions
• Go to sheet 1 and format the sheet
  • Make labels bold.
  • Rotate them and allow word wrap.
  • 
• A charge column for each of the cookie types
• 
• For each cookie for each customer, calculate the cost of the cookies
  • Prices for each type are on the assumptions sheet.
  • This is a simple multiply
  • 
• Add a column to calculate the subtotal, (the price of all cookies) and do this computation for all customers.
• Add a column for the discount.
  • If a customer is a member of the cookie club, then they receive the discount listed in the assumptions page.
  • =IF(B2="yes",I2*Assumptions!$B$2,0)
• Add a column for the final price, Calculate the final price, (Subtotal - discount)
• Add a column for the shipping costs
  • If the order is over the amount on the assumptions page, the shipping is free, otherwise the price is listed per dozen on the assumptions page.
  • =IF(K2>Assumptions!$B$3,0,Assumptions!B$10*(Orders!C2+Orders!E2+Orders!G2))
• 
• Freeze the panes in cell b2
• Move to row 53 and insert a totals row.
• 
• Insert a new sheet, call it Supplies
  • Copy the labels from the cookie assumption table from the Assumptions sheet
  • 
  • For each cell b2:g4 calculate the number of containers of the ingredient needed for each type of cookie
    • For B2, this is the number of bags of flour needed for ginger snaps.
    • Multiply the number dozens of ginger snaps (G53 from the orders worksheet) by the number of cups of flour needed (f6 on the assumptions worksheet) divided by the size of a bag of flour (f10 on the assumptions worksheet)
    • =Orders!$G53*Assumptions!F6/Assumptions!F10
  • 
  • In row 6, calculate the total for each ingredient
    • Just the sum of the column above.
    • Round this up as you can not purchase a fraction of a container
  • Calculate the cost of each ingredient
    • The total containers you just calculated above
    • Multiplied by the cost of each container on the assumptions tab, row 11.
  • 
  • Finally in column H, compute the total cost for each type of cookie
    • This will be the amount of each ingredient times the cost for each ingredient.
    • =B2*Assumptions!$F$11+Supplies!C2*Assumptions!$G$11+Supplies!D2*Assumptions!$H$11+Supplies!E2*Assumptions!$I$11+Supplies!F2*Assumptions!$J$11+Supplies!G2*Assumptions!$K$11
    • This is somewhat painful to add, but we want this total.
    • 
• Add a sheet called Summary
  • Add the following labels
  • 
  • add formulas to copy the values for the various totals (B2:D4) from other sheets
  • 
  • Calculate the profit (total sales - cost)
  • And the profit margin (profit/total sales)
  • 
  • Draw a couple of useful graphs
  • 
  •