You will have a number of different assignments for this [very] small business, consisting for now of a single lemonade stand that sells lemonade by the glass for $3.00 or sweet tea for $2.50. We will see the way in which expansion of the business requires new types of data and new types of record keeping, and what that means for how we would need to handle the data in a spreadsheet or, perhaps, in a structured database.

Instructions for individual required elements follow:

1. Create formula in cell D6 that uses the quantity of lemonade for the first month and the price of lemonade to calculate that month's overall sales from lemonade. Use relative and absolute references appropriately so that this function can at least be copied down column D to find the overall lemonade sales for each month in the data. Ideally, you would use a partial absolute reference / "mixed reference" to lock in only the row and not the column when referencing the price so that the formula can also be copied over to column E [and then down] without having to type new formula into E6. However you go about it, fill out all of column D and E for the 12 months of the year.
2. Create formula in F6 to find the total combined sales in each month from both lemonade and sweet tea, and fill it in down column F.
3. Using a rate of 4.45% (0.0445), create formula in G6 to find the sales tax due on total sales in each month, and fill it down column G. Ideally, you would follow best practice here of putting the "constant" sales tax rate first in its own designated cell on the sheet, then referencing it, rather than typing the number directly into your formula.
4. Use the RANK function to find the sales rank for each of the first 6 months by entering an appropriate formula in cell H6 and filling down through June. As the range for comparison, use only those first 6 months. Hint: You will want to ensure that you use an absolute reference here to the range so that it remains "locked" in as the same 6-month window.
5. Use the auto-sum feature (on the "Home" tab in the ribbon) to enter grand totals at the bottom of columns B through G (lemonade through sales tax).
6. Use the COUNTIF function with a numeric comparison (following double-quote syntax: e.g. ">10") to create formula in cell D20 which will indicate the number of months a total sales (column F) target of $500 was achieved.
7. Change the parameters as indicated for items 7-9 and note (as a number entered directly into the indicated cell, not using a formula) the requested outcome.

Transcribed Image Text:

Little Sally's Lemonade Stand Lemonade Drink Prices: Month January 2023 February 2023 March 2023 April 2023 May 2023 June 2023 July 2023 August 2023 September 2023 October 2023 November 2023 December 2023 Grand Total $3.00 Sweet Tea 107 104 125 147 151 143 $2.50 2 Lemonade Quantity Sweet Tea Quantity Lemonade Sales Sweet Tea Sales Total Sales 41 47 62 73 68 75 Number of months to date meeting sales target of $500.00: What would total sales have been if Sweet Tea were $3.00? How many months on target if Sweet Tea had been $3.00? How many on target if Lemonade was $3.50 and Tea $3.00? 6 7 8 9 Sales Tax Due Sales Rank Little Sally's Lemonade Stand Lemonade Drink Prices: Month January 2023 February 2023 March 2023 April 2023 May 2023 June 2023 July 2023 August 2023 September 2023 October 2023 November 2023 December 2023 Grand Total $3.00 Sweet Tea 107 104 125 147 151 143 $2.50 2 Lemonade Quantity Sweet Tea Quantity Lemonade Sales Sweet Tea Sales Total Sales 41 47 62 73 68 75 Number of months to date meeting sales target of $500.00: What would total sales have been if Sweet Tea were $3.00? How many months on target if Sweet Tea had been $3.00? How many on target if Lemonade was $3.50 and Tea $3.00? 6 7 8 9 Sales Tax Due Sales Rank