$1.$ Amy Zhao is a financial analyst with MedSight Instruments, a global manufacturer of medical instruments and devices. The MedSight Auditory Instruments Division is planning to manufacture and market an innovative hearing aid suitable for all ages. Amy is using an Excel workbook to estimate the sales, expenses, and profits of the new device.

Switch to the Unit Sales Projections worksheet. Use the values in the range C$5$:D$5$ to fill the range E$5$:I$5$ with a series of incremented percentages.

$2.$ Use the values and formatting in the range B$6$:B$7$ to extend the list of years to the range B$8$:B$10$ and project five years of growth.

$3.$ Use AutoFill to fill the range C$8$:I$10$ with the formatting from the range C$6$:I$7$ to apply a consistent format in the projections.

$4.$ Enter formulas as follows to complete the table of projections in the range C$6$:I$10$:

a$.$ In cell C$7,$ create a formula without using a function that multiplies the sales projection for the first year $($cell C$6)$ by the first growth rate $($cell C$5),$ and adds the sales projection for the first year $($cell C$6)$ to the result to determine the first estimate for $2025.$

b$.$ Use an absolute reference to row $5$ in the reference to cell C$5.$

c$.$ Copy the formula from cell C$7$ to the range C$8$:C$10,$ and then copy the formulas from the range C$7$:C$10$ to the range D$7$:I$10$ to complete the table of projections.

$5.$ Switch to the Cost Analysis worksheet, which analyzes the vendor and shipping costs per unit to select a vendor and shipper for the new product. In cell B$10,$ find the average vendor cost per unit by creating a formula using the AVERAGE function to calculate the average of the values in the range B$5$:B$8.$ Copy the formula to cell E$10$ to find the average shipping cost per unit.

$6.$ In cell B$11,$ find the highest vendor cost per unit by creating a formula using the MAX function to identify the maximum value in the range B$5$:B$8.$ Copy the formula to cell E$11$ to find the highest shipping cost per unit.

$7.$ In cell B$12,$ find the lowest vendor cost per unit by creating a formula using the MIN function to identify the minimum value in the range B$5$:B$8.$ Copy the formula to cell E$12$ to find the lowest shipping cost per unit.

$8.$ In cell C$14,$ identify the vendor with the lowest cost per unit by creating a formula using the VLOOKUP function to look up the value from cell B$12($the lowest vendor cost$)$ in the range B$5$:C$8($the vendor cost per unit table$),$ return the name of the vendor $($column $2),$ and specify an exact match. Copy the formula to cell F$14$ to identify the shipper with the lowest cost per unit.

$9.$ Switch to the Profit Projections worksheet, which sets a schedule for developing and launching the new product and projects its profits for five years. In cell I$7,$ use the TODAY function to insert the current date.

$10.$ In cell I$9,$ create a formula that uses the NETWORKDAYS function to calculate the number of working days between the dates in cells I$7$ and I$8.$ Leave the Holidays argument blank.

$11.$ Use the values in the range C$14$:D$14$ to fill the range E$14$:I$14$ with a series of incremented percentages.

$12.$ Use the values in the range B$15$:B$16$ to extend the list of years to the range B$17$:B$19$ for five years of projections.

$13.$ Estimate the gross profit for $2024$ as follows:

a$.$ In cell C$15,$ create a formula without using a function that subtracts the vendor cost $($cell C$7)$ and the shipping cost $($cell C$9)$ from the sales price $($cell C$8),$ and then multiplies the result by the projected number of units for sale in $2024($cell C$10).$

b$.$ Use absolute references to each cell in the formula.

c$.$ Copy the formula from cell C$15$ to the range D$15$:I$15$ to complete the estimates for $2024.$

$14.$ Complete the growth estimates for the years $2025$ to $2028$ as follows:

a$.$ In cell C$16,$ create a formula that adds $1$ to the growth rate $($cell C$14)$ and multiplies the result by the estimated profit from $2024($cell C$15).$

b$.$ Use an absolute row reference to cell C$14.$

c$.$ Copy the formula from cell C$16$ to the range C$17$:C$19$ to enter the first estimates for $20262028.$

d$.$ Copy the formulas from the range C$16$:C$19$ to the range D$16$:I$19$ to enter the remaining sales projections.

$15.$ Delete row $21$ since the worksheet is now complete.

Your workbook should look like the Final Figures on the following pages. The values in cells I$7$ and I$9$ of the Profit Projections worksheet have been intentionally blurred as they will never be constant. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.MedSight Auditory Instruments

Unit Sales Projections