Computer Project
REQUIRED:
Use the following company information (next page) to complete the master budget for Evening Accessories.
You are given an Excel template (Template_MB) both to give you guidance in developing the budgets that make up the master budget and to aid in my grading.
You are to use Excel efficiently and effectively.
That includes using:
- Data input area (see Excel template). I entered a few items in this area as a guide. There are no calculations in the Data input area.
- Cell references to the data input area or preceding schedules should be used as applicable. You will see a couple of formulas entered to give you examples of what I expect in formulas/cell references.
- Cell formulas for all summations, calculations, etc. There is an example given in the Excel spreadsheet for the appropriate formula to use for schedules with a total column to ascertain that the total row and total column agree. You will use variations of this formula for other schedules.
- The first row and the total row of a schedule should be dollar ($) format. All other cells should be comma format.
Use ZERO decimal places except for unit prices and unit costs.
- Use underlines and double underlines as appropriate.
- Do NOT add columns to the schedules. The master budget is for the 3-month period only.
That will impact your formulas.
THERE ARE 2 PARTS TO THE PROJECT:
Part 1: Complete: Data Input area Sales Budget, by month and in total Schedule of Expected Cash Collections From Sales, by month and in total Merchandise Purchases Budget in units and in dollars. Show the budget by month and in total. Schedule of Expected Cash Disbursements for Merchandise Purchases, by month in total. Do NOT continue to Part 2 before submitting Part 1 for prescreening and extra help if needed. Submit your Part 1 by the time you submit your Chap 10 homework.
Part 2: Correct Part 1 Complete: Cash Budget. Show the budget by month and in total. Determine any borrowing that would be needed to maintain the minimum cash balance of $50,000. Budgeted Income Statement for the 3-month period ending June 30. Use the contribution approach. List all expenses individually. Budgeted Balance Sheet as of June 30. DUE: Project due date is clearly indicated and highlighted in your Syllabus.
Computer Project: Master Budget and Schedules (Evening Accessories)
You have just been hired as a new management trainee by Evening Accessories, 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):
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 are 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 sale. Bad debts have been negligible.
Monthly operating expenses for the company at the present time are given below:
The insurance is paid on an annual basis on November 1 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 at the end of each quarter, payable in the first month of the following quarter.
A listing of the company's ledger accounts as of March 31 is given below:
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 annual interest rate is 12%. Interest is computed and paid at the end of each quarter on all loans outstanding during the quarter.
Required:
PART 1: Prepare a master budget for the three-month period ending June 30. Include the following detailed budgets:
1. a. A sales budget, by month and in total.
b. A schedule of expected cash collections from sales, by month and in total.
c. A merchandise purchases budget in units and in dollars. Show the budget by month and in total.
d. A schedule of expected cash disbursements for merchandise purchases, by month and in total.
PART 2: Complete the master budget for the three-month period ending June 30. Include the following detailed budgets:
A cash budget. Show the budget by month and in total. Determine any borrowing that would be needed to maintain the minimum cash balance of $50,000.
2. A budgeted income statement for the three-month period ending June 30. Use the contribution approach.
3. A budgeted balance sheet as of June 30.
4. CORRECT ALL ERRORS IN PART 1 BEFORE COMPLETING PART 2 BUDGETS.
Additional comments to assist your completion of these last three budgets:
Cash budget: - This budget has a column for each month in the quarter and a total column. Be careful in the total column when it comes to beginning and ending balances. Also, in some cases you can/must add across the three monthly columns to get the amount for the total column, but in other cases you cannot.
In other words, your formulas should generally take you vertically down the total column. Review my chapter notes on this point if necessary. - On the template, I have given titles for a couple of the cash disbursements. You should fill in the rest in the available space, along with the correct dollar amounts.
- The financing section is somewhat complicated. We will generally follow the instructions in the problem on borrowing and repaying, supplemented by a few clarifications, modifications, and assumptions as follows: NOTE: Assume that borrowing and payment of interest can be done in any amount, but that repayment of principal must be in round $1,000 amounts.
1. For April and May, we will either borrow (if necessary) or do nothing. We will never repay interest or principal in April or May. Borrowing can be handled with an IF statement: if the "cash excess (deficiency)" amount is less than the minimum cash balance amount, then borrow that minimum cash balance amount minus the "cash excess (deficiency)" amount; otherwise, borrow zero.
2. For June, we will either borrow (if necessary), or repay (if enough cash is available), or do nothing. Borrowing is handled like April and May. If some kind of repayment is possible, a good way to handle the possibilities is with a separate set of calculations below the cash budget (see template). Some IF statements and the ROUNDDOWN function will be involved.
To facilitate your completion of these calculations (and you'll be very glad I'm doing this), a brief explanation of each line in this set of calculations is as follows:
(a.) If the cash excess (deficiency) amount is greater than the minimum cash balance amount, the cash available for repayment is the cash excess (deficiency) amount minus the minimum cash balance amount; otherwise, the cash available for repayment is zero.
(b.) The problem states that interest is calculated and paid at the end of each quarter on all loans outstanding. Obviously, this can happen only if there is enough cash to do so.
Also, interest is calculated only for April and May borrowing (if any) because, if we had to borrow in June, we obviously wouldn't be in a position to pay anything back. Interest on quarterly borrowing is the April borrowed amount multiplied by the interest rate and by 3/12 plus the May borrowed amount multiplied by the interest rate and by 2/12. (We use 3/12 and 2/12 because the problem states that borrowing must be done at the beginning of the month and repayment done at the end of the month.) Interest should be rounded to the nearest whole dollar.
(c.) We will assume that the company will pay all of the interest if enough cash is available; otherwise, it will pay nothing. Paying only part of the interest makes spreadsheets in subsequent months very messy. To calculate this interest payment (all-or-nothing), we look at the two lines above. If the cash available for repayment is less than the interest on quarterly borrowing, then we pay zero; otherwise (i.e., if the cash available is equal to or greater than the interest), we pay the interest on quarterly borrowing. Again, the above assumptions state that interest payments can be in any amount.
(d.) If the all-or-nothing interest payment is greater than zero (i.e., if we are making an interest payment), the remaining cash available for principal repayment is the cash available for repayment minus the all-or-nothing interest payment; otherwise, it is zero. Here, we will assume that if we have cash, but not enough to pay the entire interest payment, then we will not use the cash to pay off any of the principal either.
(e.) The above assumptions state that payments of principal must be in increments of $1,000. Therefore, we must round down the amount in the line immediately above to the nearest $1,000. Go to the Help menu within Excel and type in ROUNDDOWN to get the necessary information for calculating remaining cash available rounded down.
(f.) The total borrowing for April and May is the sum of the amounts (if any) on the Borrowing line in the April and May columns.
(g.) Because we are repaying in increments of $1,000, we will also ROUNDDOWN the number in the line immediately above to the nearest $1,000 to get total borrowing for April and May rounded down.
(h.) If the remaining cash available rounded down is greater than the total borrowing for April and May rounded down, the principal repayment is the latter amount; otherwise, it is the former amount. 3. Returning to the financing section of the cash budget, and once this set of calculations is completed, the Repayment (principal) amount is taken from the Principal repayment line and the Interest payment amount is taken from the Interest payment (all or nothing) line.
Budgeted income statement: - The income statement uses the contribution approach (per the problem's instructions) and is set up accordingly. All of the main totals and subtotals should be in Column E. The variable and fixed expenses should be listed individually in Column D and subtotaled in Column E. Budgeted balance sheet:
- The ending balance sheet is similar in format to the beginning balance sheet given in the data section. If you prepare it correctly, it should balance. - Fixed assets include property, plant, and equipment (PP&E). Assume that no PP&E was sold during the quarter.
Also assume that there has been no change in the capital stock account. - Information for the various accounts should come from the beginning balance sheet, previous reports, and the data section.
Similar to the approach we used in the cash budget, a few calculations appear on the template below the balance sheet to help you obtain this information. These calculations should be self-explanatory.