November A B D E F G H 1 Please complete the yellow highlighted parts. Please show the formulas if the number or figures are not provided in the problem. 2 Loblaw Manufacturing 3 Cash Budget 4 June 2021 to October 2021 5 April May June July August September October 8 Cash 20% $ 96,625 $ 80,762 $ 68,819 $ 53,094 $ 43,225 9 First Month 65% $ 249,538.90 $ 314,031 $ 262,478 $ 223,660 $ 172,554.20 10 Second Month 15% $ 49,054.65 $ 57,585.90 $ 72,468.75 $ 60,571.80 $ 51,613.95 11 Total Collections $1.856,082 $ 395,219 $ 452,380 $ 403,765 $ 337,326 $ 267,393 12 Purchases 55% $ 265,719 $ 222,097 $ 189,251 $ 146,007 $ 118,869 $ 129,438.65 13 Payments: 14 During Month 40% $ 87,062 $ 74,186 $ 57,235 $ 46,597 $ 50,740 15 Month After 60% $ 133,258 $ 113,551 $ 87,604 $ 71,321 $ 77,663 16 Total Payments $ 220,320 $ 187,737 $ 144,839 $ 117,918 $ 128,403 17 Collections $ 222,097 $ 189,251 $ 146,007 $ 118,869 $ 129,439 18 Disbursements: 19 Inventory Payments $ 220,320 $ 187,737 $ 144,839 $ 117,918 $ 128,403 20 Short-term Interest $ $ 752 $ 1,461 $ 2.125 $ 2,613 21 Other Payments $ 227,500 $ 213,281 $ 199,062 $ 145,188 $ 127,750 22 Total Disbursements $ 447,820 $ 401,771 $ 345,362 $ 265,231 $ 258,766 23 Beginning Cash Balance $ 25,000 $ 25,000 $ 25,000 $ 25,000 $ 25,000 24 Net Additions to Cash $ (225,723) $ (212,519) $ (199,355) $ (146,362) $ (129,328) 25 Unadjusted Cash Balance $ 30,000 $ (200,723) $ (187,519) $ (174,355) $ (121,362) $ (104,328) 26 Add: Current Borrowing $ $ 225,723 $ 212,519 $ 199,355 $ 146,362 $ 129,328 27 Ending Cash Balance $ 30,000 $ 25,000 $ 25,000 $ 25,000 $ 25,000 $ 25,000 28 29 Cumulative Borrowing $ $ 225,723 $ 438,243 $ 637,597 783,960 $ 913,287 30 Minimum Cash Balance 25,000 31 Discount for Paying in Same Month 2.00% 32 Annual Interest Rate 4.00% 33 Monthly Interest Rate 0.33% 34 35 Total Interest Cost $ 6,951.74 36 37 b) Bob Loblaw, the president, is considering stretching out its inventory payments. He believes that it may be less expensive to borrow from suppliers than from the bank. He has asked you to use the Scenario Manager to see what the total interest cost for this time period would be if the company paid for 0%, 10%, 30%, or 40% of its inventory purchases in the same month. The remainder would be paid in the following month. Create a scenario summary, and describe whether the results support Bob's belief (Analysis, Identify and Access Needed Information & Use Information Effectively to Accomplish a Specific Purpose 12 pts). c) Interpretation: please describe where you obtained information to develop analyses and interpret the results. Please include your written description in the "interpretation tab. If you do not complete this section with your words or leave the interpretation tab as a blank, you receive 0 point in this HW. November A B D E F G H 1 Please complete the yellow highlighted parts. Please show the formulas if the number or figures are not provided in the problem. 2 Loblaw Manufacturing 3 Cash Budget 4 June 2021 to October 2021 5 April May June July August September October 8 Cash 20% $ 96,625 $ 80,762 $ 68,819 $ 53,094 $ 43,225 9 First Month 65% $ 249,538.90 $ 314,031 $ 262,478 $ 223,660 $ 172,554.20 10 Second Month 15% $ 49,054.65 $ 57,585.90 $ 72,468.75 $ 60,571.80 $ 51,613.95 11 Total Collections $1.856,082 $ 395,219 $ 452,380 $ 403,765 $ 337,326 $ 267,393 12 Purchases 55% $ 265,719 $ 222,097 $ 189,251 $ 146,007 $ 118,869 $ 129,438.65 13 Payments: 14 During Month 40% $ 87,062 $ 74,186 $ 57,235 $ 46,597 $ 50,740 15 Month After 60% $ 133,258 $ 113,551 $ 87,604 $ 71,321 $ 77,663 16 Total Payments $ 220,320 $ 187,737 $ 144,839 $ 117,918 $ 128,403 17 Collections $ 222,097 $ 189,251 $ 146,007 $ 118,869 $ 129,439 18 Disbursements: 19 Inventory Payments $ 220,320 $ 187,737 $ 144,839 $ 117,918 $ 128,403 20 Short-term Interest $ $ 752 $ 1,461 $ 2.125 $ 2,613 21 Other Payments $ 227,500 $ 213,281 $ 199,062 $ 145,188 $ 127,750 22 Total Disbursements $ 447,820 $ 401,771 $ 345,362 $ 265,231 $ 258,766 23 Beginning Cash Balance $ 25,000 $ 25,000 $ 25,000 $ 25,000 $ 25,000 24 Net Additions to Cash $ (225,723) $ (212,519) $ (199,355) $ (146,362) $ (129,328) 25 Unadjusted Cash Balance $ 30,000 $ (200,723) $ (187,519) $ (174,355) $ (121,362) $ (104,328) 26 Add: Current Borrowing $ $ 225,723 $ 212,519 $ 199,355 $ 146,362 $ 129,328 27 Ending Cash Balance $ 30,000 $ 25,000 $ 25,000 $ 25,000 $ 25,000 $ 25,000 28 29 Cumulative Borrowing $ $ 225,723 $ 438,243 $ 637,597 783,960 $ 913,287 30 Minimum Cash Balance 25,000 31 Discount for Paying in Same Month 2.00% 32 Annual Interest Rate 4.00% 33 Monthly Interest Rate 0.33% 34 35 Total Interest Cost $ 6,951.74 36 37 b) Bob Loblaw, the president, is considering stretching out its inventory payments. He believes that it may be less expensive to borrow from suppliers than from the bank. He has asked you to use the Scenario Manager to see what the total interest cost for this time period would be if the company paid for 0%, 10%, 30%, or 40% of its inventory purchases in the same month. The remainder would be paid in the following month. Create a scenario summary, and describe whether the results support Bob's belief (Analysis, Identify and Access Needed Information & Use Information Effectively to Accomplish a Specific Purpose 12 pts). c) Interpretation: please describe where you obtained information to develop analyses and interpret the results. Please include your written description in the "interpretation tab. If you do not complete this section with your words or leave the interpretation tab as a blank, you receive 0 point in this HW