The purpose of this project is to give you experience creating a financing budgets, specifically the cash budgets using excel. Artz. Co is a retailer that is preparing its cash budget for 1st quarter. Provided below is information pertaining to Artz Co.'s expected sales, merchandise payments and expense information for 1st quarter 2021. 25% of sales are cash sales and 75% are on account. 40% of A/R are collected in the month of the sale and 60% in the month following the sale. $ January 110,000 $ February 99,000 $ March 106,000 Budgeted Sales Budgeted Pmts for merchandise: Merchandise Inventory Shipping $ $ 48,000 $ 5,000 $ 36,000 $ 4,200 $ 41,000 4,800 Relevant account balances from December 31, 2020 are: Cash $15,000 A/R $24,000 Loan/Payable $5,000 Borrowing from the bank can only be done in increments of $1,000 Monthly operating expenses paid in cash in the month incurred are: Sales commission that is 15% of sales Office salaries $25,000 Rent $9,000 Artz Co. requires an ending cash balance of at least $15,000, if the preliminary a balance is below this amount Artz Co. will borrow from the bank to achieve this balance. The bank charges Artz Co. 2.5% interest per month on loan balance. Required: Prepare a cash receipts budget for January, February, and March for Artz Co. Gray cells-number or formula entry Green cells- formula & sheet reference Blue cells- Formula Tangerine cells-sheet reference/Cell reference Prepare a Cash budget for each month January, February, and March for Artz Co. Green cells-formula & sheet reference Blue cells- Formula Tangerine cells- sheet reference/Cell reference you should combine merchandise payments into one group in cash budget March 99,000 $ 106,000 Budgeted Sales and Cash Pmt for Merchandise for 1st quarter 2021 for Artz. Co is provided below: January February Budgeted Sales $ 110,000 $ Budgeted Pmts for merchandise: Merchandise Inventory $ 48,000 $ Shipping $ 5,000 $ 36,000 $ 4,200 $ 41,000 4,800 % of total sales that is cash sales % of total sales that is A/R 25% 75% A/R collection in the month of the sale A/R collection in the month following the sale 40% 60% Balances for relevant accounts on Dec 31, 2020 Cash $ Accounts Receivable $ 15,000 24,000 Loan Payable to bank $ 5,000 amounts can only be borrowed from the bank in increments of $1,000 Montly operating expenses paid in the month incurred Sales commission % of sales Office Salaries $ Rent $ 15% 25,000 9,000 Minimum required ending cash balance $ Montly interest rate on beginning of month loan balance 15,000 2.5% Required: Prepare a cash receipts budget for January, February, and March for Artz Co. Gray cells-number or formula entry Green cells- formula & sheet reference Blue cells- Formula Tangerine cells-sheet reference/Cell reference Prepare a Cash budget for each month January, February, and March for Artz Co. Green cells-formula & sheet reference Blue cells- Formula Tangerine cells-sheet reference/Cell reference you should combine merchandise payments into one group in cash budget Artz. Co. Cash Receipts Budget For July, August, and September January February March Cash Sales Collections of prior month's receivables Collections of current month's receivables Total cash receipts Cash Budget For January, February, and March January February March Beginning cash balance* Cash receipts Total cash available Payments for Merchandise Sales commissions Office salaries Rent Interest on bank loan January February March Preliminary cash balance Additional loan from bank Repayment of loan to bank Ending cash balance $ $ $ Loan balance, end of month
