I have accounting data of Sales of a Car Company, but it is really confusing to understand.

Could you please present it in tables, or a excel sheet or even hand-written tables but with clarity. Please add attachment and however you think is appropriate.

The data is as follow:

Question One and Two JAN FEB MARCH APRIL MAY JUNE Sales in units $ 20,000 $ 24,000 $ 28,000 $ 35,000 $ 45,000 $ 60,000 Selling price ($) $ 8 $ 8 $ 8 $ 8 $ 8 $ 8 Total S.P ($) $160,000 $192,000 $ 224,000 $ 280,000 $ 360,000 $ 480,000 Cash Collection 25% Month of sale $ 40,000 $ 48,000 $ 56,000 $ 70,000 $ 90,000 $ 120,000 50% Following month $ 80,000 $ 96,000 $ 112,000 $ 140,000 $ 180,000 25% Second month $ 40,000 $ 48,000 $ 56,000 $ 70,000 total cash collected $ 230,000 $ 286,000 $ 370,000 Notes The company collects the sales from the debtors at the rate of 25% on the month of sale, 50% of sales on the following month and the remaining 25% on the second month after sales. The amounts that will form the accounts receivable will be 25% of May sales amount and 75% of the sales amount that have been recorded in the month of June. The selling price per unit will be $8 with the amount being constant throughout the quarter ending 30th June.

Question Three and Four March April May June July opening stock $ 25,200 $ 31,500 $ 40,500 $ 54,000 sales $ 28,000 $ 35,000 $ 45,000 $ 60,000 $ 40,000 closing stock; 90% of next month sales $ 31,500 $ 40,500 $ 54,000 $ 36,000 purchases in Units $ 34,300 $ 44,000 $ 58,500 $ 42,000 Total Purchases cost in $ $ 171,500 $ 220,000 $292,500 $ 210,000 payments Month of purchase $ 85,750 $ 110,000 $146,250 $ 105,000 next month of purchase $ 85,750 $110,000 $ 146,250 $ 105,000 total cash on purchases $ 195,750 $256,250 $ 251,250 $ 703,250 Notes The company pays its creditors 50% on the month of purchase and 50% on the next month after purchase. The closing stock of each month is 90% next month sales amount. In order to get the purchases for the period, the closing stock and the sales units are added and then the opening stock is subtracted from the two summation. The value of purchases is then multiplied by the cost in order to get the total value. The month of June purchases is paid by 50% and thus the remaining amount that is 50% is paid in the month of July. The remaining amount is the accounts payable at the end of the quarter. The cost per unit has been estimated to be $5 which will remain constant throughout the period.

Cash Budget For the months of April- June April May June total Receipts sales $ 230,000 $ 286,000 $ 3,700,000 $ 4,216,000 Loan received $ 1,000 $ 2,000 $ 3,000 $ 6,000

$ 231,000 $ 288,000 $ 373,000 $ 892,000

payments purchases $ 195,750 $ 256,250 $ 251,250 $ 703,250 sales commission $ 35,000 $ 45,000 $ 60,000 $ 140,000 wages $ 22,000 $ 22,000 $ 22,000 $ 66,000 utilities $ 14,000 $ 14,000 $ 14,000 $ 42,000 miscellaneous $ 3,000 $ 3,000 $ 3,000 $ 9,000 land $ 25,000 $ 25,000 Dividends $ 12,000 $ 12,000 loan interest $ 10 $ 20 $ 30 $ 60 $ 281,760 $ 365,270 $ 350,280 $ 997,310 net cash for the month $ (50,760) $ (77,270) $ 22,720 $ (105,310) opening balance $ 14,000 $ 10,000 $ 10,000 $ 34,000 Closing balance $ (36,760) $ (67,270) $ 32,720 minimum $ 10,000 $ 10,000 $ 10,000 $ 30,000 loan paid $ 6,000 $ 6,000 excess/deficit $ (46,760) $ (77,270) $ 16,720 cash at end of the quarter $ 26,720 Notes The company takes the loan which increases by $1000 every month. Thus the loan amount at the end of the quarter is $6000. The loan interest is 1% per month. The accumulated interest amount is paid at the end of the quarter. The principal amount for the quarter can be paid as the company is able to maintain the $ 10000 minimum balance Depreciation and insurance expense are not included in cash budget. Depreciation is a non-cash item thus cannot be included in the cash budget. The utilities, wages and salaries and miscellaneous expenses are payable every month. Land will be bought in May thus its amount should be deducted as a cash outflow. The dividends declared from the previous quarter will be paid in April The company will pay the principal amount given that the company can still pass the policy of minimum cash requirement of $10000. The company projected cash budget has deficits for the month of April and May and then records a surplus cash for the month of June. The company in order to finance its activities in the consequent months after recording a cash deficit is expected to take a bank overdraft.

Workings on sales commission and loan amounts

workings sales commission sales per month * sales commission April May June sales in units 35000 45000 60000 commission per unit $1 total in $ 35000 45000 60000

loan schedule April May June amount per month 1000 1000 1000 incremental for first month 1000 1000 incremental for second month 1000 total amounts 1000 2000 3000 interest payment 1% per month 1% 10 20 30