Need help with the pro forma excel

A B D E 1 2 3 9 10 12 13 14 15 16 Jupiter Auto Repair & Maintenance Case Facts and Assumptions Module 10 Exercise Jupiter Auto Repair wants to evaluate the possibility of adding additional bays to their existing facility to increase volume. The new bays would be identicalto the existing bay, so many of the inputs would be identical in proportion to sales. In order to add additional bays, the facility would need to lease more space in its building, and purchase 4 new equipment. The facility would be constructed in 2020, and begin operations in 2021. Using the 2019 operating results for Jupiter create proforma financial statements that can measure the feasibility 5 and financial performance of this potential project. Please consider the following facts: 6 $ 38,000 Revenue attributed to New Repair Bays in 2021 7 4.00% Repair Revenue Growth YoY 8 The following Items should grow in proportion to revenue: COGS Salaries 11 Benefits & Pensions Advertising Sales, General & Administrative (SG&A) Repairs Insurance Expense Miscellaneous Expenses 17 Rent is usually determined as the price per square foot per year as follows: $ 12.00 per square foot/year 3,400 square feet currently leased to Jupiter 1,200 additional square feet needed for new service bays 21 8.00% Interest Charges on existing notes payable 22 New Equipment will be required if the new bays were added, as follows: 23 $ 185,000 Jupiter equipment purchased in 2021 24 $ 18,500 Annual depreciation expense attributable to the new equipment 10.00% of equipment cost paid in cash in 2021 90.00% of equipment cost financed in 2021 8.00% Interest Charges on the financed portion of the new equipment 28 $ 60,000 Architectural/Installation Fees required, to be incurred in 2020 $ 145,000 Construction Fees required, to be incurred in 2020 30 Architectural, Installation and Construction Fees should be categorized as Professional Fees on the IS 31 Additional facts to consider; 32 12.85% Payroll tax rate payable on Wages & Salaries (FICA, Medicare, FUTA, SUTA) 40.00% Dividend rate of Net Income paid out as dividends as required by the BoD 34 $ 38,000 Annual depreciation expense attributable to existing equipment $ 50,000 Annual principle payments on notes payable only if net income is positive 36 (will reduce principle amount for next year) 28.00% Income Tax Rate charged on EBT 38 18 19 20 25 26 27 29 33 35 37 Jupiter Auto Repair & Maintenance Pro Forma 2019 2020 2021 2022 2023 ISCHEDULE 1 - SALES FORECAST Auto Repair Revenues New Bay Repair Revenues 1,526,910 $ 1,651,505 $ 4.00% Annual Growth $ 4.00% Annual Growth $ TOTAL FORECASTED REVENUE '$ 1,526,910 $ 3,053,820 $ 1,587,986 $ 1,587,986 $ 3,175,972 $ 1,651,505 $ 3,303,010 $ 1,717,565 $ 1,717,565 $ 3,435,130 $ 1,786,268 1,786,268 3,572,536 2019 2020 2021 2022 2023 $ $ $ ISCHEDULE 2 - COST OF GOODS SOLD Auto Parts of Revenue $ Direct Labor 0.00% of Revenue $ Payroll Taxes on Direct Labor 0.00% of Wages $ TOTAL FORECASTED COST OF GOODS SOLD $ $ $ $ 409,378 $ 390,500 $ $ 799,878 $ $ $ $ $ $ $ 2019 2020 2021 2022 2023 89,782 $ $ $ $ 131,745 $ $ $ $ SCHEDULE 3 - WAGES & SALARIES Executive Salaries 0.00% of Revenue $ Other Salaries & Wages 0.00% of Revenue $ Payroll Taxes 0.00% of Wages $ Benefits/Pensions 0.00% of Revenue $ TOTAL FORECASTED WAGES & SALARIES $ $ $ $ $ 25,681 $ $ $ $ $ 247,208 $ $ $ - 2019 2020 2021 2022 2023 40,800 $ $ $ $ $ $ $ $ 150,641 $ 16,513 $ $ $ ISCHEDULE 4 - FIXED OPERATING COSTS 12.00/sq ft Rent - Existing Facility 3,400 square feet $ $ 12.00/sq ft Rent - New Bays 1,200 square feet $ Sales General & Administrative 0.00% of Revenue $ Marketing/Advertising 0.00% of Revenue $ Repairs 0.00% of Revenue $ Insurance Expense 0.00% of Revenue $ Miscellaneous Expenses 0.00% of Revenue $ TOTAL FORECASTED FIXED OPERATING COSTS $ $ $ $ $ 11,994 $ $ $ $ $ $ $ 4,550 $ 5,364 $ $ $ $ $ 229,862 $ $ $ - 2019 2020 2021 2022 2023 $ $ $ $ $ $ SCHEDULE 5 - NON-OPERATING COSTS ProfessionalFees 0.00% of Revenue $ Legal Fees 0.00% of Revenue $ Equipment Installation $ Construction Expenses $ TOTAL FORECASTED NON-OPERATING EXPENSES $ 6,775 $ 4,000 $ $ $ $ $ $ $ $ $ 10,775 $ $ $ $ INET OPERATING INCOME $ 1,766,097 $ $ $ NET OPERATING PROFIT MARGIN 57.83% 0.00% 0.00% 0.00% 0.00% A B D E 1 2 3 9 10 12 13 14 15 16 Jupiter Auto Repair & Maintenance Case Facts and Assumptions Module 10 Exercise Jupiter Auto Repair wants to evaluate the possibility of adding additional bays to their existing facility to increase volume. The new bays would be identicalto the existing bay, so many of the inputs would be identical in proportion to sales. In order to add additional bays, the facility would need to lease more space in its building, and purchase 4 new equipment. The facility would be constructed in 2020, and begin operations in 2021. Using the 2019 operating results for Jupiter create proforma financial statements that can measure the feasibility 5 and financial performance of this potential project. Please consider the following facts: 6 $ 38,000 Revenue attributed to New Repair Bays in 2021 7 4.00% Repair Revenue Growth YoY 8 The following Items should grow in proportion to revenue: COGS Salaries 11 Benefits & Pensions Advertising Sales, General & Administrative (SG&A) Repairs Insurance Expense Miscellaneous Expenses 17 Rent is usually determined as the price per square foot per year as follows: $ 12.00 per square foot/year 3,400 square feet currently leased to Jupiter 1,200 additional square feet needed for new service bays 21 8.00% Interest Charges on existing notes payable 22 New Equipment will be required if the new bays were added, as follows: 23 $ 185,000 Jupiter equipment purchased in 2021 24 $ 18,500 Annual depreciation expense attributable to the new equipment 10.00% of equipment cost paid in cash in 2021 90.00% of equipment cost financed in 2021 8.00% Interest Charges on the financed portion of the new equipment 28 $ 60,000 Architectural/Installation Fees required, to be incurred in 2020 $ 145,000 Construction Fees required, to be incurred in 2020 30 Architectural, Installation and Construction Fees should be categorized as Professional Fees on the IS 31 Additional facts to consider; 32 12.85% Payroll tax rate payable on Wages & Salaries (FICA, Medicare, FUTA, SUTA) 40.00% Dividend rate of Net Income paid out as dividends as required by the BoD 34 $ 38,000 Annual depreciation expense attributable to existing equipment $ 50,000 Annual principle payments on notes payable only if net income is positive 36 (will reduce principle amount for next year) 28.00% Income Tax Rate charged on EBT 38 18 19 20 25 26 27 29 33 35 37 Jupiter Auto Repair & Maintenance Pro Forma 2019 2020 2021 2022 2023 ISCHEDULE 1 - SALES FORECAST Auto Repair Revenues New Bay Repair Revenues 1,526,910 $ 1,651,505 $ 4.00% Annual Growth $ 4.00% Annual Growth $ TOTAL FORECASTED REVENUE '$ 1,526,910 $ 3,053,820 $ 1,587,986 $ 1,587,986 $ 3,175,972 $ 1,651,505 $ 3,303,010 $ 1,717,565 $ 1,717,565 $ 3,435,130 $ 1,786,268 1,786,268 3,572,536 2019 2020 2021 2022 2023 $ $ $ ISCHEDULE 2 - COST OF GOODS SOLD Auto Parts of Revenue $ Direct Labor 0.00% of Revenue $ Payroll Taxes on Direct Labor 0.00% of Wages $ TOTAL FORECASTED COST OF GOODS SOLD $ $ $ $ 409,378 $ 390,500 $ $ 799,878 $ $ $ $ $ $ $ 2019 2020 2021 2022 2023 89,782 $ $ $ $ 131,745 $ $ $ $ SCHEDULE 3 - WAGES & SALARIES Executive Salaries 0.00% of Revenue $ Other Salaries & Wages 0.00% of Revenue $ Payroll Taxes 0.00% of Wages $ Benefits/Pensions 0.00% of Revenue $ TOTAL FORECASTED WAGES & SALARIES $ $ $ $ $ 25,681 $ $ $ $ $ 247,208 $ $ $ - 2019 2020 2021 2022 2023 40,800 $ $ $ $ $ $ $ $ 150,641 $ 16,513 $ $ $ ISCHEDULE 4 - FIXED OPERATING COSTS 12.00/sq ft Rent - Existing Facility 3,400 square feet $ $ 12.00/sq ft Rent - New Bays 1,200 square feet $ Sales General & Administrative 0.00% of Revenue $ Marketing/Advertising 0.00% of Revenue $ Repairs 0.00% of Revenue $ Insurance Expense 0.00% of Revenue $ Miscellaneous Expenses 0.00% of Revenue $ TOTAL FORECASTED FIXED OPERATING COSTS $ $ $ $ $ 11,994 $ $ $ $ $ $ $ 4,550 $ 5,364 $ $ $ $ $ 229,862 $ $ $ - 2019 2020 2021 2022 2023 $ $ $ $ $ $ SCHEDULE 5 - NON-OPERATING COSTS ProfessionalFees 0.00% of Revenue $ Legal Fees 0.00% of Revenue $ Equipment Installation $ Construction Expenses $ TOTAL FORECASTED NON-OPERATING EXPENSES $ 6,775 $ 4,000 $ $ $ $ $ $ $ $ $ 10,775 $ $ $ $ INET OPERATING INCOME $ 1,766,097 $ $ $ NET OPERATING PROFIT MARGIN 57.83% 0.00% 0.00% 0.00% 0.00%

