The Nelson Company is planning a new division to make a specialised product. This is the only product that the division will make. As a financial analyst in the planning department, youve been asked to make the decision on whether this is a good idea or not. Sales will commence in Year 1, after installation on the final day of Year 0. The marketing department believes that first year revenue will be $1,800,000 rising to $7,000,000 in Year 8. The company will pay no tax if Earnings before Tax are negative.

The company will purchase equipment for $4 million, which will have to be replaced at the end of Year 8. Youve been asked to create a discounted cash flow analysis in order to calculate the projects net present value and internal rate of return. The company has a policy of only accepting projects with a return greater than 7.5%. Remember that a common financial shortcut to calculate free cash flow is to add depreciation to net income.

1. Open the Nelson worksheet.
2. In Cell F25 (Tax Rate) set up a data validation input message to read Tax Rate.
3. In Cell F27 (Discount Rate) set up a data validation so that the rate can only be between 0% and 8.0%; the Input Message must read Allowable Discount Rates; and the Error Message must read Wrong Number.
4. Use the fill function to complete the Revenue row from Year 1 to Year 8. Use the growth trend.
5. Using absolute cell references, 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 declining balance depreciation to complete row 12.
9. Complete the final three rows (13, 14, and 15). Remember the Company only pays taxes if Earnings before Tax is above $0.
10. In row19 determine the annual free cash flow. See the opening paragraphs for a hint on how to calculate cash flow.
11. In cells B21 and B23 determine the net present value (NPV) and internal rate of return (IRR) for the project.
12. Answer the question Would you invest and why? in cell F22 on the worksheet.
13. Format the worksheet so that it fits on 1 page with Landscape orientation.
14. Put your name in the right footer.
15. Save your workbook.

The Nelson Company is planning a new division to make a specialised product. This is the only product that the division will make. As a financial analyst in the planning department, youve been asked to make the decision on whether this is a good idea or not. Sales will commence in Year 1, after installation on the final day of Year 0. The marketing department believes that first year revenue will be $1,800,000 rising to $7,000,000 in Year 8. The company will pay no tax if Earnings before Tax are negative.

The company will purchase equipment for $4 million, which will have to be replaced at the end of Year 8. Youve been asked to create a discounted cash flow analysis in order to calculate the projects net present value and internal rate of return. The company has a policy of only accepting projects with a return greater than 7.5%. Remember that a common financial shortcut to calculate free cash flow is to add depreciation to net income.

1. Open the Nelson worksheet. 2. In Cell F25 (Tax Rate) set up a data validation input message to read Tax Rate. 3. In Cell F27 (Discount Rate) set up a data validation so that the rate can only be between 3.0% and 8.0%; the Input Message must read Allowable Discount Rates; and the Error Message must read Wrong Number. 4. Use the fill function to complete the Revenue row from Year 1 to Year 8. Use the growth trend. 5. Using absolute cell references, 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 declining balance depreciation to complete row 12. 9. Complete the final three rows (13, 14, and 15). Remember the Company only pays taxes if Earnings before Tax is above $0. 10. In row19 determine the annual free cash flow. See the opening paragraphs for a hint on how to calculate cash flow. 11. In cells B21 and B23 determine the net present value (NPV) and internal rate of return (IRR) for the project. 12. Answer the question Would you invest and why? in cell F22 on the worksheet. 13. Format the worksheet so that it fits on 1 page with Landscape orientation. 14. Put your name in the right footer. 15. Save your workbook.

Case 2 Local Vending (40 marks)

You are looking into the business done by the Coke machines leased by your company, National Distributing, which are placed in various locations in nearby businesses. The Coca-Cola Company does not charge you for the machines, only for the Coke products which you purchase. You are responsible for insurance and maintenance. The businesses do not charge you to place the machines in their locations. One full-time driver is hired in order to service these machines.

You are concerned that sales of Coke at your machines may not always stay at their current level. Alternatively, it is possible that sales may be higher in some months. You are interested in discovering how this would affect income. You would also like to see what happens to income if you raise the price charged per can, knowing this might lower your sales. You decide to explore all these different avenues by using the tools provided by Excel.

1. Open Vending worksheet. 2. The company has 12 machines that sell an average of 2,000 cans of Coke per month, for a total of 24,000 cans per month. In the Income Statement, input the data below and calculate the Total lines that are in Bold on the worksheet :

Revenue Units Sold 24,000 Price charged per Coke $1.50 Variable Expenses Cost per Coke $0.75 Fixed Expenses Maintenance $400.00 Insurance $1,000.00 Salary $2,000.00 Taxes $1,800.00

