Complete the excel spreadsheet to prepare 8-year budget for the project.
NOTE: Under SG&A tab, ignore (1) and assume that SG&A cost is made up of 5% of sales as variable SG&A and $1,146,000 as fixed SG&A cost.
Tools Data Window Help 5 . 44% 0 Sat 9:46 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 28-year project budget Data Review View Comments ab Wrap Text Share [ 49Or Number E P2 D Conditional Format Cell 3 Insert Calibri (Body) 12 A A = = = BI U DA SE Paste Merge & Center $ % -29 26 Delete Format Ideas Formatting as Table Sort & Filter Sensitivity Styles Find & Select X E34 x fx A B C D E F G H I 1 (1) Annual sales volume increase 5% for the years until 2023, then decline by 10% until 2025 2 (2) Unit Price is not changing (market competition is reflected in the sales volume) 3 (3) 90% of sales revenue is collected in cash in the current year; the remaining 10% will be collected in the following year 4 (4) Government grants $460,000 interest free loan, as an incentive for job creation. Must be returned at the end of project SALES BUDGET First Year 2018 9 8 Year Project Last Year Post-Project 2024 20251 2026 2019 2020 2021 2022 2023 185,220 Estimated Sale Unit Price Sales Revenue 160,000 168,000 176,400 40 $ 6,400,000 $ 6,720,000 $ 7,056,000 40 40 40 194,481 2 04,205 183,785 165,406 40 40 $ 7,779,240 $ 8,168,202 $ 7,351,382 $ 6,616,244 40 40 $ 7,408,800 $ 57,499,867 Last Year 2019 2020 2021 2022 2023 2024 Post-Project 2025 2026 Total Cash Collection Schedule First Year 2018 Cash Collection from current from previous Gov't Loan Total Cash Collection $ - $ - $ - $ - $ - $ - $ - $ - $ . Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING + Ready - - 125% 4000905190 @dI9 A = W3x ELU Tools Data Window Help 5 . 44% 0 Sat 9:46 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 28-year project budget Data Review View Share Comments 12 A A = = as wrap Text v Custom A Calibri (Body) B IV Custom . = E " Insert IX Delete Format Paste Deletev D A E Merge & Center $ % -26 Conditional Format Formatting as Table Cell Styles i sort & Finde Sort & Filter Find & Select Ideas X Sensitivity E49 x V fx MN U V W 1 2 3 4 5 6 (1) Required ending inventory is 10,000 units as a safety (2) Required material inventory is 8% of the following year's use (3) To produce one unit, cotton and fine cotton are demanded by 3 yards and 0.2 yard, respectively. (4) Per the company's cash management policy, all purchases will be paided in full within the year of purchase (Cotton $3 per yard; fine cotton; $5 per yard locked-in for 8 years) (5) To produce one unit of product, 0.5 hour of labor is required. Hourly wage rate is $22 per hour in 2018. Annual increase in hourly wage rate will be 8% (6) Variable MOH is $0.80 per unit (not changing). Fixed MOH will be increased by 15% year-over-year until year 2021, and subsequently remain constant. PRODUCTION BUDGET First Year 2018 2019 2020 Last Year Post-Project 2025 2026 2021 2022 2023 2024 165,406 Estimated Sale Unit Required Ending Inv. Total Demand (#) Less: Avalable Beg. Inv. # 160,000 10,000 168,000 10,000 176,400 10,000 185,220 10,000 194,481 10,000 2 04,205 10,000 1 83,785 10,000 0 Production in Unit 0 DM BUDGET 2018 2019 2021 2025 2020 0 2022 0 2023 0 2024 0 Production in Unit DM needed per Unit Cotton (3 yard) Fine Cotton (0.2 yard) Required Ending Inv. (yard) Cotton Fine Cotton Total Demand Cotton Fine Cotton Less: Beginning Inventory Cotton Fine Cotton DM purchase Cotton Fine Cotton Total Direct Material Cost 0 0 0 0 0 0 0 Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING + Ready - - + 86% 40009 3100 @ 1.10 @ # " A - W 5* ELU Tools Data Window Help 5 . 44% 0 Sat 9:46 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 28-year project budget Data Review View Share Comments A A = = = ab Wrap Text Custom A . Calibri (Body) B I 12 A Insert IX Delete Format Paste E Merge & Center $ % 8 Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select Ideas X Sensitivity E49 x fx MNO DM BUDGET 2018 0 2019 0 2020 0 2021 0 2022 0 2023 0 2024 0 2025 0 Production in Unit DM needed per Unit Cotton (3 yard) Fine Cotton (0.2 yard) Required Ending Inv. (yard) Cotton Fine Cotton Total Demand Cotton Fine Cotton Less: Beginning Inventory Cotton Fine Cotton DM purchase Cotton Fine Cotton Total Direct Material Cost 0 0 0 0 0 0 0 0 0 0 0 0 $ - 2018 0 2019 0 DL BUDGET 2020 0 2021 0 2022 0 2023 0 2024 0 2025 0 Production in Unit Direct Labour Hour per Unit Total Labor Hour Needed Hourly Rate ($22 in Year) Total Direct Labor Cost 0 0 0 0 0 0 0 0 MOH BUDGET 2019 2020 2024 2025 2022 0 2023 0 2018 Production in Unit Variable MOH ($0.80 per unit) Fixed MOH($544,000 at yearl) 544,000 Total Manufaturing Overhead Cost $ 544,000 Less: Depreciation - noncash Total Cash Payment for MOH $ 544,000 *Fored MOH includes $84,000 annual depreciation expenses. Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING Ready 2 - - + OUT 40009 3100 @ 72IOQ & A - W1 x ELU Tools Data Window Help 5 . 43%O Sat 9:47 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 8-year project budget Data Review View Share Comments Insert EX Deletev Format A . Zre Paste Sort & Filter Find & Select Ideas X Sensitivity Calibri (Body) 12 A A = = = ab Wrap Text General E BI U BOA E ES Merge & Center $ % -28 Conditional Format Cell Formatting as Table Styles 615 A xv fx A B C D E F G H 1 (1) Annual SG&A costs are 30% of sales revenue 2 (2) Variable SG&A is 5% of sales revenue; Fixed SG&A is $1,146,000. All SG&A costs are paid in full during the year M SG&A BUDGET First Year 2018 2019 $ 6,400,000 $ 6,720,000 2020 $ 7,056,000 2021 2022 $ 7,408,800 $ 7,779,240 Last Year 2023 2024 2025 $ 8,168,202 $ 7,351,382 $ 6,616,244 Sales Revenue Variable SG&A Fixed SG&A Total SG&A cost *Income tax costs will be disregarded for this project. ** No depreciation cost in SG&A 22 Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING + Ready @ - + 163% Tools Data Window Help 5 . 43%O Sat 9:47 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 8-year project budget Data Review View Share Comments = LO ( = ab Wrap Text General A DO ES Calibri (Body) 12 A A = BI U BOA E fx 7 Insert E Deletev Format v Paste Merge & Center $ % , E Cell Styles Conditional Format Formatting as Table Ideas Sort & Filter X Find & Select Sensitivity | A22 x N O P Q R S 1 (1) Seed money available for this project is $830,000 (= beginning cash) 2 (2) No working capital shall be needed. Used assets will be disposed in January 2026 (salvage value = $500,000). 3 (3) Maintenance/retooling costs will be incurred in the years, 2020, 2022, and 2024. CASH BUDGET First Year 2018 830,000 - 2019 2020 2021 2022 2023 Last Year 2024 Post-Project 2025 2026 Cash Balance, BOY Cash Collection Salvage value of assets Less: Cash Disbursement 500,000 DM DL SG&A Asset Purchase/Maint 1,508,600 500,000 400,000 300,000 Cash Balance, EOY $ (678,600) "Asset Purchase is done at the beginning of year 2018 ** Asset purhcase was done at the beginning of 2018 creating cash shortage. However, short-term financing was made during the year 2018 to cover the cash shortage and paid back during the year. In this cash budget, the financing transaction is disregarded. *** Asset sale (salvage value) occurs at the end of year 2026 Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING + Ready @ - + 100% 400090300 @ 72IOQ #WA W 9x ELU Tools Data Window Help 5 . 43%O Sat 9:47 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 8-year project budget Data Review View Share Comments = = 25 Wrap Text v General General , 2 b st pole Insert Delete Format A TUZT 7 Calibri (Body) 12 A A = BI U BOA E fox Paste E Merge & Center $ % ) 09 Conditional Format Formatting as Table Cell Styles Sort & Filter Ideas Sensitivity X V Find & Select | 418 x EF (1) All cash flows are assumed to be made at the end of year. (2) Depreciation taxshield is assumed to be $30,000 per year until 2025. All other tax effects are ignored. 2020 First Year 1/1/18 - 2018 1 (1,508,600) Post-Project 2026 2019 2 2021 4 2022 5 2023 6 Last Year 2024 2025 7 Year Annual C/F Depr. Tax Shield Total Cash Flows (1,508,600) (1,508,600) o o o o o o PV factor @ 12% 1 0.8929 0.7972 0.7972 0.7118 0.7118 0.6355 0.6355 0.5674 0.5674 0.5066 0.5066 0.4523 0.4523 0.4039 0.3606 0.8929 0 Present Value (1,508,600) 0 0 0 NPV $ (1,508,600) Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING + Ready - - + 150% 40009 3100 @ 72IOQ & A - W1 x ELU Tools Data Window Help 5 . 44% 0 Sat 9:46 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 28-year project budget Data Review View Comments ab Wrap Text Share [ 49Or Number E P2 D Conditional Format Cell 3 Insert Calibri (Body) 12 A A = = = BI U DA SE Paste Merge & Center $ % -29 26 Delete Format Ideas Formatting as Table Sort & Filter Sensitivity Styles Find & Select X E34 x fx A B C D E F G H I 1 (1) Annual sales volume increase 5% for the years until 2023, then decline by 10% until 2025 2 (2) Unit Price is not changing (market competition is reflected in the sales volume) 3 (3) 90% of sales revenue is collected in cash in the current year; the remaining 10% will be collected in the following year 4 (4) Government grants $460,000 interest free loan, as an incentive for job creation. Must be returned at the end of project SALES BUDGET First Year 2018 9 8 Year Project Last Year Post-Project 2024 20251 2026 2019 2020 2021 2022 2023 185,220 Estimated Sale Unit Price Sales Revenue 160,000 168,000 176,400 40 $ 6,400,000 $ 6,720,000 $ 7,056,000 40 40 40 194,481 2 04,205 183,785 165,406 40 40 $ 7,779,240 $ 8,168,202 $ 7,351,382 $ 6,616,244 40 40 $ 7,408,800 $ 57,499,867 Last Year 2019 2020 2021 2022 2023 2024 Post-Project 2025 2026 Total Cash Collection Schedule First Year 2018 Cash Collection from current from previous Gov't Loan Total Cash Collection $ - $ - $ - $ - $ - $ - $ - $ - $ . Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING + Ready - - 125% 4000905190 @dI9 A = W3x ELU Tools Data Window Help 5 . 44% 0 Sat 9:46 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 28-year project budget Data Review View Share Comments 12 A A = = as wrap Text v Custom A Calibri (Body) B IV Custom . = E " Insert IX Delete Format Paste Deletev D A E Merge & Center $ % -26 Conditional Format Formatting as Table Cell Styles i sort & Finde Sort & Filter Find & Select Ideas X Sensitivity E49 x V fx MN U V W 1 2 3 4 5 6 (1) Required ending inventory is 10,000 units as a safety (2) Required material inventory is 8% of the following year's use (3) To produce one unit, cotton and fine cotton are demanded by 3 yards and 0.2 yard, respectively. (4) Per the company's cash management policy, all purchases will be paided in full within the year of purchase (Cotton $3 per yard; fine cotton; $5 per yard locked-in for 8 years) (5) To produce one unit of product, 0.5 hour of labor is required. Hourly wage rate is $22 per hour in 2018. Annual increase in hourly wage rate will be 8% (6) Variable MOH is $0.80 per unit (not changing). Fixed MOH will be increased by 15% year-over-year until year 2021, and subsequently remain constant. PRODUCTION BUDGET First Year 2018 2019 2020 Last Year Post-Project 2025 2026 2021 2022 2023 2024 165,406 Estimated Sale Unit Required Ending Inv. Total Demand (#) Less: Avalable Beg. Inv. # 160,000 10,000 168,000 10,000 176,400 10,000 185,220 10,000 194,481 10,000 2 04,205 10,000 1 83,785 10,000 0 Production in Unit 0 DM BUDGET 2018 2019 2021 2025 2020 0 2022 0 2023 0 2024 0 Production in Unit DM needed per Unit Cotton (3 yard) Fine Cotton (0.2 yard) Required Ending Inv. (yard) Cotton Fine Cotton Total Demand Cotton Fine Cotton Less: Beginning Inventory Cotton Fine Cotton DM purchase Cotton Fine Cotton Total Direct Material Cost 0 0 0 0 0 0 0 Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING + Ready - - + 86% 40009 3100 @ 1.10 @ # " A - W 5* ELU Tools Data Window Help 5 . 44% 0 Sat 9:46 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 28-year project budget Data Review View Share Comments A A = = = ab Wrap Text Custom A . Calibri (Body) B I 12 A Insert IX Delete Format Paste E Merge & Center $ % 8 Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select Ideas X Sensitivity E49 x fx MNO DM BUDGET 2018 0 2019 0 2020 0 2021 0 2022 0 2023 0 2024 0 2025 0 Production in Unit DM needed per Unit Cotton (3 yard) Fine Cotton (0.2 yard) Required Ending Inv. (yard) Cotton Fine Cotton Total Demand Cotton Fine Cotton Less: Beginning Inventory Cotton Fine Cotton DM purchase Cotton Fine Cotton Total Direct Material Cost 0 0 0 0 0 0 0 0 0 0 0 0 $ - 2018 0 2019 0 DL BUDGET 2020 0 2021 0 2022 0 2023 0 2024 0 2025 0 Production in Unit Direct Labour Hour per Unit Total Labor Hour Needed Hourly Rate ($22 in Year) Total Direct Labor Cost 0 0 0 0 0 0 0 0 MOH BUDGET 2019 2020 2024 2025 2022 0 2023 0 2018 Production in Unit Variable MOH ($0.80 per unit) Fixed MOH($544,000 at yearl) 544,000 Total Manufaturing Overhead Cost $ 544,000 Less: Depreciation - noncash Total Cash Payment for MOH $ 544,000 *Fored MOH includes $84,000 annual depreciation expenses. Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING Ready 2 - - + OUT 40009 3100 @ 72IOQ & A - W1 x ELU Tools Data Window Help 5 . 43%O Sat 9:47 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 8-year project budget Data Review View Share Comments Insert EX Deletev Format A . Zre Paste Sort & Filter Find & Select Ideas X Sensitivity Calibri (Body) 12 A A = = = ab Wrap Text General E BI U BOA E ES Merge & Center $ % -28 Conditional Format Cell Formatting as Table Styles 615 A xv fx A B C D E F G H 1 (1) Annual SG&A costs are 30% of sales revenue 2 (2) Variable SG&A is 5% of sales revenue; Fixed SG&A is $1,146,000. All SG&A costs are paid in full during the year M SG&A BUDGET First Year 2018 2019 $ 6,400,000 $ 6,720,000 2020 $ 7,056,000 2021 2022 $ 7,408,800 $ 7,779,240 Last Year 2023 2024 2025 $ 8,168,202 $ 7,351,382 $ 6,616,244 Sales Revenue Variable SG&A Fixed SG&A Total SG&A cost *Income tax costs will be disregarded for this project. ** No depreciation cost in SG&A 22 Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING + Ready @ - + 163% Tools Data Window Help 5 . 43%O Sat 9:47 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 8-year project budget Data Review View Share Comments = LO ( = ab Wrap Text General A DO ES Calibri (Body) 12 A A = BI U BOA E fx 7 Insert E Deletev Format v Paste Merge & Center $ % , E Cell Styles Conditional Format Formatting as Table Ideas Sort & Filter X Find & Select Sensitivity | A22 x N O P Q R S 1 (1) Seed money available for this project is $830,000 (= beginning cash) 2 (2) No working capital shall be needed. Used assets will be disposed in January 2026 (salvage value = $500,000). 3 (3) Maintenance/retooling costs will be incurred in the years, 2020, 2022, and 2024. CASH BUDGET First Year 2018 830,000 - 2019 2020 2021 2022 2023 Last Year 2024 Post-Project 2025 2026 Cash Balance, BOY Cash Collection Salvage value of assets Less: Cash Disbursement 500,000 DM DL SG&A Asset Purchase/Maint 1,508,600 500,000 400,000 300,000 Cash Balance, EOY $ (678,600) "Asset Purchase is done at the beginning of year 2018 ** Asset purhcase was done at the beginning of 2018 creating cash shortage. However, short-term financing was made during the year 2018 to cover the cash shortage and paid back during the year. In this cash budget, the financing transaction is disregarded. *** Asset sale (salvage value) occurs at the end of year 2026 Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING + Ready @ - + 100% 400090300 @ 72IOQ #WA W 9x ELU Tools Data Window Help 5 . 43%O Sat 9:47 PM s a E Excel File Edit oo AutoSave OFF Home Insert Draw View Insert Format HESU Page Layout Formulas 8-year project budget Data Review View Share Comments = = 25 Wrap Text v General General , 2 b st pole Insert Delete Format A TUZT 7 Calibri (Body) 12 A A = BI U BOA E fox Paste E Merge & Center $ % ) 09 Conditional Format Formatting as Table Cell Styles Sort & Filter Ideas Sensitivity X V Find & Select | 418 x EF (1) All cash flows are assumed to be made at the end of year. (2) Depreciation taxshield is assumed to be $30,000 per year until 2025. All other tax effects are ignored. 2020 First Year 1/1/18 - 2018 1 (1,508,600) Post-Project 2026 2019 2 2021 4 2022 5 2023 6 Last Year 2024 2025 7 Year Annual C/F Depr. Tax Shield Total Cash Flows (1,508,600) (1,508,600) o o o o o o PV factor @ 12% 1 0.8929 0.7972 0.7972 0.7118 0.7118 0.6355 0.6355 0.5674 0.5674 0.5066 0.5066 0.4523 0.4523 0.4039 0.3606 0.8929 0 Present Value (1,508,600) 0 0 0 NPV $ (1,508,600) Synopsis SALES PRODUCTION SG&A CASH BUDGET CAPITAL BUDGETING + Ready - - + 150% 40009 3100 @ 72IOQ & A - W1 x ELU
