AutoSave Off Graded Problem 6 - Excel Search Samuel Weiss01 SW 7 File Home Insert Page Layout Formulas Data Review View Help Share Comments Arial 10 = = ~ Ai a Wrap Text General 48 @ IN Analyze Data Paste |B I Ur a. Av + Merge & Center $ %). Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Analysis P83 fic J K L M N 0 P Q R s T B D E F G H I 5 Rusty Spears, CEO of Rusty's Renovations, a custom building and repair company, is preparing 6 documentation for a line of credit request from his commercial banker. Among the required 7 documents is a detailed sales forecast for parts for the next two years. 8 9 Estimates obtained from the credit and collection department are as follows: collections within the month of sale, 15%; collections during the month following the sale, 65%; collections the second month 10 following the sale, 20%. Payments for labor and raw materials are typically made during the month 11 following the one in which these costs were incurred. Total costs for labor and raw materials are 12 estimated for each month as shown in the table. 13 14 General and administrative salaries will amount to approximately $15,000 a month; lease payments 15 under long-term lease contracts will be $5,000 a month; depreciation charges will be $7,500 a month; miscellaneous expenses will be $2,000 a month; income tax payments of $25,000 will be due in both 16 September and December; and a progress payment of $80,000 on a new office suite must be paid in 17 October. Cash on hand on July 1 will amount to $60,000, and a minimum cash balance of $40,000 will 18 be maintained throughout the cash budget period. 19 20 Input Data 21 Collections during month of sale 15% 22 Collections during month after sale 65% Note: When the percent collected 23 Collections during second month after sale 20% during the second month after sale is 24 Lease payments changed, the percent for collections 25 Target cash balance $40,000 during month after sale is 26 General and administrative salaries $15,000 automatically changed so that 100% 27 Depreciation charges $7,500 of sales are collected during the 28 Income tax payments (Sep & Dec) $25,000 three-month period. Build a Model + $5,000 Ready 100% j Type here to search O 78F 0 4x 10:31 AM 9/21/2021 AutoSave Off He Graded Problem 6 - Excel Search Samuel Weiss01 SW 7 File Home Insert Page Layout Formulas Data Review View Help Share Comments Arial 10 ~ Ai = = a Wrap Text CIT M General 7 @ IN Analyze Data Paste |B I Ur a. Av EE E Merge & Center $ %) 08 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Analysis P83 X for N 0 P o R s T B D E F G H I J K L M 31 Cash on hand July 1 $60,000 32 33 Sales, labor, and RM adjustment factor 0% 34 35 a. Prepare a monthly cash budget for the last six months of the year. 36 37 May June July August September October NovemberDecember January 38 Original sales estimates $60,000 $100,000 $130,000 $120,000 $100,000 $80,000 $60,000 $40,000 $30,000 39 Original labor and raw mat. estimat $75,000 $90,000 $95,000 $70,000 $60,000 $50,000 $20,000 $20,000 40 41 Forecasted Sales 42 Sales (gross) $60,000 $100,000 $130,000 $120,000 $100,000 $80,000 $60,000 $40,000 $30,000 43 44 Collections 45 During month of sale 19500 18000 15000 12000 9000 6000 46 During 1st month after sale $65,000 $84,500 $78,000 $65,000 $52,000 $39,000 47 During 2nd month after sale $12,000 $20,000 $26,000 $24,000 $20,000 $16,000 48 Total collections $96,500 $122,500 $119,000 $101,000 $81,000 $61,000 49 50 Purchases 51 Labor and raw materials $75,000 $90,000 $95,000 $70,000 $60,000 $50,000 $20,000 $20,000 52 Payments for labor and raw materials $90,000 $95,000 $70,000 $60,000 $50,000 $20,000 53 54 Payments 55 Payments for labor and raw materials 90,000 95,000 70,000 60,000 50,000 20,000 56 General and administrative salaries 15,000 15,000 15,000 15,000 15,000 15,000 57 Lana namanta 6.nnn 6.non Build a Model a + E E 6 Ann 6 nn 5 non Ready - + 100% Type here to search O X R W 78F X 0 4x 10:31 AM 9/21/2021 Auto Say Graded Problem #7 - Excel O Search SW o Samuel Wes-11 ZP x lol Help File F Data A Store ZS Home Insert Page Layout Formulas N | Times New Roman 10 | A = Concedents ZC Review View R W Wrap Text X Number 28 O F IN Analyze Data Paste BIU - A E + E Merge & Center $ %) , Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Editing Clipboard Font Alignment Number Cells Analysis M16 X E J L M M N o R F $1,000 3,000 G $1,262 4,800 H $1,544 2,880 $1,732 1,728 $15,000 $2,600 $935 $247 $120 $3,168 $1,023 $7,651 $17,600 $3,065 $6.309 $4,544 A D 65 Net operating profit after taxes 66 Add back depreciation 67 Equipment purchases 68 Cash flow due to change in NOWC 69 Net cash flow due to salvage 70 Net Cash Flow (Time line of cash flows) 71 72 Key Results: Appraisal of the Proposed Project 73 74 Net Present Value (at 10%) = ($960) 75 IRR = 7.72% 76 MIRR 8.47% 77 Payback = 2.80 78 Discounted Payback 79 Data for Payback Years 80 81 Net cash flow 82 Cumulative CF 83 Part of year required for payback 84 85 86 Data for Discounted Payback Years 87 88 Net cash flow 89 Discounted cash flow 90 Cumulative CF 91 Part of vear required for discounted navback Graded Problem #7 0 $17,600 $17,600 1 $3,065 $14,535 1.00 Years 2 $6,309 $8,226 1.00 3 $4,544 $3,683 0.80 4 $7,651 $3,969 0.00 0 $17,600 sa . Years 2 $6,309 1 $3,065 3 $4,544 4 $7,651 Ready Calculate W + 100% Type here to search O BI 03 W 69F D 10:59 AM 10/4/2021 F AutoSave Off Graded Problem 6 - Excel Search Samuel Weiss01 SW 7 File Home Insert Page Layout Formulas Data Review View Help Share Comments Arial 10 = = ~ Ai a Wrap Text General 48 @ IN Analyze Data Paste |B I Ur a. Av + Merge & Center $ %). Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Analysis P83 fic J K L M N 0 P Q R s T B D E F G H I 5 Rusty Spears, CEO of Rusty's Renovations, a custom building and repair company, is preparing 6 documentation for a line of credit request from his commercial banker. Among the required 7 documents is a detailed sales forecast for parts for the next two years. 8 9 Estimates obtained from the credit and collection department are as follows: collections within the month of sale, 15%; collections during the month following the sale, 65%; collections the second month 10 following the sale, 20%. Payments for labor and raw materials are typically made during the month 11 following the one in which these costs were incurred. Total costs for labor and raw materials are 12 estimated for each month as shown in the table. 13 14 General and administrative salaries will amount to approximately $15,000 a month; lease payments 15 under long-term lease contracts will be $5,000 a month; depreciation charges will be $7,500 a month; miscellaneous expenses will be $2,000 a month; income tax payments of $25,000 will be due in both 16 September and December; and a progress payment of $80,000 on a new office suite must be paid in 17 October. Cash on hand on July 1 will amount to $60,000, and a minimum cash balance of $40,000 will 18 be maintained throughout the cash budget period. 19 20 Input Data 21 Collections during month of sale 15% 22 Collections during month after sale 65% Note: When the percent collected 23 Collections during second month after sale 20% during the second month after sale is 24 Lease payments changed, the percent for collections 25 Target cash balance $40,000 during month after sale is 26 General and administrative salaries $15,000 automatically changed so that 100% 27 Depreciation charges $7,500 of sales are collected during the 28 Income tax payments (Sep & Dec) $25,000 three-month period. Build a Model + $5,000 Ready 100% j Type here to search O 78F 0 4x 10:31 AM 9/21/2021 AutoSave Off He Graded Problem 6 - Excel Search Samuel Weiss01 SW 7 File Home Insert Page Layout Formulas Data Review View Help Share Comments Arial 10 ~ Ai = = a Wrap Text CIT M General 7 @ IN Analyze Data Paste |B I Ur a. Av EE E Merge & Center $ %) 08 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing Analysis P83 X for N 0 P o R s T B D E F G H I J K L M 31 Cash on hand July 1 $60,000 32 33 Sales, labor, and RM adjustment factor 0% 34 35 a. Prepare a monthly cash budget for the last six months of the year. 36 37 May June July August September October NovemberDecember January 38 Original sales estimates $60,000 $100,000 $130,000 $120,000 $100,000 $80,000 $60,000 $40,000 $30,000 39 Original labor and raw mat. estimat $75,000 $90,000 $95,000 $70,000 $60,000 $50,000 $20,000 $20,000 40 41 Forecasted Sales 42 Sales (gross) $60,000 $100,000 $130,000 $120,000 $100,000 $80,000 $60,000 $40,000 $30,000 43 44 Collections 45 During month of sale 19500 18000 15000 12000 9000 6000 46 During 1st month after sale $65,000 $84,500 $78,000 $65,000 $52,000 $39,000 47 During 2nd month after sale $12,000 $20,000 $26,000 $24,000 $20,000 $16,000 48 Total collections $96,500 $122,500 $119,000 $101,000 $81,000 $61,000 49 50 Purchases 51 Labor and raw materials $75,000 $90,000 $95,000 $70,000 $60,000 $50,000 $20,000 $20,000 52 Payments for labor and raw materials $90,000 $95,000 $70,000 $60,000 $50,000 $20,000 53 54 Payments 55 Payments for labor and raw materials 90,000 95,000 70,000 60,000 50,000 20,000 56 General and administrative salaries 15,000 15,000 15,000 15,000 15,000 15,000 57 Lana namanta 6.nnn 6.non Build a Model a + E E 6 Ann 6 nn 5 non Ready - + 100% Type here to search O X R W 78F X 0 4x 10:31 AM 9/21/2021 Auto Say Graded Problem #7 - Excel O Search SW o Samuel Wes-11 ZP x lol Help File F Data A Store ZS Home Insert Page Layout Formulas N | Times New Roman 10 | A = Concedents ZC Review View R W Wrap Text X Number 28 O F IN Analyze Data Paste BIU - A E + E Merge & Center $ %) , Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Editing Clipboard Font Alignment Number Cells Analysis M16 X E J L M M N o R F $1,000 3,000 G $1,262 4,800 H $1,544 2,880 $1,732 1,728 $15,000 $2,600 $935 $247 $120 $3,168 $1,023 $7,651 $17,600 $3,065 $6.309 $4,544 A D 65 Net operating profit after taxes 66 Add back depreciation 67 Equipment purchases 68 Cash flow due to change in NOWC 69 Net cash flow due to salvage 70 Net Cash Flow (Time line of cash flows) 71 72 Key Results: Appraisal of the Proposed Project 73 74 Net Present Value (at 10%) = ($960) 75 IRR = 7.72% 76 MIRR 8.47% 77 Payback = 2.80 78 Discounted Payback 79 Data for Payback Years 80 81 Net cash flow 82 Cumulative CF 83 Part of year required for payback 84 85 86 Data for Discounted Payback Years 87 88 Net cash flow 89 Discounted cash flow 90 Cumulative CF 91 Part of vear required for discounted navback Graded Problem #7 0 $17,600 $17,600 1 $3,065 $14,535 1.00 Years 2 $6,309 $8,226 1.00 3 $4,544 $3,683 0.80 4 $7,651 $3,969 0.00 0 $17,600 sa . Years 2 $6,309 1 $3,065 3 $4,544 4 $7,651 Ready Calculate W + 100% Type here to search O BI 03 W 69F D 10:59 AM 10/4/2021 F