Sweet Dreams: Holiday Candy Production, Sales Value Worksheet Analysis

This assignment focuses on creating a worksheet to analyze the candy production and potential sales value for Sweet Dreams, a candy shop. The shop's production is centered around three holidays: Valentine's Day, Halloween, and Christmas, with specific production quantities for each. The worksheet is divided into three sections: Assumptions, Production Data, and Potential Sales Value. The Assumptions section documents the given data, including holiday production amounts, candy style proportions (boxed chocolate, taffy, and holiday-specific candy), and selling prices per pound for each candy style. The Production Data section calculates the quantity of each candy style produced on each holiday and the total production. The Potential Sales Value section calculates the potential sales value for each candy style on a holiday, with subtotals for each holiday, candy style, and a grand total. The solution emphasizes the use of absolute cell references and sound spreadsheet practices. The assignment also provides an example of using the IF logical function for conditional output.

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 B
1
Page 1 of 5 pages

2 =A1
3 =B2
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 B
1
2 =$A$1
3 =$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 B
1
2 =A$1
3 =B$1
Sample Solution
Section 1
A B C D E
3 Section 1: Assumptions
4 Holiday Production (lb) Candy Style % of Production Price
5 Valentines 1,000 Chocolate 50% $ 5.00
6 Halloween 2,000 Taffy 30% $ 2.00
7 Christmas 1,500 Others 20% $ 1.00
Section 2
A B C D E
9 Section 2: Production Data
Page 2 of 5 pages

10 Valentines Halloween Christmas Total
11 Chocolate 500 1,000 750 2,250
12 Taffy 300 600 450 1,350
13 Others 200 400 300 900
14 Total 1,000 2,000 1,500 4,500
Section 2 Fomulas
A B C D E
9 Section 2: Production Data
10 Valentines Halloween Christmas Total
11 Chocolate =$B$5*$D5 =$B$6*$D5 =$B$7*$D5 =SUM(B11:D11)
12 Taffy =$B$5*$D6 =$B$6*$D6 =$B$7*$D6 =SUM(B12:D12)
13 Others =$B$5*$D7 =$B$6*$D7 =$B$7*$D7 =SUM(B13:D13)
14 Total =SUM(B11:B13) =SUM(C11:C13) =SUM(D11:D13) =SUM(B14:D14)
Section 3
A B C D E
16 Section 3: Potential Sales Value
17 Valentines Halloween Christmas Total
18 Chocolate $ 2,500.00 $ 5,000.00 $ 3,750.00 $ 11,250.00
19 Taffy $ 600.00 $ 1,200.00 $ 900.00 $ 2,700.00
20 Others $ 200.00 $ 400.00 $ 300.00 $ 900.00
21 Total $ 3,300.00 $ 6,600.00 $ 4,950.00 $ 14,850.00
Section 3 Formulas
A B C D E
16 Section 3: Potential Sales Value
17 Valentines Halloween Christmas Total
18 Chocolate =B11*$E5 =C11*$E5 =D11*$E5 =SUM(B18:D18)
19 Taffy =B12*$E6 =C12*$E6 =D12*$E6 =SUM(B19:D19)
20 Others =B13*$E7 =C13*$E7 =D13*$E7 =SUM(B20:D20)
21 Total =SUM(B18:B20) =SUM(C18:C20) =SUM(D18:D20) =SUM(B21:D21)
Extra: Using the Logical Function IF() (adapted from Microsoft Excel Online
Help)
Page 3 of 5 pages

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
A B
1 Actual Expenses Predicted Expenses
2 1500 900
3 500 900
4 500 925
5 Formula Description (Result)
6 =IF(A2>B2,"Over
Budget","OK")
Checks whether the first row is over
budget (Over Budget)
7 =IF(A3>B3,"Over
Budget","OK")
Checks whether the second row is over
budget (OK)
Example 2
A B
1 Score
2 45
3 90
4 78
5 Formula Description (Result)
Page 4 of 5 pages

6 =IF(A2>89,"A",IF(A2>79,"B",
IF(A2>69,"C",IF(A2>59,"D","F"))))
Assigns a letter grade to the first
score (F)
7 =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)
8 =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 Then return
Greater than 89 A
From 80 to 89 B
From 70 to 79 C
From 60 to 69 D
Less than 60 F
Page 5 of 5 pages