You have been approached by your client, Banana Industries Pty Ltd, to assist them in planning a new business venture. The business is planned to start on 1 July 2020.

Banana Industries has developed a new corporate boardroom webcam system which will be competing with others for the massive uptake in videoconferencing technology due to Covid-19.

Banana Industries requires you to prepare a budget and analysis of two separate scenarios they have determined to be likely, and have provided you with the following information.

Scenario #1

Cost of goods per unit $3750 [excluding GST]; mark-up 65%; expected annual sales 410 units. 8% of sales revenue [excluding GST] will be spent on marketing and advertising costs

Expenses [excluding GST)

Sales staff: 2 positions, $40,000 annual salary each [including superannuation].

Sales commissions: 4% of sales [excluding GST].

Admin staff: 1 manager [$95,000], 1 bookkeeper [$55,000], 1 receptionist [$40,000], all salaries given are annual and include superannuation.

Rent: $12,000 per month.

Other operating costs: $5500 per month.

Delivery costs: $130 per unit sold.

Assets [excluding GST]

Office furniture: Cost $75,000; expected residual $0; useful life 8 years; depreciation method straight-line; installed on 1 July 2020.

Factory equipment: Cost $775,000; expected residual $25,000; useful life 8 years; depreciation method straight-line; installed ready for use 31 July 2020.

Computers: Cost $65,000; expected residual $0; useful life 4 years; depreciation method reducing balance; installed ready for use 15 July 2020.

Loan from Big Bank

Banana Industries will borrow $300,000 at 4.5% p.a. compounding monthly. Repayments will be made each month, over a four-year loan term. The loan start date will be 1 July 2020.

Scenario #2

The marketing department at Banana Industries believes that by selling a slightly higher quality webcam system, a higher price and mark-up would be acceptable in the market. They have estimated that with an increased cost per unit [$3900] a mark-up of 70% would be achievable. To support this price increase, marketing and advertising expenditure would increase from 8% to 11%. Annual sales are expected to increase by 10 units compared to scenario #1.

Task to do

I have already done part a excel work. If any one can help me in part B.

Question

Part B

1) Advise your client on which scenario should be selected. Explain and justify why you would advise this selection (approx. 150 words).

2) Comment on any variances between the scenarios (approx. 150 words).

3) Explain the difference between straight-line depreciation and reducing balance depreciation

to your client. Reference any materials cited (approx. 150 words).

4) Prepare break-even analysis on the scenario you recommended in part B[1] above:

a. Calculate the break even analysis mathematically.

b. Show the break even analysis graphically.

c. Explain break-even analysis and why it is an important tool for business. (References

required) (approx. 250 words).

5) Produce a Pie Graph showing the break-up of all expenses in the under the scenario

recommended in part B[1] above. Comment on the pie graph (approx. 100 words).

6) From your analysis provide your recommendation to the client with supporting evidence

(hints: consider contribution margins, NP and GP analysis, and the current economic outlook).

The picture below are excel work done by me. Hope it will help any expert to answer my question

updated information

Question

Part B

1) Advise your client on which scenario should be selected. Explain and justify why you would advise this selection

2) Comment on any variances between the scenarios

3) Explain the difference between straight-line depreciation and reducing balance depreciation

to your client

4) Prepare break-even analysis on the scenario you recommended in part B[1] above:

a. Calculate the break even analysis mathematically.

b. Show the break even analysis graphically.

c. Explain break-even analysis and why it is an important tool for business.

5) Produce a Pie Graph showing the break-up of all expenses in the under the scenario

recommended in part B[1] above. Comment on the pie graph

6) From your analysis provide your recommendation to the client with supporting evidence

(hints: consider contribution margins, NP and GP analysis, and the current economic outlook).

