just need to fill in the rest of the yellow boxes. i posted what i completed already. and please lmk where you got the numbers from! thank you in advance!
your Requirements: Please use the data from the following table to complete the cash budget in Excel spreadsheets. You can use the Excel template (FINA 3562 Project 2 data.xlsx) posted on Blackboard for this assignment. To complete the cash budget, you will have to work on the schedules of expected cash collections and payments first. Please set formulae by referencing to the data in Excel spreadsheets. If you do not meet this requirement, you will be asked to re-work on the project. This project accounts for 6% of overall grade. Due date: March 19th, 11:59 pm on Blackboard The following table lists the data from the budget of Lewellen Products. 35% the company's sales will be collected in the month of the sale; the other 65% will be collected in the following month. The company pays all its credit purchase with a one-month delay. Credit purchases in June were $206,300, and total sales in June were $419,000. Lewellen Products Sales, purchase, and other budgets July August September Total sales $419,000 $485,000 $510,000 $565,000 June Purchases of material For cash 135,000 103,000 95,000 For credit 206,300 210,800 278,300 296,700 63,800 Selling and admin. expenses 42,700 56,500 Income taxes 20,000 20,000 20,000 15,000 Dividends 15,000 15,000 Equipment purchase 108,000 83,800 0 We make these assumptions to simplify the analysis: * Lewellen Products has an open line of credit with its bank, which can be used as needed to bolster the cash position. The company desires to maintain a $70,000 minimum cash balance at the end of each month. Therefore, borrowing must be sufficient to cover the cash shortfall and to provide for the minimum cash balance of $70,000. The beginning cash balance for July is $100,000. * All borrowings and repayments must be in multiples of $1,000 amounts, and interest is 3 percent per annum. * Interest is computed and paid on the principal during the borrowing period. All borrowings take place at the beginning of a month, and all repayments are made at the end of a month. The company will take a loan at the beginning of July, repay some portion of the loan at the end of August, and should be able to pay off the remaining balance of the loan at the end of September. You have to figure out the amount of loan taken at the beginning of July. The company pays the selling and administrative expenses, income taxes, dividends, and pays for equipment purchase at the end of a month. Please refer to the example of cash budget in chapter 6 Excel file posted on Blackboard. The interest payment made at the end of August or September is computed as: Principal of the loan x Interest rate on the loan x borrowing period. Because the interest rate on the loan is an annual rate, the borrowing period will be adjusted to a fraction of a year. Please also review pages 8-9 of the class notes for how to back out the borrowing amount or repayment of the loan. Compute the interest payment first, and then figure out the repayment of the loan for August or September. Leonilenhoducts Sales parts, and other budgets 110,000 ASS.000 August 510,000 September 45.000 Yotatales hotel Forch Fort Teen Products Cash Budget For the Month Ended September, 2020 Month Buly Aupet September SL.000 2100 2.700 20.000 13.00 10.000 102.000 2100 54.500 20.000 15,000 83.000 35,000 296,700 63,00 20.00 15.000 Owens 15 Schedule of Expected Cash Collection July August September Cash balance beginning Add: Receipts Collections from customer Totalcach Less Die Direct materials Seadmin Equipment porte Dividende Income tan Total Minimum chance Total needed Cash plus de 14 Finance Bong Hepay NY Cashments September Total effect of financing Cah balance, ending Am D Lewellen Products purchase, and other budgets At 510,000 Lewend CathBudget For the Month Ended September 10, 2000 Month lu 100,000 5.000 5.000 95,00 100,000 2,00 For ng anda perties 135.000 3100 42.00 20.000 000 30.000 Gaun balance bene Add: RAI Collections from customers Total cash available 2, 1,000 20,000 25.000 0 2100 5542.100 20,000 15,000 1 wideo purch Schedule of Expected the Collections July August September 27150 Duibus Direct material Selling and admin Equipment pas Dividende Income Totalment Michalince Tolond Canth supus fit Finance SOLO 13.000 2006 $27.000 17 Archive/00 10 SA Apostles 10,000 20 September (10) 100 31 191,750 250 pement In totaleffect of inancing Cashblance, endine September 26 27 Ans pat 20 20 Oulu Aphases burch 51 Total Sheet1 Sheet2 Schedule of porte Cash August 200.00 115.000 210,00 101,000 2M, 1.000 371,300 M1,300 3. Sheet3 your Requirements: Please use the data from the following table to complete the cash budget in Excel spreadsheets. You can use the Excel template (FINA 3562 Project 2 data.xlsx) posted on Blackboard for this assignment. To complete the cash budget, you will have to work on the schedules of expected cash collections and payments first. Please set formulae by referencing to the data in Excel spreadsheets. If you do not meet this requirement, you will be asked to re-work on the project. This project accounts for 6% of overall grade. Due date: March 19th, 11:59 pm on Blackboard The following table lists the data from the budget of Lewellen Products. 35% the company's sales will be collected in the month of the sale; the other 65% will be collected in the following month. The company pays all its credit purchase with a one-month delay. Credit purchases in June were $206,300, and total sales in June were $419,000. Lewellen Products Sales, purchase, and other budgets July August September Total sales $419,000 $485,000 $510,000 $565,000 June Purchases of material For cash 135,000 103,000 95,000 For credit 206,300 210,800 278,300 296,700 63,800 Selling and admin. expenses 42,700 56,500 Income taxes 20,000 20,000 20,000 15,000 Dividends 15,000 15,000 Equipment purchase 108,000 83,800 0 We make these assumptions to simplify the analysis: * Lewellen Products has an open line of credit with its bank, which can be used as needed to bolster the cash position. The company desires to maintain a $70,000 minimum cash balance at the end of each month. Therefore, borrowing must be sufficient to cover the cash shortfall and to provide for the minimum cash balance of $70,000. The beginning cash balance for July is $100,000. * All borrowings and repayments must be in multiples of $1,000 amounts, and interest is 3 percent per annum. * Interest is computed and paid on the principal during the borrowing period. All borrowings take place at the beginning of a month, and all repayments are made at the end of a month. The company will take a loan at the beginning of July, repay some portion of the loan at the end of August, and should be able to pay off the remaining balance of the loan at the end of September. You have to figure out the amount of loan taken at the beginning of July. The company pays the selling and administrative expenses, income taxes, dividends, and pays for equipment purchase at the end of a month. Please refer to the example of cash budget in chapter 6 Excel file posted on Blackboard. The interest payment made at the end of August or September is computed as: Principal of the loan x Interest rate on the loan x borrowing period. Because the interest rate on the loan is an annual rate, the borrowing period will be adjusted to a fraction of a year. Please also review pages 8-9 of the class notes for how to back out the borrowing amount or repayment of the loan. Compute the interest payment first, and then figure out the repayment of the loan for August or September. Leonilenhoducts Sales parts, and other budgets 110,000 ASS.000 August 510,000 September 45.000 Yotatales hotel Forch Fort Teen Products Cash Budget For the Month Ended September, 2020 Month Buly Aupet September SL.000 2100 2.700 20.000 13.00 10.000 102.000 2100 54.500 20.000 15,000 83.000 35,000 296,700 63,00 20.00 15.000 Owens 15 Schedule of Expected Cash Collection July August September Cash balance beginning Add: Receipts Collections from customer Totalcach Less Die Direct materials Seadmin Equipment porte Dividende Income tan Total Minimum chance Total needed Cash plus de 14 Finance Bong Hepay NY Cashments September Total effect of financing Cah balance, ending Am D Lewellen Products purchase, and other budgets At 510,000 Lewend CathBudget For the Month Ended September 10, 2000 Month lu 100,000 5.000 5.000 95,00 100,000 2,00 For ng anda perties 135.000 3100 42.00 20.000 000 30.000 Gaun balance bene Add: RAI Collections from customers Total cash available 2, 1,000 20,000 25.000 0 2100 5542.100 20,000 15,000 1 wideo purch Schedule of Expected the Collections July August September 27150 Duibus Direct material Selling and admin Equipment pas Dividende Income Totalment Michalince Tolond Canth supus fit Finance SOLO 13.000 2006 $27.000 17 Archive/00 10 SA Apostles 10,000 20 September (10) 100 31 191,750 250 pement In totaleffect of inancing Cashblance, endine September 26 27 Ans pat 20 20 Oulu Aphases burch 51 Total Sheet1 Sheet2 Schedule of porte Cash August 200.00 115.000 210,00 101,000 2M, 1.000 371,300 M1,300 3. Sheet3
