SimulatorCo Master Budget Case (spr20) 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:

Month

Forecasted Unit Sales

Month

Actual

Unit Sales

July

32000

April

20000

August

38500

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 3,000

Insurance expired 3,750

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 (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 $40,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 $600,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

30,000

Fixed Assets, net of Acc. Depreciation

247,200

Total Assets

1,496,250

Liabilities & Equity

A/P (for inventory purchases)

$280,800

Dividend Payable

50,000

Common Stock

350,000

Retained Earnings

815,450

Total Liabilities & Equity

1,496,250

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, four salaried supervisors who make $75,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 both 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. 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.

Sheet 3 (name = Cash). Use my format. Your cash budget won't look like a SCF.

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 $28 and September sales to 55000 units.

I was wondering for the financing section how you determine how much money to borrow when there is a deficit. Thanks!