Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

Unlock your academic potential

Â© 2024 Â | Â Zucol Services PVT LTD Â | Â All rights reserved.

Added on Â 2019/09/30

|5

|1783

|291

Report

AI Summary

The assignment content appears to be related to a business or financial analysis, specifically focusing on production data and potential sales value. It provides formulas for calculating various values such as total production, average production, and predicted expenses, as well as examples of using the IF function to evaluate conditions and return specific values. The content also includes explanations of how to use the IF function, including its syntax and the ability to nest up to seven functions.

Your contribution can guide someoneâ€™s learning journey. Share your
documents today.

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

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

Need help grading? Try our AI Grader for instant feedback on your assignments.

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

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

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

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

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

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

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

1 out of 5