Problem Sweet Dreams: Worksheet
5 Pages1783 Words291 Views
Added on 2019-09-30
Problem Sweet Dreams: Worksheet
Added on 2019-09-30
ShareRelated Documents
ProblemSweet 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 Practice1.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 ReferencesRelative 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 2 =A1 3 =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 referencein cell A2 to cell B3, it stays the same in both cells =$A$1. A B 1 2 =$A$13 =$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 SolutionSection 1 A B C D E 3Section 1: Assumptions 4Holiday Production (lb) Candy Style % of Production Price 5Valentines 1,000 Chocolate 50% $ 5.00 6Halloween 2,000 Taffy 30% $ 2.00 7Christmas 1,500 Others 20% $ 1.00 Section 2 A B C D E 9 Section 2: Production Data 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 Page 2 of 5 pages
End of preview
Want to access all the pages? Upload your documents or become a member.