Scenario 2 B Projected Projected Profit and Loss: 2020-21 Scenario 1 Unit Calculations Cost of Goods (per unit) 3750 Mark up 65% Sales Price (GST Excl) 6,187.50 7 GST 10% 8 Sales Price (GST incl) 6,806.25 9 10 Number of Units 410 11 12 13 3900 70% 6,630.00 10% 7,293.00 420 $ 14 Revenue 15 Less: Cost of Goods Sold 16 GROSS PROFIT 2,536,875 $ 1,537,500 999,375 2,784,600 1,638,000 1,146,600 17 18 Expenses Scenario Planning Deprecation Calculations Interest calculation B C 1,146,600 999,375 16 GROSS PROFIT 17 18 Expenses 19 Wages (sales staff] 20 Commission 21 Wages (admin staff] 22 Depreciation - Factory equipment 23 Depreciation - Computers 24 Depreciation - Office fit out 25 Marketing 26 Rent 27 Operating costs 28 Delivery costs 29 Interest 30 TOTAL EXPENSES 31 80,000 101,475 190,000 85,938 15,573 9,375 202,950 144,000 66,000 53,290 12,067 960,678 80,000 111,384 190,000 85,938 15,573 9,375 306,306 144,000 66,000 54,600 12,067 1,075,243 38,697 71,357 32 NET PROFIT 33 Scenario Planning Resde Depreciation calculations Interest calculation D E 0 8 A 1 Depreciation Work sheet (for the entire life of each asset) 2 Office Furniture 3 Cost 75000 4 Salvage 5 Life 8 6 Depreciation Office Furniture $9,375.00 7 8 Factory Equipment 9 Cost 775000 10 Salvage 25000 11 Life 12 Depreciation Factory Equipment $93,750 $85,938 13 14 Computers 15 Cost 65000 16 Salvage 0 17 Life 4 18 Depreciation Computers 16250 15573 19 20 21 22 23 24 25 26 + Scenario Planning Depreciation Calculations Interest calculation F G E D A B 1 Calcuation of interest on loan 2 3 4 Date Loan Rate Number of Month Monthly repayment 1-Jul-20 $300,000 0.375% 48 $6,841.05 $12,067 5 6 7 8 9 Period Number 10 1 11 2 12 3 13 4 14 5 15 6 16 7 17 8 18 9 19 10 20 11 21 12 22 13 23 14 24 15 25 16 26 17 Opening Balance Add interest Repayments Closing Balance $300,000 $1,125 $6,841.05 294283.95 $294,284 $1,104 $6,841.05 288546.47 $288,546 $1,082 $6,841.05 282787.48 $282,787 $1,060 $6,841.05 277006.88 $277,007 $1,039 $6,841.05 271204.61 $271,205 $1,017 $6,841.05 265380.59 $265,381 $995 $6,841.05 259534.72 $259,535 $973 $6,841.05 253666.93 $253,667 $951 $6,841.05 247777.13 $247,777 $929 $6,841.05 241865.25 $241,865 $907 $6,841.05 235931.20 $235,931 $885 $6,841.05 229974.89 $229,975 $862 $6,841.05 223996.25 $223,996 $840 $6,841.05 217995.19 $217,995 $817 $6,841.05 211971.63 $211,972 $795 $6,841.05 205925.48 $205,925 $772 $6,841.05 199856.65 Scenario Planning Depreciation Calculations Interest calculation A B D F G 18 9 10 19 20 21 22 23 11 12 13 14 15 16 24 25 26 27 17 18 19 20 28 29 30 31 32 33 34 35 36 37 38 39 40 $253,667 $247,777 $241,865 $235,931 $229,975 $223,996 $217,995 $211,972 $205,925 $199,857 $193,765 $187,651 $181,513 $175,353 $169,169 $162,963 $156,733 $150,480 $144,203 $137,903 $131,579 $125,231 $118,860 $112,464 $106,045 $99,602 21 22 23 24 25 26 27 28 29 30 E $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $951 $929 $907 $885 $862 $840 $817 $795 $772 $749 $727 $704 $681 $658 $634 $611 $588 $564 $541 $517 $493 $470 $446 $422 $398 $374 247777.13 241865.25 235931.20 229974.89 223996.25 217995.19 211971.63 205925.48 199856.65 193765.07 187650.64 181513.29 175352.92 169169.44 162962.78 156732.85 150479.55 144202.80 137902.52 131578.61 125230.98 118859.55 112464.23 106044.92 99601.55 93134.01 41 42 43 31 32 33 34 Scenario Planning Depreciation Calculations Interest calculation A B D F G 34 35 36 37 38 39 40 41 25 26 27 28 29 30 42 43 31 32 33 34 35 36 37 38 39 40 $156,733 $150,480 $144,203 $137,903 $131,579 $125,231 $118,860 $112,464 $106,045 $99,602 $93,134 $86,642 $80,126 $73,586 $67,020 $60,431 $53,816 $47,177 $40,513 $33,824 $27,110 $20,370 $13,606 $6,815 $588 $564 $541 $S17 $493 $470 $446 $422 $398 $374 $349 $325 $300 $276 $251 $227 $202 $177 $152 $127 $102 45 46 47 48 49 50 51 52 53 54 55 56 E $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 150479.55 144202.80 137902.52 131578.61 125230.98 118859.55 112464.23 106044.92 99601.55 93134.01 86642.21 80126.07 73585.50 67020.40 60430.68 53816.25 47177.02 40512.88 33823.76 27109.56 20370.17 13605.51 6815.49 0.00 41 42 43 44 45 46 $76 47 48 $51 $26 57 58 59 Scenario Planning Depreciation Calculations Interest calculation + Scenario Planning Depreciation Calculations Inte Projected Projected Profit and Loss: 2020-21 Scenario 1 (S) Scenario 2 (5) Unit Calculations Cost of Goods (per unit) Mark up Sales Price (GST Excl) GST Sales Price (GST incl) 3750 65% 6,187.50 10% 6,806,25 3900 70% 6,630.00 10% 7.293.00 Number of Units 410 420 Revenue Less: Cost of Goods Sold GROSS PROFIT $2,536,875 1,537,500 999,375 $2,784,600 1,638,000 1,146,600 Expenses Wages (sales staff) Commission Wages (admin staff) Depreciation - Factory equipment Depreciation - Computers Depreciation - Office fit out Marketing Rent Operating costs Delivery costs Interest TOTAL EXPENSES 80,000 101,475 190,000 85,938 15,573 9,375 202.950 144.000 66,000 53,300 12,067 960,678 80,000 111,384 190,000 85,938 15,573 9,375 306,306 144,000 66,000 54,600 12,067 1,075,243 NET PROFIT 38,697 71,357 26.0% 26.0% Company Tax Rate Company Tax Payable 10,061 18,553 NPAT 28,636 52,804 Scenario Planning Depreciation Calcula Depreciation Work sheet (for the entire life of each asset) Office Furniture Cost 75000 Salvage 0 Life 8 Depreciation Office Furniture $9,375.00 Factory Equipment Cost Salvage Life Depreciation Factory Equipment 775000 25000 8 $93,750 $85,938 Computers Cost Salvage Life Depreciation Computers 65000 0 4 16250 15573 Scenario Planning Depreciation Calculations Interest calculation Calcuation of interest on loan Date Lean Rate Number of Month Monthly repayment 1-Jul-20 $300,000 0.375% 48 $6,841.05 $12,067 $1.125 Period Number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 5840 Opening Balance Add interest $300,000 S294,284 $288,546 S282,787 $277.007 S271,205 S265,381 $259,535 S253.667 S247.777 S241.865 S235,931 $229,975 S223,996 $217,995 $211.972 S205,925 S199.857 $193.765 S187.651 S181,513 $175.353 S169.169 S162,963 SI56,733 S150,480 S144,201 $137.900 $131.579 $125.231 $118.860 $112.464 S106,045 599,602 $93.134 586,642 S80.126 $73.586 S67,020 S60,431 $53.816 S47.177 S40,513 $33.824 $27.110 $20,370 S13,606 $6,819 Repayments Closing Balance $6,841.05 29428395 S1,104 S6,841.05 288546.47 $1.082 $6,841.05 282787.48 $1.060 $6,841.05 277006.88 $1.039 S6,841.05 27120461 $1,017 $6,841.05 265380 59 $995 $6,84105 259534.72 5973 S6,841.05 253666.93 $951 $6,841.05 247777.13 5929 $6,841.05 241865 25 S907 S6,841.05 235931.20 SRS S6,841 05 229974.89 S862 $6,841.05 223996.25 S6,841.05 217995.19 $817 $6,841.05 211971.63 $793 $6,841.05 205925.4 5772 56.841.05 199856.65 $749 S6,841.05 193765.67 $727 $6,841.05 187650.64 $704 $6,841.05 181513.29 S681 S6,841.05 175352.92 S658 $6,841.05 169169.44 5634 S6,841.05 162962.78 S611 S6,841.05 156732.85 S588 $6,841.05 150479.55 5564 $6,841.05 144202.80 S541 S6,841.05 13790252 $517 $6,841.05 131578 61 5493 $6,841.05 125230.98 S470 S6,841.05 118859.55 $446 $6,841.05 11246423 $422 $6,841.05 106044.92 5398 S6,841.05 99601.55 5374 $6,841.05 93134.01 $349 $6,841.05 8664221 $325 S6,841.05 80126.07 $200 S6,841.05 73585.50 $276 $6,841.05 67020.40 S251 $6,841.0S 60430.68 S227 S6,841.05 53816.25 S202 $6,841.05 47177.02 $177 $6,841.05 40512.88 SI52 S6,841.05 33823.76 $127 $6,841.05 27109.56 S102 $6,841.05 20370.17 $76 S6,841.05 13605 S1 SSI S6,841.05 6815.49 $26 $6,841.05 -239197-10 25 26 27 28 29 30 31 32 34 35 36 37 38 39 40 41 44 45 46 47 48 Scenario Planning Depreciation Calculations Inte Projected Projected Profit and Loss: 2020-21 Scenario 1 (S) Scenario 2 (5) Unit Calculations Cost of Goods (per unit) Mark up Sales Price (GST Excl) GST Sales Price (GST incl) 3750 65% 6,187.50 10% 6,806,25 3900 70% 6,630.00 10% 7.293.00 Number of Units 410 420 Revenue Less: Cost of Goods Sold GROSS PROFIT $2,536,875 1,537,500 999,375 $2,784,600 1,638,000 1,146,600 Expenses Wages (sales staff) Commission Wages (admin staff) Depreciation - Factory equipment Depreciation - Computers Depreciation - Office fit out Marketing Rent Operating costs Delivery costs Interest TOTAL EXPENSES 80,000 101,475 190,000 85,938 15,573 9,375 202.950 144.000 66,000 53,300 12,067 960,678 80,000 111,384 190,000 85,938 15,573 9,375 306,306 144,000 66,000 54,600 12,067 1,075,243 NET PROFIT 38,697 71,357 26.0% 26.0% Company Tax Rate Company Tax Payable 10,061 18,553 NPAT 28,636 52,804 Scenario Planning Depreciation Ca Depreciation Work sheet (for the entire life of each asset) Office Furniture Cost 75000 Salvage 0 Life 8 Depreciation Office Furniture $9,375.00 Factory Equipment Cost Salvage Life Depreciation Factory Equipment 775000 25000 8 $93,750 $85,938 Computers Cost Salvage Life Depreciation Computers 65000 0 4 16250 15573 Scenario Planning Depreciation Calculations Interest Calcuation of interest on loan Date Lean Rate Number of Month Monthly repayment 1-Jul-20 $300.000 0.375% 48 $6,84105 $12,067 Period Number ! 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 5681 Opening Balance Add interest $300,000 $294.284 $288.546 S282,787 $277.007 $271,205 $265.381 S259.535 $253.667 S247.777 $241.865 $235.931 $229,975 $223.996 $217.995 $211.972 S205.925 $199.857 $193,765 $187.651 $181.513 SI75.353 $169,169 S162.963 $156,733 $150,480 $144.203 $137.903 $131.579 S125.231 $118.860 $112.464 106.045 $99,602 $93.134 586.642 $80.126 $73,586 $67,020 $60,431 $53.816 $47.177 $40.513 $33,824 $27.110 $20,370 $13,606 $6,815 Repayments Closing Balance $1.125 $6,841.05 294283.95 $1.104 56.841.OS 288546 47 $1,082 $6,841.05 282787.48 $1.060 56,841.05 277006.88 $1.039 $6,841.05 271204.61 S1.017 $6,841 OS 265380 59 5995 $6,841.05 259534.72 5973 S6,841 OS 254666 93 $951 $6,841.05 247777.13 5929 $6,841.05 241865.25 $907 $6,841.OS 235931 20 SSRS 56,841.0S 229974.89 5862 $6,841.05 223996 25 5840 56,841.05 217995.19 $817 $6,841.05 211971.63 5795 S6,841.05 205925.48 $772 $6,841.05 199856.65 $749 56,841.05 193765 07 $727 $6,841.05 18765064 $704 56,841 OS 181513.29 $6,841.05 17535292 5658 S6,841.05 169169.44 $634 $6,841.05 162962.78 S611 S6,841.05 156732.85 $588 $6,841.05 150479 55 S564 S6,841.05 144202.80 $541 $6,841. OS 137902 S2 5517 S6,841.05 131578.61 $493 $6,841.05 125230.98 $470 S6,841 OS 118859.55 $446 $6,841.05 112464 23 S422 $6,841.05 106044.92 $398 $6,841.05 99601.55 5374 S6,841.05 93134.01 5349 $6,841.05 8664221 $325 S6,841.05 80126.07 $300 56,841.05 73585.50 $276 $6,841.05 67020.40 S251 $6,841 OS 60430 68 S227 $6,841.05 53816.25 5202 $6,841 OS 47177.02 $177 $6,841.05 40512.88 SIS2 56.841.05 33823.76 S127 $6,841.05 27109.56 S102 $6,841 OS 20370.17 576 $6,841.05 13605.51 SSI 56,841.05 6815.49 S26 $6,841.05 -2.39197c-10 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 43 45 46 47 48 Scenario 2 B Projected Projected Profit and Loss: 2020-21 Scenario 1 Unit Calculations Cost of Goods (per unit) 3750 Mark up 65% Sales Price (GST Excl) 6,187.50 7 GST 10% 8 Sales Price (GST incl) 6,806.25 9 10 Number of Units 410 11 12 13 3900 70% 6,630.00 10% 7,293.00 420 $ 14 Revenue 15 Less: Cost of Goods Sold 16 GROSS PROFIT 2,536,875 $ 1,537,500 999,375 2,784,600 1,638,000 1,146,600 17 18 Expenses Scenario Planning Deprecation Calculations Interest calculation B C 1,146,600 999,375 16 GROSS PROFIT 17 18 Expenses 19 Wages (sales staff] 20 Commission 21 Wages (admin staff] 22 Depreciation - Factory equipment 23 Depreciation - Computers 24 Depreciation - Office fit out 25 Marketing 26 Rent 27 Operating costs 28 Delivery costs 29 Interest 30 TOTAL EXPENSES 31 80,000 101,475 190,000 85,938 15,573 9,375 202,950 144,000 66,000 53,290 12,067 960,678 80,000 111,384 190,000 85,938 15,573 9,375 306,306 144,000 66,000 54,600 12,067 1,075,243 38,697 71,357 32 NET PROFIT 33 Scenario Planning Resde Depreciation calculations Interest calculation D E 0 8 A 1 Depreciation Work sheet (for the entire life of each asset) 2 Office Furniture 3 Cost 75000 4 Salvage 5 Life 8 6 Depreciation Office Furniture $9,375.00 7 8 Factory Equipment 9 Cost 775000 10 Salvage 25000 11 Life 12 Depreciation Factory Equipment $93,750 $85,938 13 14 Computers 15 Cost 65000 16 Salvage 0 17 Life 4 18 Depreciation Computers 16250 15573 19 20 21 22 23 24 25 26 + Scenario Planning Depreciation Calculations Interest calculation F G E D A B 1 Calcuation of interest on loan 2 3 4 Date Loan Rate Number of Month Monthly repayment 1-Jul-20 $300,000 0.375% 48 $6,841.05 $12,067 5 6 7 8 9 Period Number 10 1 11 2 12 3 13 4 14 5 15 6 16 7 17 8 18 9 19 10 20 11 21 12 22 13 23 14 24 15 25 16 26 17 Opening Balance Add interest Repayments Closing Balance $300,000 $1,125 $6,841.05 294283.95 $294,284 $1,104 $6,841.05 288546.47 $288,546 $1,082 $6,841.05 282787.48 $282,787 $1,060 $6,841.05 277006.88 $277,007 $1,039 $6,841.05 271204.61 $271,205 $1,017 $6,841.05 265380.59 $265,381 $995 $6,841.05 259534.72 $259,535 $973 $6,841.05 253666.93 $253,667 $951 $6,841.05 247777.13 $247,777 $929 $6,841.05 241865.25 $241,865 $907 $6,841.05 235931.20 $235,931 $885 $6,841.05 229974.89 $229,975 $862 $6,841.05 223996.25 $223,996 $840 $6,841.05 217995.19 $217,995 $817 $6,841.05 211971.63 $211,972 $795 $6,841.05 205925.48 $205,925 $772 $6,841.05 199856.65 Scenario Planning Depreciation Calculations Interest calculation A B D F G 18 9 10 19 20 21 22 23 11 12 13 14 15 16 24 25 26 27 17 18 19 20 28 29 30 31 32 33 34 35 36 37 38 39 40 $253,667 $247,777 $241,865 $235,931 $229,975 $223,996 $217,995 $211,972 $205,925 $199,857 $193,765 $187,651 $181,513 $175,353 $169,169 $162,963 $156,733 $150,480 $144,203 $137,903 $131,579 $125,231 $118,860 $112,464 $106,045 $99,602 21 22 23 24 25 26 27 28 29 30 E $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $951 $929 $907 $885 $862 $840 $817 $795 $772 $749 $727 $704 $681 $658 $634 $611 $588 $564 $541 $517 $493 $470 $446 $422 $398 $374 247777.13 241865.25 235931.20 229974.89 223996.25 217995.19 211971.63 205925.48 199856.65 193765.07 187650.64 181513.29 175352.92 169169.44 162962.78 156732.85 150479.55 144202.80 137902.52 131578.61 125230.98 118859.55 112464.23 106044.92 99601.55 93134.01 41 42 43 31 32 33 34 Scenario Planning Depreciation Calculations Interest calculation A B D F G 34 35 36 37 38 39 40 41 25 26 27 28 29 30 42 43 31 32 33 34 35 36 37 38 39 40 $156,733 $150,480 $144,203 $137,903 $131,579 $125,231 $118,860 $112,464 $106,045 $99,602 $93,134 $86,642 $80,126 $73,586 $67,020 $60,431 $53,816 $47,177 $40,513 $33,824 $27,110 $20,370 $13,606 $6,815 $588 $564 $541 $S17 $493 $470 $446 $422 $398 $374 $349 $325 $300 $276 $251 $227 $202 $177 $152 $127 $102 45 46 47 48 49 50 51 52 53 54 55 56 E $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 $6,841.05 150479.55 144202.80 137902.52 131578.61 125230.98 118859.55 112464.23 106044.92 99601.55 93134.01 86642.21 80126.07 73585.50 67020.40 60430.68 53816.25 47177.02 40512.88 33823.76 27109.56 20370.17 13605.51 6815.49 0.00 41 42 43 44 45 46 $76 47 48 $51 $26 57 58 59 Scenario Planning Depreciation Calculations Interest calculation + Scenario Planning Depreciation Calculations Inte Projected Projected Profit and Loss: 2020-21 Scenario 1 (S) Scenario 2 (5) Unit Calculations Cost of Goods (per unit) Mark up Sales Price (GST Excl) GST Sales Price (GST incl) 3750 65% 6,187.50 10% 6,806,25 3900 70% 6,630.00 10% 7.293.00 Number of Units 410 420 Revenue Less: Cost of Goods Sold GROSS PROFIT $2,536,875 1,537,500 999,375 $2,784,600 1,638,000 1,146,600 Expenses Wages (sales staff) Commission Wages (admin staff) Depreciation - Factory equipment Depreciation - Computers Depreciation - Office fit out Marketing Rent Operating costs Delivery costs Interest TOTAL EXPENSES 80,000 101,475 190,000 85,938 15,573 9,375 202.950 144.000 66,000 53,300 12,067 960,678 80,000 111,384 190,000 85,938 15,573 9,375 306,306 144,000 66,000 54,600 12,067 1,075,243 NET PROFIT 38,697 71,357 26.0% 26.0% Company Tax Rate Company Tax Payable 10,061 18,553 NPAT 28,636 52,804 Scenario Planning Depreciation Calcula Depreciation Work sheet (for the entire life of each asset) Office Furniture Cost 75000 Salvage 0 Life 8 Depreciation Office Furniture $9,375.00 Factory Equipment Cost Salvage Life Depreciation Factory Equipment 775000 25000 8 $93,750 $85,938 Computers Cost Salvage Life Depreciation Computers 65000 0 4 16250 15573 Scenario Planning Depreciation Calculations Interest calculation Calcuation of interest on loan Date Lean Rate Number of Month Monthly repayment 1-Jul-20 $300,000 0.375% 48 $6,841.05 $12,067 $1.125 Period Number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 5840 Opening Balance Add interest $300,000 S294,284 $288,546 S282,787 $277.007 S271,205 S265,381 $259,535 S253.667 S247.777 S241.865 S235,931 $229,975 S223,996 $217,995 $211.972 S205,925 S199.857 $193.765 S187.651 S181,513 $175.353 S169.169 S162,963 SI56,733 S150,480 S144,201 $137.900 $131.579 $125.231 $118.860 $112.464 S106,045 599,602 $93.134 586,642 S80.126 $73.586 S67,020 S60,431 $53.816 S47.177 S40,513 $33.824 $27.110 $20,370 S13,606 $6,819 Repayments Closing Balance $6,841.05 29428395 S1,104 S6,841.05 288546.47 $1.082 $6,841.05 282787.48 $1.060 $6,841.05 277006.88 $1.039 S6,841.05 27120461 $1,017 $6,841.05 265380 59 $995 $6,84105 259534.72 5973 S6,841.05 253666.93 $951 $6,841.05 247777.13 5929 $6,841.05 241865 25 S907 S6,841.05 235931.20 SRS S6,841 05 229974.89 S862 $6,841.05 223996.25 S6,841.05 217995.19 $817 $6,841.05 211971.63 $793 $6,841.05 205925.4 5772 56.841.05 199856.65 $749 S6,841.05 193765.67 $727 $6,841.05 187650.64 $704 $6,841.05 181513.29 S681 S6,841.05 175352.92 S658 $6,841.05 169169.44 5634 S6,841.05 162962.78 S611 S6,841.05 156732.85 S588 $6,841.05 150479.55 5564 $6,841.05 144202.80 S541 S6,841.05 13790252 $517 $6,841.05 131578 61 5493 $6,841.05 125230.98 S470 S6,841.05 118859.55 $446 $6,841.05 11246423 $422 $6,841.05 106044.92 5398 S6,841.05 99601.55 5374 $6,841.05 93134.01 $349 $6,841.05 8664221 $325 S6,841.05 80126.07 $200 S6,841.05 73585.50 $276 $6,841.05 67020.40 S251 $6,841.0S 60430.68 S227 S6,841.05 53816.25 S202 $6,841.05 47177.02 $177 $6,841.05 40512.88 SI52 S6,841.05 33823.76 $127 $6,841.05 27109.56 S102 $6,841.05 20370.17 $76 S6,841.05 13605 S1 SSI S6,841.05 6815.49 $26 $6,841.05 -239197-10 25 26 27 28 29 30 31 32 34 35 36 37 38 39 40 41 44 45 46 47 48 Scenario Planning Depreciation Calculations Inte Projected Projected Profit and Loss: 2020-21 Scenario 1 (S) Scenario 2 (5) Unit Calculations Cost of Goods (per unit) Mark up Sales Price (GST Excl) GST Sales Price (GST incl) 3750 65% 6,187.50 10% 6,806,25 3900 70% 6,630.00 10% 7.293.00 Number of Units 410 420 Revenue Less: Cost of Goods Sold GROSS PROFIT $2,536,875 1,537,500 999,375 $2,784,600 1,638,000 1,146,600 Expenses Wages (sales staff) Commission Wages (admin staff) Depreciation - Factory equipment Depreciation - Computers Depreciation - Office fit out Marketing Rent Operating costs Delivery costs Interest TOTAL EXPENSES 80,000 101,475 190,000 85,938 15,573 9,375 202.950 144.000 66,000 53,300 12,067 960,678 80,000 111,384 190,000 85,938 15,573 9,375 306,306 144,000 66,000 54,600 12,067 1,075,243 NET PROFIT 38,697 71,357 26.0% 26.0% Company Tax Rate Company Tax Payable 10,061 18,553 NPAT 28,636 52,804 Scenario Planning Depreciation Ca Depreciation Work sheet (for the entire life of each asset) Office Furniture Cost 75000 Salvage 0 Life 8 Depreciation Office Furniture $9,375.00 Factory Equipment Cost Salvage Life Depreciation Factory Equipment 775000 25000 8 $93,750 $85,938 Computers Cost Salvage Life Depreciation Computers 65000 0 4 16250 15573 Scenario Planning Depreciation Calculations Interest Calcuation of interest on loan Date Lean Rate Number of Month Monthly repayment 1-Jul-20 $300.000 0.375% 48 $6,84105 $12,067 Period Number ! 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 5681 Opening Balance Add interest $300,000 $294.284 $288.546 S282,787 $277.007 $271,205 $265.381 S259.535 $253.667 S247.777 $241.865 $235.931 $229,975 $223.996 $217.995 $211.972 S205.925 $199.857 $193,765 $187.651 $181.513 SI75.353 $169,169 S162.963 $156,733 $150,480 $144.203 $137.903 $131.579 S125.231 $118.860 $112.464 106.045 $99,602 $93.134 586.642 $80.126 $73,586 $67,020 $60,431 $53.816 $47.177 $40.513 $33,824 $27.110 $20,370 $13,606 $6,815 Repayments Closing Balance $1.125 $6,841.05 294283.95 $1.104 56.841.OS 288546 47 $1,082 $6,841.05 282787.48 $1.060 56,841.05 277006.88 $1.039 $6,841.05 271204.61 S1.017 $6,841 OS 265380 59 5995 $6,841.05 259534.72 5973 S6,841 OS 254666 93 $951 $6,841.05 247777.13 5929 $6,841.05 241865.25 $907 $6,841.OS 235931 20 SSRS 56,841.0S 229974.89 5862 $6,841.05 223996 25 5840 56,841.05 217995.19 $817 $6,841.05 211971.63 5795 S6,841.05 205925.48 $772 $6,841.05 199856.65 $749 56,841.05 193765 07 $727 $6,841.05 18765064 $704 56,841 OS 181513.29 $6,841.05 17535292 5658 S6,841.05 169169.44 $634 $6,841.05 162962.78 S611 S6,841.05 156732.85 $588 $6,841.05 150479 55 S564 S6,841.05 144202.80 $541 $6,841. OS 137902 S2 5517 S6,841.05 131578.61 $493 $6,841.05 125230.98 $470 S6,841 OS 118859.55 $446 $6,841.05 112464 23 S422 $6,841.05 106044.92 $398 $6,841.05 99601.55 5374 S6,841.05 93134.01 5349 $6,841.05 8664221 $325 S6,841.05 80126.07 $300 56,841.05 73585.50 $276 $6,841.05 67020.40 S251 $6,841 OS 60430 68 S227 $6,841.05 53816.25 5202 $6,841 OS 47177.02 $177 $6,841.05 40512.88 SIS2 56.841.05 33823.76 S127 $6,841.05 27109.56 S102 $6,841 OS 20370.17 576 $6,841.05 13605.51 SSI 56,841.05 6815.49 S26 $6,841.05 -2.39197c-10 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 43 45 46 47 48