You have   just been hired as a new management trainee by XYZ Co, a distributor of   earrings to various retail outlets located in shopping malls across the   country. In the past, the company has done very little in the way of   budgeting and at certain times of the year has experienced a shortage of   cash.                               Since you are well   trained in budgeting, you have decided to prepare comprehensive budgets for   the upcoming second quarter in order to show management the benefits that can   be gained from an integrated budgeting program. To this end, you have worked   with accounting and other areas to gather the information assembled   below.                               The company sells many   styles of earrings but all are sold for the same price ($10 per pair). Actual   sales of earrings for the last three months and budgeted sales for the next   six months follow (in pairs of earrings):                                                                                                           January (actual) 20000                                     February (actual)  26000                                     March (actual)  40000                                     April (budget)  65000                                     May (budget)  100000                                     June (budget)  50000                                     July (budget)  30000                                     August (budget)  28000                                     September (budget)  25000                                                                                                                                                       
 The   concentration of sales before and during May is due to Mother’s Day.   Sufficient inventory should be on hand at the end of each month to supply 40%   of the earrings sold in the following month.
    Suppliers are paid $4 for a pair of earrings. One half of a month’s   purchases is paid for in the month of purchase – the other half is paid for   in the following month. All sales are on credit, with no discount and payable   within 15 days. The company has found, however, that only 20% of a month’s   sales are collected in the month of sale. An additional 70% is collected in   the following month and the remaining 10% is collected in the second month   following the sale. Bad debts have been negligible.
    Monthly operating expenses for the company are given below:                                                                                                            Sales   commissions  4% of sales                                                                                                                   Fixed:                                      Advertising  $2,00,000.00                                     Rent  $18,000.00                                     Salaries  $1,06,000.00                                     Utilities  $7,000.00                                     Insurance  $3,000.00                                     Depreciation  $14,000.00                                                                           Insurance is paid on an   annual basis, in November of each year.                                                                                                                The company plans to   purchase $16,000 in new equipment during May and $40,000 in new equipment   during June – both purchases will be for cash. The company declares dividends   of $15,000 each quarter, payable in the first month of the following quarter.   
    A listing of the company’s leger accounts as of March 31st   are given below:                                                                     Assets                                       Cash……………………………………..………………………………………………………………………………………………..$74,000                               Accounts receivable   ($26,000 February sales. $320,000 March sales)………………………………….….$346,000                                Inventory…………………….………………………………………………………………………………………………………..…104,000                               Prepaid   Insurance…………………………………………………………………………………………………………..…….…..21,000                               Property & Equipment   (net)…………………………………………………………………………………………….……..950,000                               Total   Assets:…………………………………………………………………………………………………………..…………..$1,495,000                               Liabilities &   Stockholder’s Equity                                      Accounts   Payable……………………………..………………………………………………………………………………..$100,000                               Dividends   Payable………………………………………………………….………………………………………………..…15,000                               Common   Stock……………………………………………………………..……………………………………………………..800,000                               Retained   Earnings…………………………………………………………………………………………….……..…………..580,000                               Total Liabilities &   Stockholders’ Equity……………………………………………………………….……..……$1,495,000                                The company maintains a   minimum cash balance of $50,000. All borrowing is done at the beginning of a   month – any repayments are made at the end of a month.                            The company has an   agreement with a bank that allows the company to borrow in increments of   $1,000 at the beginning of each month. The interest rate on these lands is 1%   per month and for simplicity we will assume that interest is not compounded.   At the end of the quarter, the company would pay the bank all of the   accumulated interested on the loan and as much of the loan as possible (in   increments of $1,000) while still retaining at least $50,000 in cash.                                                                                  Required (PLEASE PRESENT   IN EXCEL):                                     1. Prepare a master   budget including sales budget and merchandise purchase budget                                  2. Schedule of expected   cash collections                                     3. Schedule of expected   cash payments                                     4. Cash budget                                       5. Budgeted income   statement