SimulatorCo Master Budget Case (spr11) This is an INDIVIDUAL assignment.
You have just been hired as a management consultant by SimulatorCo, Inc., a nationwide distributor of flight simulator video games. The company has an exclusive franchise on distribution of TopFlight, and sales have grown so rapidly over the last few years that it has become necessary to formalize the quarterly budgeting process. Your assignment is to prepare a master budget for the third quarter, starting July 1. This is an important quarter for the company, so they need to know inventory, sales, cash flow projections, etc. You are anxious to make a favorable impression on your new client and have assembled the information below.
The company desires a minimum ending cash balance each month of $20,000. The video games are forecasted to sell for $27 each. Actual second quarter sales and forecasted third quarter sales in units are:
Forecasted Unit Sales
The buildup in sales before and during the month of September is due to the Christmas season. Retailers like to have inventory for the Christmas season, which begins in October. Ending inventories are supposed to equal 80 percent of the next month’s sales in units. The video games cost the company $18 each.
Purchases are paid for as follows: 50 percent in the month of purchase and the remaining 50 percent in the following month. All sales are on credit, with no discount, and payable within 30 days. The company has found, however, that only 25 percent of a month’s sales are collected by month-end. An additional 50 percent is collected in the month following, and the remaining 25 percent collected in the second month following. Bad debts have been negligible.
The company’s monthly operating expenses are given below:
Sales commissions 5% of sales dollars
Temporary workers (temps) 3% of sales dollars
Wages and salaries $82,000
Insurance expired 2,400
Depreciation 15,000 (prior to any purchases or disposals of equipment)
SimulatorCo has a steady full-time work force. It uses some temps as business fluctuates each month (in this way, they don’t have to deal with laying off full-time employees, etc.). All cash operating expenses are paid for during the month incurred. New fixed assets with an estimated life of five years will be purchased during August for $30,000 cash. Depreciation of new equipment begins in the month following the month of purchase. The company has declared dividends of $50,000 each quarter, payable in the first month of the following quarter. However, the third quarter dividend will be $100,000 if Operating Income exceeds $550,000. The company’s balance sheet at June 30 is given below:
Prepaid Insurance 19,200
Fixed Assets, net of Acc. Depreciation 242,700
Total Assets 1,480,950
Liabilities & Equity
A/P (for inventory purchases) $280,800
Dividend Payable 50,000
Common Stock 300,000
Retained Earnings 850,150
Total Liabilities & Equity 1,480,950
The company can borrow money from its bank at “the prime rate plus 2%” or 10 percent (because the prime rate is now 8%) annual (SIMPLE, no compounding) interest. SimulatorCo borrows money when it has a shortage. It repays existing loans when it has excess funds. If you are short in a given month, assume that you borrow funds as of the beginning of the month. If excess, repay portions of any loans as of the end of the month. It is impossible to have an excess and a shortage in the same month. Interest is paid only on the principal that is repaid. Both borrowing and repayments of principal must be in round $1,000 amounts. The interest is whatever it is. Income taxes are to be ignored in this exercise.
Required: follow my format PRECISELY. Four sheets. NO landscape printing.
Prepare a master budget for the third quarter. Include:
Sheet 1 (name=Assumptions) is your Assumptions sheet. This sheet is critical. The primary reason for using Excel to prepare your budget (rather than doing it manually) is so that you can perform a sensitivity (what-if) analysis, based upon these assumptions.
Sheet 2 (name = Schedules) Include 1-4 below. These support the three Fin Stmts.
1. A sales schedule/budget showing units and dollars by month and in total.
2. A schedule of budgeted cash collections from customers by month and in total.
3. An inventory purchases schedule in units and in dollars, by month and in total.
4. A schedule of budgeted cash payments for inventory purchases by month and in total.