Use the dataset in the ev worksheet. Modify the worksheet as follows: . . . W...

       

Transcribed Image Text:

Use the dataset in the ev worksheet. Modify the worksheet as follows: . . . W Rename the worksheet q1a and position it sequentially in your workbook. Include descriptive statistics (using Excel's analysis tool) starting in cell B5 analyzing the Jan figures. There may be multiple modes in the Sep values. Starting in E5, use the appropriate function to display these modes. Use the appropriate statistical function that will display the frequency of Sep values based on the bin sizes of 0, 20000, 40000, 60000 starting in G5. Create a new worksheet named q1h and position it sequentially in the workbook. Include your answers to the following in the new worksheet: Write formulas using the MATCH and INDEX functions and named ranges to determine the figure for a given product and a given month. Create dată validation lists for the user to choose a product and month. For example, if the user selects the product Trio EV and the month May, the formula result should display $96,017.66. If no product or month is selected, the result should not display #N/A Last year's sales of the Jolt electric scooter were 12,000 units. The company anticipates an annual sales growth of 10%. Currently, one competitor is developing an equivalent scooter. In the year that the competitor's scooter enters the marketplace, sales of the Jolt scooter is expected to decrease by 20%. Create an Excel spreadsheet that will compute the annual sales for the current and next 9 years. Your spreadsheet should allow the user to enter the date (not year) the competitor enters the marketplace, and your sales computations should reflect the change in sales when the competitor enters. Barkley Inc. based in Langley, British Columbia, offers a comprehensive array of environmentally friendly cleaning products. The company takes pride in its implementation of clean energy in its manufacturing plant. Barkley sells two popular cleaning products: Eco-Friendly Cleaning Vinegar and Eco-Friendly Laundry Detergent. Each Eco-Friendly Cleaning Vinegar sells for $12.95 and Eco-Friendly Laundry Detergent for $21.95. The per unit variable costs for a bottle of Eco-Friendly Cleaning Vinegar include $1.50 in raw materials, $0.55 in packaging, $1.05 in manufacturing overhead and $0.16 in shipping. The variable costs for a bottle of Eco-Friendly Laundry Detergent are $7.95 in raw materials, $1.95 in packaging, $2.15 in manufacturing overhead and $0.35 in shipping. Barkley fixed costs include office rent of $85,000, office supplies of $24,000 and marketing expenses of $15,000. • Create a spreadsheet that provides the income position of the company for the upcoming year. The company projects unit sales of 7,500 for their Eco-Friendly Cleaning Vinegar and 10,000 for their Eco-Friendly Laundry Detergent. Assuming a unit-to-unit sale, what is a more profitable product for Barkley? • Determine the break-even for each product, assuming that fixed expenses are allocated equally between the two products. Provide a chart showing the break-even point for each product. Yummy Cupcakes is famous for its delicious cupcakes featuring five flavors (vanilla, chocolate, red velvet, and coconut). A box of a dozen cupcakes sells for $36. After 4 PM on weekdays (Mon to Fri), the cupcakes sell for 25% less. Design a spreadsheet that allows the user to enter the following: the date of the sale in one cell the time of sale in another cell the quantity (in boxes) in a third cell • . In the fourth cell, include a formula that will compute the total amount of the transaction • Apply appropriate labels and formatting. Rename the sales worksheet q5 and position it sequentially in your workbook. In the renamed worksheet, write formulas using the appropriate summary statistical functions (countif, sumif, averageif...) to provide the following information: a) What are the average sales (all products) in Ontario (ON)? b) How many BC cities are on the list? c) What are the average desktop sales for locations with a below-average desktop sales? d) How many cities have "port" in their city name? e) Total the laptop sales for locations with monitor sales below $100,000 that also have keyboard sales below $5,000? Use the dataset in the ev worksheet. Modify the worksheet as follows: . . . W Rename the worksheet q1a and position it sequentially in your workbook. Include descriptive statistics (using Excel's analysis tool) starting in cell B5 analyzing the Jan figures. There may be multiple modes in the Sep values. Starting in E5, use the appropriate function to display these modes. Use the appropriate statistical function that will display the frequency of Sep values based on the bin sizes of 0, 20000, 40000, 60000 starting in G5. Create a new worksheet named q1h and position it sequentially in the workbook. Include your answers to the following in the new worksheet: Write formulas using the MATCH and INDEX functions and named ranges to determine the figure for a given product and a given month. Create dată validation lists for the user to choose a product and month. For example, if the user selects the product Trio EV and the month May, the formula result should display $96,017.66. If no product or month is selected, the result should not display #N/A Last year's sales of the Jolt electric scooter were 12,000 units. The company anticipates an annual sales growth of 10%. Currently, one competitor is developing an equivalent scooter. In the year that the competitor's scooter enters the marketplace, sales of the Jolt scooter is expected to decrease by 20%. Create an Excel spreadsheet that will compute the annual sales for the current and next 9 years. Your spreadsheet should allow the user to enter the date (not year) the competitor enters the marketplace, and your sales computations should reflect the change in sales when the competitor enters. Barkley Inc. based in Langley, British Columbia, offers a comprehensive array of environmentally friendly cleaning products. The company takes pride in its implementation of clean energy in its manufacturing plant. Barkley sells two popular cleaning products: Eco-Friendly Cleaning Vinegar and Eco-Friendly Laundry Detergent. Each Eco-Friendly Cleaning Vinegar sells for $12.95 and Eco-Friendly Laundry Detergent for $21.95. The per unit variable costs for a bottle of Eco-Friendly Cleaning Vinegar include $1.50 in raw materials, $0.55 in packaging, $1.05 in manufacturing overhead and $0.16 in shipping. The variable costs for a bottle of Eco-Friendly Laundry Detergent are $7.95 in raw materials, $1.95 in packaging, $2.15 in manufacturing overhead and $0.35 in shipping. Barkley fixed costs include office rent of $85,000, office supplies of $24,000 and marketing expenses of $15,000. • Create a spreadsheet that provides the income position of the company for the upcoming year. The company projects unit sales of 7,500 for their Eco-Friendly Cleaning Vinegar and 10,000 for their Eco-Friendly Laundry Detergent. Assuming a unit-to-unit sale, what is a more profitable product for Barkley? • Determine the break-even for each product, assuming that fixed expenses are allocated equally between the two products. Provide a chart showing the break-even point for each product. Yummy Cupcakes is famous for its delicious cupcakes featuring five flavors (vanilla, chocolate, red velvet, and coconut). A box of a dozen cupcakes sells for $36. After 4 PM on weekdays (Mon to Fri), the cupcakes sell for 25% less. Design a spreadsheet that allows the user to enter the following: the date of the sale in one cell the time of sale in another cell the quantity (in boxes) in a third cell • . In the fourth cell, include a formula that will compute the total amount of the transaction • Apply appropriate labels and formatting. Rename the sales worksheet q5 and position it sequentially in your workbook. In the renamed worksheet, write formulas using the appropriate summary statistical functions (countif, sumif, averageif...) to provide the following information: a) What are the average sales (all products) in Ontario (ON)? b) How many BC cities are on the list? c) What are the average desktop sales for locations with a below-average desktop sales? d) How many cities have "port" in their city name? e) Total the laptop sales for locations with monitor sales below $100,000 that also have keyboard sales below $5,000?

Answer rating: 100% (QA)

Based on your instructions I have created a sample workbook that you can download and use as a reference Here is the link to the workbook Excel Data Analysis and Spreadsheet Design1 In the workbook yo... View the full answer