Excel Lab Notes

IS 312  Excel Lab Notes (This is NOT part of the project) Problem Sweet Dreams is open year round, but most of the candy shop' s production revolves around three holidays: Valentines' Day (1,000 lb.), Halloween (2,000 lb.), and Christmas (1,500 lb.). On these days, 50% of the store' s output is boxed chocolate, 30% is taffy, and the remaining 20% is holiday-specific candy. The boxed chocolate sells for $5 per pound, the taffy for $2 per pound, and holiday-specific candy for $1 per pound. Sweet Dreams is considering revising its production figures, and you are asked to prepare a worksheet as specified below. The worksheet should contain three sections, each with appropriate headings. Section 1: Assumptions. Record all the assumptions as given above: total production for each holiday, proportions of each style of candy produced, and selling prices. Section 2: Production Data. Calculate, in pounds, each style of candy produced on a holiday, and the total amount of candy produced for the three holidays. Section 3: Potential Sales Value. Calculate the potential sales value for each candy style on a holiday, subtotals for each holiday, subtotals for each candy style, and a grand total. Sound Spreadsheeting Practice 1. Always create an assumptions section to store any input data given. 2. When creating a formula, avoid using any numeric input directly in the formula, unless you are sure it is a number that will never change (maintenance-free). Instead, refer to the cell in the assumptions section where the number is stored. 3. Use Absolute Cell References wherever possible to make formula development and maintenance easier. 4. Use appropriate cell format throughout your worksheet (e.g., borders, numeric format, etc.). The Differences between Relative and Absolute Cell References Relative references A relative cell reference in a formula, such as =A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy a relative reference in cell A2 to cell B3, it automatically adjusts from =A1 to =B2 A 1 2 3 B =A1 =B2 Page 1 of 5 pages Absolute references An absolute cell reference in a formula, such as =$A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you need to switch them to absolute references. For example, if you copy an absolute reference in cell A2 to cell B3, it stays the same in both cells =$A$1. A 1 2 3 B =$A$1 =$A$1 Mixed references A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1. A 1 2 3 B =A$1 =B$1 Sample Solution Section 1 A 3 4 Holiday 5 Valentines 6 Halloween 7 Christmas B C D E Section 1: Assumptions Production (lb) 1,000 2,000 1,500 Candy Style Chocolate Taffy Others Page 2 of 5 pages % of Production 50% 30% 20% Price $ 5.00 $ 2.00 $ 1.00 Section 2 A B D E Section 2: Production Data 9 10 11 12 13 14 C Chocolate Taffy Others Total Valentines 500 300 200 1,000 Halloween 1,000 600 400 2,000 Christmas 750 450 300 1,500 Total 2,250 1,350 900 4,500 Section 2 Fomulas A B D E Section 2: Production Data 9 10 11 12 13 14 C Valentines Halloween Christmas Total Chocolate Taffy Others Total =$B$5*$D5 =$B$5*$D6 =$B$5*$D7 =SUM(B11:B13) =$B$6*$D5 =$B$6*$D6 =$B$6*$D7 =SUM(C11:C13) =$B$7*$D5 =$B$7*$D6 =$B$7*$D7 =SUM(D11:D13) =SUM(B11:D11) =SUM(B12:D12) =SUM(B13:D13) =SUM(B14:D14) A B C D E Section 3 Section 3: Potential Sales Value 16 17 18 19 20 21 Valentines Chocolate Taffy Others Total $ 2,500.00 $ 600.00 $ 200.00 $ 3,300.00 Halloween $ 5,000.00 $ 1,200.00 $ 400.00 $ 6,600.00 Christmas $ 3,750.00 $ 900.00 $ 300.00 $ 4,950.00 Total $ 11,250.00 $ 2,700.00 $ 900.00 $ 14,850.00 Section 3 Formulas A C D E Section 3: Potential Sales Value 16 17 18 19 20 21 B Chocolate Taffy Others Total Valentines Halloween Christmas Total =B11*$E5 =B12*$E6 =B13*$E7 =SUM(B18:B20) =C11*$E5 =C12*$E6 =C13*$E7 =SUM(C18:C20) =D11*$E5 =D12*$E6 =D13*$E7 =SUM(D18:D20) =SUM(B18:D18) =SUM(B19:D19) =SUM(B20:D20) =SUM(B21:D21) Page 3 of 5 pages Extra: Using the Logical Function IF() (adapted from Microsoft Excel Online Help) The logical function IF() lets you choose among alternative output values based on some test conditions. It choose one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Syntax IF(logical_test, value_if_true, value_if_false) Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. Value_if_true is the value that is selected if logical_test is TRUE. For example, if this argument is the text string "Success" and the logical test evaluates to TRUE, then the IF function displays the text " Success. " Value_if_true can also be another formula or function. Value_if_false is the value that is selected if logical_test is FALSE. For example, if this argument is the text string "Failure" and the logical_test evaluates to FALSE, then the IF function displays the text " Failure. " Value_if_false can also be another formula or function. Remarks Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (See Example 2 for a sample of nested IF functions.) If a value is text (known to Excel as a character string), place it between quotation marks. If a value is a number, do NOT use quotation marks. Example 1 1 2 3 A Actual Borrower FICO Score 750 720 4 680 5 6 Approve Formula =IF(A2>=$B$2,"Approve","Decline") 7 Approve =IF(A3>=$B$2,"Approve","Decline") 8 Decline =IF(A4>=$B$2,"Approve","Decline") Result B Minimum FICO Score Required 720 Page 4 of 5 pages Example 2 1 2 3 4 5 6 7 8 A Score B 45 90 78 Formula =IF(A2>89,"A",IF(A2>79,"B",IF(A2>69,"C",IF(A2>59,"D","F")))) Description (Result) Assigns a letter grade to the first score (F) =IF(A3>89,"A",IF(A3>79,"B",IF(A3>69,"C",IF(A3>59,"D","F")))) Assigns a letter grade to the second score (A) =IF(A4>89,"A",IF(A4>79,"B",IF(A4>69,"C",IF(A4>59,"D","F")))) Assigns a letter grade to the third score (C) In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on. The letter grades are assigned to numbers using the following key. If Score is Greater than 89 From 80 to 89 From 70 to 79 From 60 to 69 Less than 60 Then return A B C D F Page 5 of 5 pages