Required: Excel spreadsheet templates have been provided to assist you with completing the following financial information schedules. 1. Prepare the Opening Balance Sheet as at December 31, 2020 (Chapter 10 - Table 10.4 & 10.5) 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 business's 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 projections from the Marketing Section of the business plan, 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. (Tbl 10.6 & 10.7) 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. (Tbl 10.8 & 10.9) 6. Proforma Balance Sheet at December 31, 2021 Hint: Proforma = Projected Additional information: cal cations 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. 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 you learned in BUAD 128, Computer Applications to calculate the monthly repayment. It is found under Formulas in Excel. 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 Individual Assignment #5 - Financial Assignment 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. Proforma Balance Sheet Date ASSETS Current Assets Cash Accounts Receivable Inventory Total Current Assets Property Plant & Equipment Equipment Less accumulated deprecitation (equipment) Computer Less accumulated deprecitation (computer) Website Less accumulated deprecitation (website) Total Property Plant & Equipment Total Assets Serpen van de opleide 1 LIABILITIES & EQUITY Liabilities Accounts Payable Equipment Loans Balance Line of Credit Balance Total Liabilities Equity Increase in Owners Equity Owners Equity Owners Draw Total Equity Total Liabilities and Equity Required: Excel spreadsheet templates have been provided to assist you with completing the following financial information schedules. 1. Prepare the Opening Balance Sheet as at December 31, 2020 (Chapter 10 - Table 10.4 & 10.5) 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 business's 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 projections from the Marketing Section of the business plan, 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. (Tbl 10.6 & 10.7) 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. (Tbl 10.8 & 10.9) 6. Proforma Balance Sheet at December 31, 2021 Hint: Proforma = Projected Additional information: cal cations 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. 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 you learned in BUAD 128, Computer Applications to calculate the monthly repayment. It is found under Formulas in Excel. 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 Individual Assignment #5 - Financial Assignment 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. Proforma Balance Sheet Date ASSETS Current Assets Cash Accounts Receivable Inventory Total Current Assets Property Plant & Equipment Equipment Less accumulated deprecitation (equipment) Computer Less accumulated deprecitation (computer) Website Less accumulated deprecitation (website) Total Property Plant & Equipment Total Assets Serpen van de opleide 1 LIABILITIES & EQUITY Liabilities Accounts Payable Equipment Loans Balance Line of Credit Balance Total Liabilities Equity Increase in Owners Equity Owners Equity Owners Draw Total Equity Total Liabilities and Equity