In Bangzia, employees take Wednesdays off, and the following holidays: January 3, March 12, April 15, July 1, August 12, October 26 and December 3. Design a spreadsheet model in which the user can enter start and end dates, and the worksheet returns the total number of working days? Your model should work for end dates entered up to and including December 31, 2035. Use formulas to generate the dates of the holidays. The result should be blank if no dates are entered. 5 2. Last year’s sales of Merisedvir, a therapeutic drug developed by Homeland Pharmaceutics, were $18.2 million. The company anticipates an annual sales growth of 6% for Merisedvir. Currently, two other drug companies are developing equivalent drugs to Merisedvir. When the first competitor’s drug enters the marketplace, Homeland Pharmaceutics expects sales to decrease by 25%. When the second competitor’s drug is introduced, sales will decrease by an additional 5 million. Create an Excel spreadsheet that will compute the annual sales for the company up to 2035 allowing the user to enter the year each competitor enters the marketplace. 5 3. Barbara created an invoice in the 3 worksheet. Reposition this worksheet sequentially in your file. Provide your solution in this worksheet. She requires your assistance to complete the invoice. When Barbara enters an invoice date, she would like a message that will display the payment due date, such as “Please pay the balance due by December 1, 2020” in B26. The company sets the payment due date for invoices in accordance with its “Net 3 EONM” policy. The payment must be made in full within 3 days following the end of the next month from the invoice date. The formula should not display data if no invoice date is entered. 5 4. Rank the dataset records in the worldometers by Total Cases per 1 M Pop where the highest number is ranked first. Using the dataset, write a formula (in a separate worksheet) using the INDEX and MATCH functions and named ranges to display (based on a country and metric which the user of your worksheet chooses) the data for the request. For example, if the user chooses the country, Canada and the metric, Total Cases, the formula result should display 107,021. Set data validation restricting the selection of the country and metrix to those listed on the dataset. If no country or metric is selected, the result should not display #N/A. 10 3 | P a g e 5. Using the dataset in the worldometers, write formulas (in a separate worksheet) to provide the following information: a) In countries in which total cases were at least 100,000 cases, what is the average number of total deaths? b) How many total tests were conducted by countries beginning with "C"? c) How many countries have below average cases? 5 6. The sales worksheet displays data about unit sales for 10 products during the years 2010 to 2019. Rename the worksheet q6 and reposition this worksheet sequentially in your file. Write a formula using the MATCH and OFFSET functions that determines the total sales of a given product during a given range of years. 5 7. Gears Inc., based in Surrey, British Columbia, offers a comprehensive array of environmentally friendly bicycles. Gears Inc. takes pride in its implementation of clean energy in its manufacturing plant. Gears sells two types of bikes: mountain and road. Each mountain bicycle sells for $859 and road bicycle for $325. The per unit variable costs for a mountain bike include $275 in raw materials, $10.95 in packaging, $12.50 in manufacturing overhead and $9.95 in shipping. The variable costs for a road bike are $75 in raw materials, $7.95 in packaging, $12.13 in manufacturing overhead and $5.05 in shipping. Gears’ fixed costs include office rent of $85,000, office supplies of $25,000 and marketing expenses of $48,000. Create a spreadsheet that provides the income position of the company for the upcoming year. The company projects demand to be 500 for mountain bikes, and 800 for road bikes. Assuming a unit to unit sale, what is a more profitable product for Gears? Determine the break-even for each product, assuming that fixed expenses are allocated as follows: 60% for mountain bikes and 40% for road bikes. Provide a chart showing the break-even point for each product. 10 8. Sally Artisan Bakery makes a variety of organic bread. The bakery is open every day from 7 AM to 6 PM. At 4 PM each day, the bakery marks down its goods by 25%. It also offers a 10% discount every Sunday before 10 AM. Design a spreadsheet that displays the current date/time in one cell. Have your spreadsheet display the correct amount due when the user enters a sale amount, assuming no taxes will be charged. 5 9. Ranika Energy would like to record its machinery runtime in seconds. Design a spreadsheet model that accepts as inputs the start and end time in hours, minutes and seconds. Set the model to display the machine time in seconds. 5 10. EDrive Inc. is a manufacturing company that specializes in the manufacture of sustainable vehicles. The company began manufacturing its E-line sports coupe at the start of the workday on Tuesday of the current week. The production cycle consists of five processes. A process begins on the next working day after the previous process finishes. For example, Process 2 begins the next working day after Process 1. Assuming that working days are Tuesday to Saturday and there are no holidays, design a spreadsheet that accepts as inputs the duration (in days) of each of the five processes. The spreadshe