Question

Need help with the pro forma excel

image text in transcribed

A B D E 1 2 3 9 10 12 13 14 15 16 Jupiter Auto Repair & Maintenance Case Facts and Assumptions Module 10 Exercise Jupiter Auto Repair wants to evaluate the possibility of adding additional bays to their existing facility to increase volume. The new bays would be identicalto the existing bay, so many of the inputs would be identical in proportion to sales. In order to add additional bays, the facility would need to lease more space in its building, and purchase 4 new equipment. The facility would be constructed in 2020, and begin operations in 2021. Using the 2019 operating results for Jupiter create proforma financial statements that can measure the feasibility 5 and financial performance of this potential project. Please consider the following facts: 6 $ 38,000 Revenue attributed to New Repair Bays in 2021 7 4.00% Repair Revenue Growth YoY 8 The following Items should grow in proportion to revenue: COGS Salaries 11 Benefits & Pensions Advertising Sales, General & Administrative (SG&A) Repairs Insurance Expense Miscellaneous Expenses 17 Rent is usually determined as the price per square foot per year as follows: $ 12.00 per square foot/year 3,400 square feet currently leased to Jupiter 1,200 additional square feet needed for new service bays 21 8.00% Interest Charges on existing notes payable 22 New Equipment will be required if the new bays were added, as follows: 23 $ 185,000 Jupiter equipment purchased in 2021 24 $ 18,500 Annual depreciation expense attributable to the new equipment 10.00% of equipment cost paid in cash in 2021 90.00% of equipment cost financed in 2021 8.00% Interest Charges on the financed portion of the new equipment 28 $ 60,000 Architectural/Installation Fees required, to be incurred in 2020 $ 145,000 Construction Fees required, to be incurred in 2020 30 Architectural, Installation and Construction Fees should be categorized as Professional Fees on the IS 31 Additional facts to consider; 32 12.85% Payroll tax rate payable on Wages & Salaries (FICA, Medicare, FUTA, SUTA) 40.00% Dividend rate of Net Income paid out as dividends as required by the BoD 34 $ 38,000 Annual depreciation expense attributable to existing equipment $ 50,000 Annual principle payments on notes payable only if net income is positive 36 (will reduce principle amount for next year) 28.00% Income Tax Rate charged on EBT 38 18 19 20 25 26 27 29 33 35 37 Jupiter Auto Repair & Maintenance Pro Forma 2019 2020 2021 2022 2023 ISCHEDULE 1 - SALES FORECAST Auto Repair Revenues New Bay Repair Revenues 1,526,910 $ 1,651,505 $ 4.00% Annual Growth $ 4.00% Annual Growth $ TOTAL FORECASTED REVENUE '$ 1,526,910 $ 3,053,820 $ 1,587,986 $ 1,587,986 $ 3,175,972 $ 1,651,505 $ 3,303,010 $ 1,717,565 $ 1,717,565 $ 3,435,130 $ 1,786,268 1,786,268 3,572,536 2019 2020 2021 2022 2023 $ $ $ ISCHEDULE 2 - COST OF GOODS SOLD Auto Parts of Revenue $ Direct Labor 0.00% of Revenue $ Payroll Taxes on Direct Labor 0.00% of Wages $ TOTAL FORECASTED COST OF GOODS SOLD $ $ $ $ 409,378 $ 390,500 $ $ 799,878 $ $ $ $ $ $ $ 2019 2020 2021 2022 2023 89,782 $ $ $ $ 131,745 $ $ $ $ SCHEDULE 3 - WAGES & SALARIES Executive Salaries 0.00% of Revenue $ Other Salaries & Wages 0.00% of Revenue $ Payroll Taxes 0.00% of Wages $ Benefits/Pensions 0.00% of Revenue $ TOTAL FORECASTED WAGES & SALARIES $ $ $ $ $ 25,681 $ $ $ $ $ 247,208 $ $ $ - 2019 2020 2021 2022 2023 40,800 $ $ $ $ $ $ $ $ 150,641 $ 16,513 $ $ $ ISCHEDULE 4 - FIXED OPERATING COSTS 12.00/sq ft Rent - Existing Facility 3,400 square feet $ $ 12.00/sq ft Rent - New Bays 1,200 square feet $ Sales General & Administrative 0.00% of Revenue $ Marketing/Advertising 0.00% of Revenue $ Repairs 0.00% of Revenue $ Insurance Expense 0.00% of Revenue $ Miscellaneous Expenses 0.00% of Revenue $ TOTAL FORECASTED FIXED OPERATING COSTS $ $ $ $ $ 11,994 $ $ $ $ $ $ $ 4,550 $ 5,364 $ $ $ $ $ 229,862 $ $ $ - 2019 2020 2021 2022 2023 $ $ $ $ $ $ SCHEDULE 5 - NON-OPERATING COSTS ProfessionalFees 0.00% of Revenue $ Legal Fees 0.00% of Revenue $ Equipment Installation $ Construction Expenses $ TOTAL FORECASTED NON-OPERATING EXPENSES $ 6,775 $ 4,000 $ $ $ $ $ $ $ $ $ 10,775 $ $ $ $ INET OPERATING INCOME $ 1,766,097 $ $ $ NET OPERATING PROFIT MARGIN 57.83% 0.00% 0.00% 0.00% 0.00% A B D E 1 2 3 9 10 12 13 14 15 16 Jupiter Auto Repair & Maintenance Case Facts and Assumptions Module 10 Exercise Jupiter Auto Repair wants to evaluate the possibility of adding additional bays to their existing facility to increase volume. The new bays would be identicalto the existing bay, so many of the inputs would be identical in proportion to sales. In order to add additional bays, the facility would need to lease more space in its building, and purchase 4 new equipment. The facility would be constructed in 2020, and begin operations in 2021. Using the 2019 operating results for Jupiter create proforma financial statements that can measure the feasibility 5 and financial performance of this potential project. Please consider the following facts: 6 $ 38,000 Revenue attributed to New Repair Bays in 2021 7 4.00% Repair Revenue Growth YoY 8 The following Items should grow in proportion to revenue: COGS Salaries 11 Benefits & Pensions Advertising Sales, General & Administrative (SG&A) Repairs Insurance Expense Miscellaneous Expenses 17 Rent is usually determined as the price per square foot per year as follows: $ 12.00 per square foot/year 3,400 square feet currently leased to Jupiter 1,200 additional square feet needed for new service bays 21 8.00% Interest Charges on existing notes payable 22 New Equipment will be required if the new bays were added, as follows: 23 $ 185,000 Jupiter equipment purchased in 2021 24 $ 18,500 Annual depreciation expense attributable to the new equipment 10.00% of equipment cost paid in cash in 2021 90.00% of equipment cost financed in 2021 8.00% Interest Charges on the financed portion of the new equipment 28 $ 60,000 Architectural/Installation Fees required, to be incurred in 2020 $ 145,000 Construction Fees required, to be incurred in 2020 30 Architectural, Installation and Construction Fees should be categorized as Professional Fees on the IS 31 Additional facts to consider; 32 12.85% Payroll tax rate payable on Wages & Salaries (FICA, Medicare, FUTA, SUTA) 40.00% Dividend rate of Net Income paid out as dividends as required by the BoD 34 $ 38,000 Annual depreciation expense attributable to existing equipment $ 50,000 Annual principle payments on notes payable only if net income is positive 36 (will reduce principle amount for next year) 28.00% Income Tax Rate charged on EBT 38 18 19 20 25 26 27 29 33 35 37 Jupiter Auto Repair & Maintenance Pro Forma 2019 2020 2021 2022 2023 ISCHEDULE 1 - SALES FORECAST Auto Repair Revenues New Bay Repair Revenues 1,526,910 $ 1,651,505 $ 4.00% Annual Growth $ 4.00% Annual Growth $ TOTAL FORECASTED REVENUE '$ 1,526,910 $ 3,053,820 $ 1,587,986 $ 1,587,986 $ 3,175,972 $ 1,651,505 $ 3,303,010 $ 1,717,565 $ 1,717,565 $ 3,435,130 $ 1,786,268 1,786,268 3,572,536 2019 2020 2021 2022 2023 $ $ $ ISCHEDULE 2 - COST OF GOODS SOLD Auto Parts of Revenue $ Direct Labor 0.00% of Revenue $ Payroll Taxes on Direct Labor 0.00% of Wages $ TOTAL FORECASTED COST OF GOODS SOLD $ $ $ $ 409,378 $ 390,500 $ $ 799,878 $ $ $ $ $ $ $ 2019 2020 2021 2022 2023 89,782 $ $ $ $ 131,745 $ $ $ $ SCHEDULE 3 - WAGES & SALARIES Executive Salaries 0.00% of Revenue $ Other Salaries & Wages 0.00% of Revenue $ Payroll Taxes 0.00% of Wages $ Benefits/Pensions 0.00% of Revenue $ TOTAL FORECASTED WAGES & SALARIES $ $ $ $ $ 25,681 $ $ $ $ $ 247,208 $ $ $ - 2019 2020 2021 2022 2023 40,800 $ $ $ $ $ $ $ $ 150,641 $ 16,513 $ $ $ ISCHEDULE 4 - FIXED OPERATING COSTS 12.00/sq ft Rent - Existing Facility 3,400 square feet $ $ 12.00/sq ft Rent - New Bays 1,200 square feet $ Sales General & Administrative 0.00% of Revenue $ Marketing/Advertising 0.00% of Revenue $ Repairs 0.00% of Revenue $ Insurance Expense 0.00% of Revenue $ Miscellaneous Expenses 0.00% of Revenue $ TOTAL FORECASTED FIXED OPERATING COSTS $ $ $ $ $ 11,994 $ $ $ $ $ $ $ 4,550 $ 5,364 $ $ $ $ $ 229,862 $ $ $ - 2019 2020 2021 2022 2023 $ $ $ $ $ $ SCHEDULE 5 - NON-OPERATING COSTS ProfessionalFees 0.00% of Revenue $ Legal Fees 0.00% of Revenue $ Equipment Installation $ Construction Expenses $ TOTAL FORECASTED NON-OPERATING EXPENSES $ 6,775 $ 4,000 $ $ $ $ $ $ $ $ $ 10,775 $ $ $ $ INET OPERATING INCOME $ 1,766,097 $ $ $ NET OPERATING PROFIT MARGIN 57.83% 0.00% 0.00% 0.00% 0.00%

Accepted Answer

The Answer is in the image, click to view ...

Question

Need help with the pro forma excel A B D E 1 2 3 9 10 12 13 14 15 16 Jupiter Auto Repair &

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Order Flow Trading Secrets To Mastering Market Sentiment Liquidity Volume Patterns Price Action Trading Tools And More

Students also viewed these Finance questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question