Unit 5 Projected Financial Statements anagement has developed a sales projection for the next twelve months. October $ 30,000 November $70,000 December $50,000 January $25,000 February $15,000 March $15,000 April $ 30,000 May $40,000 June $100,000 July July $100,000 August August $100,000 September $100,000 Based on industry standards, 75% of sales are expected to be collected in the current month and the remaining 25% will be collected in the month following the sale. Based on industry standards, the following are the basis for Cost of Goods Sold: Materials -45% of sales Direct Labor - 15% of sales Subcontractors - 15% of sales Mi Casa ignores overhead. Materials and subcontractors are paid in the month following the purchase. Direct labour is paid in the month the employees work. Sarah and Matthew will deposit approximately $20,000 in the bank at the beginning of October. $100 of these funds will be used to pay for their common shares and the remaining $19,900 will be set up as a loan from the shareholder with no interest and no set terms of repayment. The corporate income tax rate for Mi Casa is 17%. Operating Expenses requiring calculations as follows: Category Details Truck Approximate cost of $ 38,000 (sold to the company by the owners in exchange for a non-interest bearing shareholder loan) Useful life 5 years Residual value 20% of cost Depreciation Approximate cost of $ 18,000 (sold to the company by the owners in exchange for a non-interest bearing shareholder loan) Useful life 8 years No residual value Tools 11 Page Category Details Office Furniture Building Assumptions Approximate cost of $ 5,000 Useful life 3 years No residual value Approximate cost of $ 50,000 Useful life 20 years No residual value Quote received for $ 6,900 Quote received for $ 1,500 Full amount required Bank quoted 4.25% for 20 year amortization. Refer to the loan amortization schedule included below. Information from the County shows annual taxes at $ 420 Property and Liability Vehicle Mortgage on building Insurance Interest on Long Term Debt Property Taxes Re Building Other Operating Expenses are estimated as follows: Account Estimated Cost (Monthly) Rationale Accounting and $ 4,000 - October To establish business Legal $ 1,500 - March Interim financial results $ 3,000 - September Year-end preparation Admin Wages $ 4,800 - October Matt will be working more in the office to establish the $ 3,900 - November business $ 3,500 - December onward Advertising and $3,000 - October reducing by Different levels of advertising Promotions $1,000 per month in November required for different points in and another $1,000 in December the year None in January and February $ 1,000 in March, June - August $ 500 in April, May and Sept Business Fees $ 500 - January Annual fees (not material to set and Licenses up as prepaid) $ 170 - September Registry of Joint Stocks renewal Interest and Bank $ 35 per month As per discussion with the bank Charges Office Supplies $ 100 - October Set up the office $ 25 per month for remainder of the year Motor Vehicle $ 450 per month Expenses Repairs and $ 50 per month beginning in Maintenance January Telephone $ 165 per month 12 | Page Rationale Bimated Cost (Monthly) 5 per month 5250 per month Calculator Loan amount Annual interest rate Loan period in years Start date of loan Enter values 50,000.00 4.25% 20 10/1/2019 Monthly payment Number of payments Total interest Total cost of loan 309.62 240 24,308.14 74,308.14 Payment Beginning No. Date Balance 1 11/1/2019 50,000.00 2 12/1/2019 49,867.47 3 1/1/2020 49,734.46 4 2/1/2020 49,600.99 5 3/1/2020 49,467.04 6 4/1/2020 49,332.62 7 5/1/2020 49,197.72 8 6/1/2020 49,062.35 9 7/1/2020 48,926.49 10 8/1/2020 48,790.16 11 9/1/2020 48,653.34 12 10/1/2020 48,516.03 13 11/1/2020 48,378.24 14 12/1/2020 48,239.97 15 1/1/2021 48,101.20 16 2/1/2021 47,961.94 17 3/1/2021 47,822.19 18 4/1/2021 47,681.94 195/1/2021 47,541.20 20 6/1/2021 47,399.96 21 7/1/2021 47,258.21 22 8/1/2021 47,115.97 23 9/1/2021 46,973.22 Payment 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 Ending Principal Interest Balance 132.53 177.08 49,867.47 133.00 176.61 49,734.46 133.47 176.14 49,600.99 133.95 175.67 49,467.04 134.42 175.20 49,332.62 134.90 174.72 49,197.72 135.38 174.24 49,062.35 135.85 173.76 48,926.49 136.34 173.28 48,790.16 136.82 172.80 48,653.34 137.30 172.31 48,516.03 137.79 171.83 48,378.24 138.28 171.34 48,239.97 138.77 170.85 48,101.20 139.26 170.36 47,961.94 139.75 169.87 47,822.19 140.25 169.37 47,681.94 140.74 168.87 47,541.20 141.24 168.38 47,399.96 141.74 167.87 47,258.21 142.24 167.37 47,115.97 142.75 166.87 46,973.22 143.25 166.36 46,829.97 13 Page All operating expenses are to be paid in the month following the purchase with the exception of the following which do not have credit terms: Insurance Property Taxes Business Fees and Licenses Interest and Bank Charges When preparing your assignment using a spreadsheet, follow the instructions below: Round all numbers in your budget to the nearest dollar. Use one workbook for the assignment but each financial statement should begin on a new tab. Name each sheet tab. Excel Tip: Use cell references in all formulas and functions (no Use the freeze panes feature numbers). under the View tab when your Use a formula or function for all calculations, don't type in the spreadsheet gets too large to see on the screen. answer. Use a formula for transferring data from one sheet to ..... ........ another. Use the sum function appropriately. It should be used when adding more than two consecutive numbers. It should not be used when subtracting, multiplying or dividing. Format accordingly: O Ensure all values are accompanied by an appropriate and clear label. o Ensure that numbers have consistent alignment and format. Ensure that columns are wide enough to display all data in the cell. Required Using Excel, prepare a monthly budgeted cash flow, income statement and balance sheet. See your instructor for submission requirements. Unit 5 Projected Financial Statements anagement has developed a sales projection for the next twelve months. October $ 30,000 November $70,000 December $50,000 January $25,000 February $15,000 March $15,000 April $ 30,000 May $40,000 June $100,000 July July $100,000 August August $100,000 September $100,000 Based on industry standards, 75% of sales are expected to be collected in the current month and the remaining 25% will be collected in the month following the sale. Based on industry standards, the following are the basis for Cost of Goods Sold: Materials -45% of sales Direct Labor - 15% of sales Subcontractors - 15% of sales Mi Casa ignores overhead. Materials and subcontractors are paid in the month following the purchase. Direct labour is paid in the month the employees work. Sarah and Matthew will deposit approximately $20,000 in the bank at the beginning of October. $100 of these funds will be used to pay for their common shares and the remaining $19,900 will be set up as a loan from the shareholder with no interest and no set terms of repayment. The corporate income tax rate for Mi Casa is 17%. Operating Expenses requiring calculations as follows: Category Details Truck Approximate cost of $ 38,000 (sold to the company by the owners in exchange for a non-interest bearing shareholder loan) Useful life 5 years Residual value 20% of cost Depreciation Approximate cost of $ 18,000 (sold to the company by the owners in exchange for a non-interest bearing shareholder loan) Useful life 8 years No residual value Tools 11 Page Category Details Office Furniture Building Assumptions Approximate cost of $ 5,000 Useful life 3 years No residual value Approximate cost of $ 50,000 Useful life 20 years No residual value Quote received for $ 6,900 Quote received for $ 1,500 Full amount required Bank quoted 4.25% for 20 year amortization. Refer to the loan amortization schedule included below. Information from the County shows annual taxes at $ 420 Property and Liability Vehicle Mortgage on building Insurance Interest on Long Term Debt Property Taxes Re Building Other Operating Expenses are estimated as follows: Account Estimated Cost (Monthly) Rationale Accounting and $ 4,000 - October To establish business Legal $ 1,500 - March Interim financial results $ 3,000 - September Year-end preparation Admin Wages $ 4,800 - October Matt will be working more in the office to establish the $ 3,900 - November business $ 3,500 - December onward Advertising and $3,000 - October reducing by Different levels of advertising Promotions $1,000 per month in November required for different points in and another $1,000 in December the year None in January and February $ 1,000 in March, June - August $ 500 in April, May and Sept Business Fees $ 500 - January Annual fees (not material to set and Licenses up as prepaid) $ 170 - September Registry of Joint Stocks renewal Interest and Bank $ 35 per month As per discussion with the bank Charges Office Supplies $ 100 - October Set up the office $ 25 per month for remainder of the year Motor Vehicle $ 450 per month Expenses Repairs and $ 50 per month beginning in Maintenance January Telephone $ 165 per month 12 | Page Rationale Bimated Cost (Monthly) 5 per month 5250 per month Calculator Loan amount Annual interest rate Loan period in years Start date of loan Enter values 50,000.00 4.25% 20 10/1/2019 Monthly payment Number of payments Total interest Total cost of loan 309.62 240 24,308.14 74,308.14 Payment Beginning No. Date Balance 1 11/1/2019 50,000.00 2 12/1/2019 49,867.47 3 1/1/2020 49,734.46 4 2/1/2020 49,600.99 5 3/1/2020 49,467.04 6 4/1/2020 49,332.62 7 5/1/2020 49,197.72 8 6/1/2020 49,062.35 9 7/1/2020 48,926.49 10 8/1/2020 48,790.16 11 9/1/2020 48,653.34 12 10/1/2020 48,516.03 13 11/1/2020 48,378.24 14 12/1/2020 48,239.97 15 1/1/2021 48,101.20 16 2/1/2021 47,961.94 17 3/1/2021 47,822.19 18 4/1/2021 47,681.94 195/1/2021 47,541.20 20 6/1/2021 47,399.96 21 7/1/2021 47,258.21 22 8/1/2021 47,115.97 23 9/1/2021 46,973.22 Payment 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 309.62 Ending Principal Interest Balance 132.53 177.08 49,867.47 133.00 176.61 49,734.46 133.47 176.14 49,600.99 133.95 175.67 49,467.04 134.42 175.20 49,332.62 134.90 174.72 49,197.72 135.38 174.24 49,062.35 135.85 173.76 48,926.49 136.34 173.28 48,790.16 136.82 172.80 48,653.34 137.30 172.31 48,516.03 137.79 171.83 48,378.24 138.28 171.34 48,239.97 138.77 170.85 48,101.20 139.26 170.36 47,961.94 139.75 169.87 47,822.19 140.25 169.37 47,681.94 140.74 168.87 47,541.20 141.24 168.38 47,399.96 141.74 167.87 47,258.21 142.24 167.37 47,115.97 142.75 166.87 46,973.22 143.25 166.36 46,829.97 13 Page All operating expenses are to be paid in the month following the purchase with the exception of the following which do not have credit terms: Insurance Property Taxes Business Fees and Licenses Interest and Bank Charges When preparing your assignment using a spreadsheet, follow the instructions below: Round all numbers in your budget to the nearest dollar. Use one workbook for the assignment but each financial statement should begin on a new tab. Name each sheet tab. Excel Tip: Use cell references in all formulas and functions (no Use the freeze panes feature numbers). under the View tab when your Use a formula or function for all calculations, don't type in the spreadsheet gets too large to see on the screen. answer. Use a formula for transferring data from one sheet to ..... ........ another. Use the sum function appropriately. It should be used when adding more than two consecutive numbers. It should not be used when subtracting, multiplying or dividing. Format accordingly: O Ensure all values are accompanied by an appropriate and clear label. o Ensure that numbers have consistent alignment and format. Ensure that columns are wide enough to display all data in the cell. Required Using Excel, prepare a monthly budgeted cash flow, income statement and balance sheet. See your instructor for submission requirements