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:

Month / Forecasted Unit Sales / Month / Actual
Unit Sales
July / 32000 / April / 20000
August / 38000 / 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) 3% of sales dollars
Fixed:
Wages and salaries $82,000
Rent 5,000
Utilities 3,300
Supplies 3,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 (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:

Assets
Cash / $22,500
A/R / 735,750
Inventory / 460,800
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.

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 separately—might only be one)

minus disbursements (list each of them separately)

gives temporary balance

followed by a financing section

-- Borrow funds

--Pay principal

--Pay interest)

ending cash balance.

Sheet 4 (name = FinStmt)

6.A budgeted contribution formatincome 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 CD, 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 September sales to 55000 units, change the selling price for the quarter to$27.50.

Flash drive and Hard Copy Instructions and Grading.

Due date: March 14

CD/Flash drive (or just email it to me):

DO NOT save multiple versions of SimulatorCo_Yourname.xls. You don't save a new file for every "what-if" analysis management wants to run. Management may easily want to look at 20 scenarios, or more.

Hard Copies:

Eight sheets of paper (two four-page versions). NO landscape printing. You probably won't need File, Print, Preview, Page Setup, Fit to One, but use it if you do. Printing can sometimes be time consuming. Allow time.

After making the two changes in the part "8b" assumptions, you are to print out whatever your spreadsheet results are. Not everyone will get a 100. I WANT YOUR answers, right or wrong. Do not manually adjust the "8b" HARD-COPY version spreadsheet in any way. Turn in whatever YOUR spreadsheet produces.

If you have questions, speak with (or email) me. This is an individual assignment—open book, open notes, but no other people. Thanks and good luck.

This project takes time to complete. Most grades should be A, B, and C. Most of the accounting is very basic. I have provided most of the spreadsheet formatting you are to use. The format of the income statement is the contribution format (the one with variable and fixed costs). The balance sheet format is the standard format. The cash budget is set up differently than a Statement of Cash Flows (SCF). It is not broken down by operating, financing, and investing activities. The format is cash in, cash out, and financing, if needed.

If your balance sheet does not balance (A=L+O/E) at s.p. = $27, it probably means you are making an accounting error (but it could be a spreadsheet error). Your balance sheet won't balance at s.p. = $27.50, unless you can figure out the financing section of the cash budget and the related programming (=if statements). This is reasonably challenging.This should be the last thing you attempt.

Excel's Syntax is =if(condition, true, false).

Nested ifs are allowed. Syntax =if(condition, true, if(condition,true, false)).

Compound ifs syntax =if(and(condition1,condition2, true, false) (use either "and" or "or")

For example, if you want to do something if Y is greater than 10, but less than 20, you use a compound if. You probably have not used a compound if statement yet. I will show in class.

Refer to Excel’s help for more guidance. You may need to use some of Excel's function commands.

Your little "programming section" should be listed beneath the cash budget.

The time-consuming Excel challenge is the financing section. If you perform a what-if analysis, varying sales or other items, obviously the amounts you borrow and repay will vary. Your financing section must accommodate all possible changes. I am placing a two-hour time limit on this section. The rest of the exercise should take 4-10 hours. You must first complete the assumptions sheet. Solving this problem manually takes 90-120 minutes. You may wish to first solve it by hand and then do it in Excel. That's up to you.

Hint1: You should understand why sheet number one (Assumptions) must be completed first.

Hint2: You should envision your worksheets 2,3, and 4 as "protected." But don't really protect them because I will play around with a few of your cells. If you did this project at work, you would definitely protect sheets 2,3, and 4, so others would not mess with your formulas.

A good spreadsheet has four characteristics:

  1. it is accurate
  2. it is clear/logical and professional looking
  3. it is transparent (ties closely with clarity)
  4. it is easy to modify (what-ifs, etc.)