You and your fellow classmate have come up with an idea for a sent-free hand sanitizer. This idea came as a result of you having a constant headache from all of the different hand sanitizer smells in stores right now due to the Pandemic. You have secured a manufacturing location, the required equipment, a supplier for your business, Nosmell Sani Inc. and you plan to start manufacturing the sent-free hand sanitizer once you complete this Fall 2020 term. You are your classmate decide at this stage of your business, you will simply operate as two sole proprietors. To ensure Nosmell Sani Inc.s success you and your classmate have started to prepare a Business Plan. You are now working on the financial section and you must prepare the projected financial statements, cash budget schedules and supporting schedules.

Required: Excel spreadsheet WITH FORMULAS to complete the following financial information schedules.

1. Prepare the Opening Balance Sheet as at December 31, 2020 Hint: this will include the capital and equipment you invest to start your business

2. Monthly Cashflow Projections for the twelve months (January 2021 to December 2021) Hint: follow these steps to calculate the sale price required to generate the businesss target profit: 1. Prepare monthly cash flow projections. 2. Determine the amount of positive cashflow required in the first year. 3. Using the estimated sales price and unit sales (PROJECTED AT $80 PER UNIT/ 120 UNITS) will you generate enough cash inflow to cover costs and meet the target profit? You may need to manipulate or adapt selling price or unit projections accordingly.

3. Loan Amortization Schedule to calculate interest accrual on the annual loan.

4. PPE Depreciation Schedule Hint: Depreciation is a NON-CASH ITEM

5. Proforma Income Statement for the period ended December 31, 2021.

6. Proforma Balance Sheet at December 31, 2021 Hint: Proforma = Projected

Additional information:

December 31, 2020 To start Nosmell Sani Inc., you invest $75,000 cash and your personal computer valued at $4,000 (as at Dec. 31, 2020). The estimated life of the computer is 5 years.

Projected events during the upcoming year:

The 5,000 sq ft manufacturing location you have leased has a cost is $15 per square feet triple net. Triple net cost are $10 per square feet.

You plan to purchase the manufacturing equipment required at a New Years Day sales on January 1, 2021. Unfortunately, it cannot be installed February 1, 2021. The cost of the equipment is $20,000. Nosmell Sani Inc. will pay $5,000 in cash and secure financing for the remaining $15,000. The loan terms are 8% for a 10-year loan. The estimated life of the manufacturing equipment is 10 years. Hint: Use the PMT Function in excel to calculate the monthly repayment.

You plan to start manufacturing the hand sanitizer on the day the equipment is installed, February 1, 2021 with the hopes that the first batch will be ready to ship mid-month.

Nosmell Sani Inc. will have: an annual advertising budget of $30,000 an annual administration wages are $40,000

variable wages of $2 per bottle of hand sanitizer produced (including benefits)

direct material costs of $3 bottle of hand sanitizer produced

annual business insurance is $2,000. Insurance policy is from Jan 2021 Dec 2021

annual utilities costs are $10,000. Note these costs tend to increase in the winter months.

monthly internet and phone cost are $200

monthly vehicle lease is $400 per month, anticipated annual fuel cost is $4,000

Initial website development cost is $2,000 paid January 1, 2021 with an estimated life of 5 years. Subsequent monthly web hosting fees are $100

PLEASE SHOW FORMULAS USED IN EXCEL.