Using the module problem as an example of a budget for a retail firm, explain what might happen if an economic disaster strikes in the immediate surrounding community. Assume that everything had been going as planned up to that time. My Time, Inc. markets high quality sundials all over the world. The firm has a contract with an artisans' co-op in Greece and is able to acquire a wide variety of sundials at a fixed price. At the end of 2020, the firm reported the following balance sheet Cash $13,200 A/R 252,800 Inventory 20,400 Equipment (net) 221,228 A/P Loan Payable Common Shares Retained Earnings $360,000 20,000 50,000 77,628 My Time prepares rolling budgets for the upcoming three months. Information for the budget for the first quarter of 2021 follows: The firm predicted unit sales for January, February, March and April of 3,500, 5,000, 4,000 and 4,500 respectively. The firm has a policy of keeping inventory on hand of 15% of the following month's sales. The firm maintains a minimum balance in the cash account of $20,000. 80% of all sales are on account. Of the sales on account, 30% are collected in the month of the sale, 40% are collected in the following month and 25% are collected in the month after that. The cost per unit is $40. In addition to product cost, the firm incurs commissions of 5% of sales, depreciation on equipment of 8,400 per month, administrative costs of 3% of sales, rent of $82,000 per month and insurance of $26,000 per month. All purchases are on account. 40% of purchases are paid for in the month following the purchase and the remaining 60% are paid for two months after the purchase. Actual sales for the month of November were 8,000 units and actual sales for December were 3,000 units. Actual purchases in November were $300,000 and actual purchases in December were $100,000. The price per unit is $80 The firm can borrow money from the bank at any time. The loan carries an interest rate of 3% (per year). Any borrowing must take place at the beginning of the month in which the funds will be required. Any repayments must take place at the end of the month in which they will become possible. Interest must be paid each month on the amount outstanding as of the end of the prior month. 1. Prepare a sales budget in proper format. 2. Prepare a purchases budget in proper format. 3. Prepare a cash collections budget in proper format. 4. Prepare a cash disbursements budget in proper format. 5. Prepare a cash budget in proper format. Quarter 1 SP 80 Unit Sales Month 1 Month 2 Month 3 Month 3 + 1 3,500 5,000 4,000 4,500 Required EI BI (units) Unit cost 15% of following months sales 510 40 3 20% Collection history Number of months Cash Percentage Month of Month after 2nd month after Unit sales month back 2 months back 3 months back 3,000 8,000 30% 40% 25% 2 Payment history Number of months month of month after 2nd month after Unit purchases month back 2 months back 7,500 2,500 0% 40% 60% Beg bank balance Required balance Commissions Administrative costs Rent Insurance 13,200 20,000 5% Sales 3% Sales 82,000 month 26,000 Loan outstanding Interest accrued Interest rate 20,000 0 3% Sales Budget 1 3,500 80 $280,000 Unit Sales SP Sales Total 12,500 given 2 5,000 80 $400,000 3 4,000 80 $320,000 80 given $1,000,000 unit sales * SP Unit Sales EI Unit Required BI Purchases Budget 1 2 3,500 5,000 750 600 4,250 5,600 510 750 3,740 4,850 40 40 $149,600 $194,000 3. 4,000 675 4,675 600 4,075 40 $163,000 Total 12,500 first line of sale budget 675 15% of following month unit sales; March = april sales (4,500 * .15) 13,175 units sales + EI 510 Jan = Bi from BS / unit cost (20,400 / 40); El from last month BI for current month 12,665 units required - BI 40 given $506,600 units 2 B purchased * unit cost Units 2 B Purchased Cost Purchases 3 0 11 12 1. 0 Cash Collections Budget 1 2 128,000 0 76,800 48,000 67,200 89,600 0 96,000 0 0 56,000 80,000 $328,000 $313,600 Total 128,000 Nov. unit sales * SP* on account % (80%) * collection in Jan (25%) 124,800 Dec. unit sales * SP* on account %(.80%) *[collection in Jan (40%); collection in Feb. (25%)] 212,800 Jan. sales * SP* on account %(.80%) *[collection in Jan (30%); collection in Feb. (40%); collection in Mar. (259 224,000 Feb. sales * SP on account %(.80%) *[collection in Feb (30%); collection in Mar. (40%)] 76,800 Mar. sales * SP* on account %(.80%) *collection in Mar. (30%) 200,000 Sales for month * (1 - percentage on account) $966,400 sum down each column 2 56,000 128,000 76,800 64,000 $324,800 3 Cash Total 3 Cash Disbursements Budget 13 2 60,000 0 120,000 180,000 0 59,840 0 0 11. 12 1 2 3 Total Total 60,000 Purchases for November * % payment in Jan. (60%) 300,000 Purchases for December * [% payment in Jan (40%); % payment in Feb. (60%)] 149,600 Purchases for January * [% payment in Feb. (40%); % payment in Mar. (60%)] 77,600 Purchases for February * % payment in Mar. (40%) 89,760 77,600 0 0 0 0 0 180,000 239,840 167,360 587,200 sun down each column 2 Cash Budget 1 13,200 328,000 341,200 20,000 313,600 333,600 3 20,000 324,800 344,800 Total 13,200 given; balance for Jan; balance for Feb. 966,400 last row of cash collections budget 979,600 sum al. Collections Available Payments nases missions inistative costs ance 180,000 14,000 8,400 26,000 82,000 310,400 30,800 239,840 20,000 12,000 26,000 82,000 379,840 -46,240 167,360 16,000 9,600 26,000 82,000 300,960 43,840 587,200 last row of cash disbursements budget 50,000 sales for month times commission percentage 30,000 sales for month times percentage 78,000 246,000 given 991,200 sum of payments -11,600 Cash available less total payments Payments sver/short w 0 0 50 66,263.125 23.125 0 $20,000 66,263.125 if cash over/short is less than minimum balance, borrow enough to pay interest and bring balance to minimum 261.908 loan outstanding at end of prior month time interest percentage divided by 12 34,401.217 if cash over/short is greater than minimum balance, pay interest, then repay enough to get balance to minimum $20,000 cash over/short + borrowing - interest - repayments 188.783 23,651.217 $20,000 10,750 $20,000 al Balance end of prior month 9,250 75,513.125 51,861.908