### Problem Sweet Dreams: Worksheet

Added on - 30 Sep 2019

5

pages

1783

words

109

views

0

downloads

ProblemSweet Dreams is open year round, but most of the candy shop's production revolves aroundthree holidays: Valentines' Day (1,000 lb.), Halloween (2,000 lb.), and Christmas (1,500 lb.). On thesedays, 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 youare 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 eachholiday, 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 ona holiday, subtotals for each holiday, subtotals for each candy style, and a grand total.Sound Spreadsheeting Practice1.Always create anassumptionssection to store any input data given.2.When creating a formula, avoid using any numeric input directly in the formula, unless you aresure it is a number that will never change (maintenance-free). Instead, refer to the cell in theassumptionssection where the number is stored.3.UseAbsolute Cell Referenceswherever possible to make formula development and maintenanceeasier.4.Use appropriate cell format throughout your worksheet (e.g., borders, numeric format, etc.).The Differences between Relative and Absolute Cell ReferencesRelative referencesA relative cell reference in a formula, such as=A1, is based on the relativeposition of the cell that contains the formula and the cell the reference refers to. If the position ofthe cell that contains the formula changes, the reference is changed. If you copy the formulaacross rows or down columns, the reference automatically adjusts. By default, new formulas userelative references. For example, if you copy a relative reference in cell A2 to cell B3, itautomatically adjusts from =A1 to =B2AB12=A13=B2Page1of5pages

Absolute referencesAn absolute cell reference in a formula, such as=$A$1, always refer toa cell in a specific location. If the position of the cell that contains the formula changes, theabsolute 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, andyou 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.AB12=$A$13=$A$1Mixed referencesA mixed reference has either an absolute column and relative row, orabsolute 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 ofthe cell that contains the formula changes, the relative reference is changed, and the absolutereference does not change. If you copy the formula across rows or down columns, the relativereference automatically adjusts, and the absolute reference does not adjust. For example, ifyou copy a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.AB12=A$13=B$1Sample SolutionSection 1ABCDE3Section 1: Assumptions4HolidayProduction (lb)Candy Style% of ProductionPrice5Valentines1,000Chocolate50%$ 5.006Halloween2,000Taffy30%$ 2.007Christmas1,500Others20%$ 1.00Section 2ABCDE9Section 2: Production Data10ValentinesHalloweenChristmasTotal11Chocolate5001,0007502,25012Taffy3006004501,35013Others200400300900Page2of5pages

**You’re reading a preview**

To View Complete Document

Become a Desklib Library Member.

Subscribe to our plans