Use the dataset in the ev worksheet. Modify the worksheet as follows: . . . W...
Fantastic news! We've Found the answer you've been seeking!
Question:
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?
Expert Answer:
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
Posted Date:
Students also viewed these accounting questions
-
Calculate the average value of f(x) = x sec2x on the interval [0, /4]
-
Given the input wave forms shown in Figure 3.64, sketch the output, Q, of a D flip-flop. CLK D
-
Given the input wave forms shown in Figure 3.63, sketch the output, Q, of a D flip-flop. CLK D
-
which you describe and reflect on one of your favorite pop culture artifacts. Your goals will be simple: you will describe in detail a popular film, tv show, ad campaign, performer, song,...
-
What is the difference between an enterprise fund and an internal service fund?
-
StanfordBinet IQ Test scores are normally distributed with a mean score of 100 and a standard deviation of 16. a. Sketch the distribution of StanfordBinet IQ test scores. b. Write the equation that...
-
Six null hypotheses were tested, and the P-values were: a. Which hypotheses, if any, can be rejected at the = 0.05 level? b. Which hypotheses, if any, can be rejected at the = 0.01 level?...
-
Welcome Inns is a chain of motels serving business travelers in Arizona and southern Nevada. The chain has grown from one motel in 2007 to five motels. In 2010, the owner of the company decided to...
-
Required information Skip to question [The following information applies to the questions displayed below.] Daley Company prepared the following aging of receivables analysis at December 31. Total...
-
Gulf Uniform Supply, Inc. (GUS), a merchandising business, is organized as a corporation. The business sells a complete line of uniforms for a variety of businesses and organizations, including...
-
The following accounting information relates to Company A and Company B, competing in the same industry: Cash Short-term investments Accounts receivable Inventory Accounts payable Salaries payable...
-
A. What are informational interviews? How can you prepare to conduct an effective informational interview? B. Aside from informational interviews, what other principles for professional networking...
-
Suppose that conflict over international trade leads to a fall in consumer confidence. Starting with the economy in long-run equilibrium, use the aggregate demandaggregate supply framework to...
-
Form a group of three to four classmates. Read one anothers rsums, spending three to five minutes per rsum. As you read, focus on how well the person communicates key abilities and attributes....
-
Redo Exercise 11.44, this time assuming Et = 100 + 0.8Yt + 0.5Yt1. Exercise 11.44 Consider this income-expenditure model: where Y is aggregate income and E is aggregate spending. Is this model...
-
Where must revenue legislation originate?
-
An entity enters into a contract to provide monthly payroll processing services to a customer for one year for a monthly retainer fee of P100,000. Requirement: Identify if the performance obligation...
-
For a nonzero constant a, find the intercepts of the graph of (x 2 + y 2 ) 2 = a 2 (x 2 - y 2 ). Then test for symmetry with respect to the x-axis, the y-axis, and the origin.
-
Using the final model from Task 6, what are the probabilities that participants 12, 53 and 75 will use a condom? Task 6 An HIV researcher explored the factors that influenced condom use with a new...
-
Which problem associated with logistic regression might we have in the analysis in Task 10? Task 10 At the start of the chapter we looked at whether the type of instrument a person plays is connected...
-
At the start of the chapter we looked at whether the type of instrument a person plays is connected to their personality. A musicologist measured extroversion and agreeableness in 200 singers and...
Study smarter with the SolutionInn App