3. Use Goal Seek to calculate the Units Sold required to break even. In cells B25 to B27, enter the three cell references or numbers that you used in Goal Seek. In cell B28 enter the number of units that returns the breakeven value. Return the Units Sold to 24,000. 4. Create a one-variable data table in the shaded area E3:H13. Enter the appropriate labels in the top row to display the Units, Revenue, Total Expenses, and Net Income for Monthly Coke sales between 10,000 and 50,000 cans, in increments of 5,000. Complete the table with appropriate input cells, and format so that it is easy to read. 5. In the box below the data table (E16:K25), create a two-variable data table that displays Net Income for monthly Coke sales between 20,000 and 60,000 cans, in increments of 5,000 units in the column, and for Price Charged per Coke from $1.00 to $2.00 in increments of $0.20 in the top row. Complete the table with appropriate input cells, and format so that it is easy to read. 6. Using the custom number format, rename the cell at the top left of the table Units. 7. Assign names to the following cell ranges: B4:B5, B13:B16, B20:B22, using the text contained in column A to the left. 8. Set up four scenarios with the following data: Status Quo Raised Price Increase Sales Increase Sales & Raised Price Monthly units sold 24,000 19,000 28,500 31,000 Price per unit 1.50 2.00 1.00 2.00 Insurance 1000 400 600 600 Salary 2000 2000 2500 2500 Property Taxes 1800 1800 2100 2100

9. In the Comment box of Scenario Manager, enter your name and todays date. 10. Create a Scenario Summary Report, for the four scenarios above that shows total revenue, total expenses and net income. 11. Create a Scenario PivotTable that shows Total Revenue and Net Income. Format the values in the table with commas, no decimals. 12. In A31 answer the question: Which scenario do you recommend and why?

13. Save your workbook.

Case 3 Local Vending Part II (25 marks)

Your boss was happy with the work that you did in case two. Hes now interested in determining whether a different price per can would lead to higher sales and profits. WARNING: Do not change the formula in cell C4 your spreadsheet wont work if you do.

1. Open the worksheet Vending 2. 2. In the Forecast column C, enter the appropriate formulas in the Total cells highlighted in yellow. Do not enter anything in cell C5 at this time. 3. The companys Other Expenses will vary with the number of cans of Coke sold. Higher volumes of sales would lead to higher expenses. The lookup table in F2:G6 contains the information for Maintenance expenses; F8:G12 contains Insurance expenses; and, F15:G19 contains Salary expenses by volume sold. 4. Complete each of cells C13, C14, and C15 to determine the Maintenance, Insurance, and Salary expenses respectively. 5. Your boss would like to know the price per unit that will lead to the highest Net Income. The minimum price you can charge is $1.25 and the maximum is $2.25. In addition, you are contractually bound to purchase a minimum of 20,000 cans per year, while the maximum that Coke is able to supply is 50,000 cans. Use solver to determine the optimal price per can. 6. Save the Answer Report in a separate worksheet. 7. Answer the question in cell A26. 8. Save your workbook.

Case 4 Fatimas Popcorn (17 marks)

Fatima Ahmed established Fatimas Popcorn in Grand Bend, ON in 2016. She sells a variety of flavoured popcorn. When the COVID-19 pandemic hit, she was forced to develop an online presence in order to keep selling. Customers place their orders via the companys website and receive delivery in one to three days, depending on the shipping option they choose. Fatima wants to create a professional looking invoice she can use for each customer transaction. She 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 Online worksheet. 2. In the Shipping Cost Table, assign the defined name ShippingCost to the data in the range N4:O8. 3. Assign the defined name ProductPrice to the data in the Product Pricing table. 4. In the Item column (B16:B26), 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!. 5. In the Flavour column (E16:E26), use data validation to create a list of the items in the Flavours table. The input message title should be Flavour and the input message should read Select from Flavours Table. 6. In the first Price cell (G16), use the appropriate function to look up the price of the ordered item. 7. Modify the formula in G16 by adding an IFERROR function to the existing function to display either the price of the item or a blank cell if an error value occurs. Copy this formula to the bottom of the invoice. 8. In the Total column, enter a formula to calculate the total charge for that row. Use the IFERROR function to display either the total charge or a blank cell if an error value occurs. Copy this formula to the bottom of the invoice. 9. In the Subtotal cell, insert a formula to calculate the total charge for items ordered. Use the IFERROR function to display either the total charge or a blank cell if an error value occurs. 10. In the sales tax cell, enter a formula that will display the sales tax so if the customers province is ON, then calculate 13% of the subtotal. If the province is something other than ON, the sales tax will be 5% of the subtotal. 11. In the Shipping cell, use the IF and vlookup functions to determine the shipping cost. If the subtotal is 0, the shipping cost should be 0. 12. In the Total Due cell, enter the correct formula. 13. Enter the following data into the worksheet and check that it works: Item 1: Plain tin 2g Flavour: Cajun Quantity: 4 Item 2: Gourmet (3) 3g Flavour: Caramel Quantity: 3

14. Save your workbook.