SimulatorCo Master Budget

You're a management consultant by SimulatorCo, Inc., a nationwide distributor of flight simulator video games. 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.

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:

Month	Forecasted Unit Sales	Month	Actual Unit Sales
July	32000	April	20000
August	39000	May	25000
September	60000	June	28000

October 40000

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:

Variable:

Sales commissions 5% of sales dollars

Temporary workers (temps) 2% of sales dollars

Shipping $.50 per unit

Fixed:

Wages and salaries see labor information

Rent 6,000

Utilities 3,300

Supplies 2,000

Insurance expired 2,400

Depreciation 15,000 (prior to any purchases or disposals of equipment)

Miscellaneous 3,700

SimulatorCo has a steady full-time work force. It uses some temps as business fluctuates each month. 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:

Assets
Cash	$22,500
A/R	735,750
Inventory	460,800
Prepaid Insurance	19,200
Fixed Assets, net of Acc. Depreciation	247,200
Total Assets	1,485,450
Liabilities & Equity
A/P (for inventory purchases)	$280,800
Dividend Payable	50,000
Common Stock	300,000
Retained Earnings	854,650
Total Liabilities & Equity	1,485,450

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.

Labor information:

Simulator has an owner-manager who makes $120,000, one operations manager who makes $80,000, two salaried supervisors who make $50,000 each and ten hourly workers who make $400 per week. Assume 4 weeks in each month. Simulator hired two new people, an IT person and one in marketing at $60,000 each (they start on July 1). It also hired two new hourly workers at $10/hr or $400 per week. Raises are given in July effective August 1. Everyone except the owner and the four new hires received a 10% raise. There was no overtime in July, but the hourly workers each worked 10 hours of overtime during August and September. In addition, Simulator uses Temps which average about 2% of Sales each month. Simulator does not pay benefits (ignore FICA and other payroll tax issues).

Required: follow my format PRECISELY.

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.

Sheet 3 (name = Cash). Use my format.

5. A cash budget by month and in total (how many columns is that?). All cash payments are included in this budget. The format of the cash budget should be:

beginning balance ; plus receipts (list them separatelymight only be one) ;minus disbursements (list each of them separately) ;gives temporary balance; followed by a financing section ; Borrow funds as needed; Pay principal, as able; Pay interest; Ending cash balance.

Sheet 4 (name = FinStmt)

6. A budgeted contribution format income statement for the three-month period ending September 30. (follow these directions)

7. A budgeted balance sheet as of September 30.

8. Turn in your flash drive with SimulatorCo.xls, and two hard copies of your (4 page) budgets.

1. One 4-page master budget using the facts provided in this writeup.
2. Make the changes below to your spreadsheet and print out the same four sheets (that will now have different amounts).

Change the selling price for the quarter to $27.50 and September sales to 55000 units, August to 38000 units.