Use the fill function to complete the Revenue row from Year $1$ to Year $8.$ Use the growth

trend.

$5.$ Using absolute cell references to help you, calculate the cost of goods sold for all years.

$6.$ Calculate the Gross Profit.

$7.$ Use the fill function with a linear trend to complete the Operating Cost row.

$8.$ Use the appropriate function for straight line depreciation to complete this row.

$9.$ Complete the final three rows $(20,21,$ and $22).$ Remember that the Company will pay no

tax if earnings before tax are negative.

$10.$ In row $26$ determine the annual free cash flow. See opening paragraph for hint.

$11.$ In the appropriate cells $($B$28$ and B$30)$ determine the net present value $($NPV$)$ and

internal rate of return $($IRR$)$ for the project.

$12.$ Answer the question in cell F$29$ on the worksheet.

$13.$ Format the worksheet so that it fits on $1$ page with Landscape orientation.

$14.$ Put your name in the left footer.

$15.$ Save your worksheet.

Case $3$ Bobby$$s Popcorn $(19$ marks$)$

Bobby Nolan established Bobby$$s Popcorn in Hawthorn, NV in $2016.$ He ships flavoured

popcorn in standard flavours such as plain and Kettle corn and gourmet flavours such as grape

and orange. Customers place their orders via the company$$s website and receive delivery in one

to three days, depending on the shipping option they choose. Bobby wants to create a

professional looking invoice he can use for each customer transaction. Bobby created the layout

and wants you to add formulas to calculate the price per item, sales tax, shipping and invoice

total based on existing tables for pricing and shipping.

$1.$ Open the Popcorn worksheet.

$2.$ In the $$Shipping Cost Table$,$ assign the defined name ShippingCost to the data in the

range N$4$:O$8.$

$3.$ In the Item column $($B$16$:B$26),$ use data validation to create a list of the items in the

Product Pricing table. The input message should read $$Select from Product Pricing

Table$$ and the Error message should read $$You made a mistake!$.$

$4.$ In the first Price cell $($G$16),$ use the appropriate function to look up the price of the

ordered item.

$4$