In this exercise, you are the owner/manager of a small business which has the right to operate a concession stand at the WSU football games. Your only product is Pepsi. Before each game you must evaluate your inventory of product (i.e. number of bottles of Pepsi) left from the last game, determine how much product you will need to buy before the next game based on your expectations of units to be sold at the game. Based on the number of units actually sold, you will then compute revenues, cost of goods sold, net income and cash flows based on your results from the game. Certain items will be given as assumptions to help you. You will determine other items, such as your purchases for the upcoming game, revenues, cost of sales, rent expense, net income and net cash flows, etc. based on the facts provided. The inputs to the spreadsheet should be limited to the items identified below and your schedule must use internal formulas, summations, etc. to compute and keep track of the requested information. Items not internally computed from the inputs will result in deductions from your grade.

You have an initial cash balance of $1,000. You purchase each bottle of Pepsi for $1.1o each and your selling price is $2.20 each. You must pay WSU 25% of sales as rent for your stand at the game. Under GAAP these types of rental payments are often referred to as percentage rentals. You must pay the US Government 35% of gross profits in Federal income taxes.

Historically you know that the weather and attendance are the two primary drivers of sales volume. On average you sell 1 bottle of Pepsi for every 10 people in attendance. When the outside temperature is below 40 degrees, that ratio goes to one Pepsi for every 20 individuals in attendance, if its over 80 degrees, the ratio goes to 1 bottle for every 5 individuals.

Theft/breakage (breakage) is an issue at the game, on average; one bottle is missing for every 50 sold. This expense may be included as part of your cost of goods sold or reflected as a separate line item on your schedule.

Other assumptions include:

i. Your beginning inventory for last years last game is 50 bottles,

ii.Your desired ending inventory is 2% of expected attendance.

iii.Expected attendance for the game is 4,200 and the outside temperature is expected to be 75 degrees. If your Excel schedule computes expected product sales volumes from the attendance and weather input using if statements, that will be worth 5% of the grade on the project.

You Excel spreadsheet should include two sections, the first section is your initial expectations for purchases and budgeted cash balances. Your inputs should be:

beginning cash,

beginning inventory in units,

expected attendance, and

weather (expected temperature)

Your output should be:

Quantity of Pepsi to purchase (in units and cash - determined as expected sales less beginning inventory plus desired ending inventory),

Quantity on hand (in units and cash), and

Ending cash (beginning cash less purchases).

Your second portion of your schedule should be your modified income and cash flow statements. Your inputs should be:

Units sold, (be careful you dont sell more than you have on hand). You may assume that you sold 475 units at the game. I should be able to change this number and your schedule should be able to compute new amounts for the outputs below.

Your output should be:

revenues for the game (units X sales price),

less- cost of goods sold (adjusting for breakage computed as units sold plus breakage X cost per unit). As stated above, breakage may be reflected as a separate line item,

less- the percentage fee paid to WSU (revenues X 25%),

to arrive at taxable income,

less Federal income taxes (gross profit x 35%),

To arrive at net income (taxable income less Federal income taxes).

This net income is now the starting point of your cash flow statement to convert net income into net cash flows provided by (used by) the game. The net change in cash and should then be added to your beginning cash before the game from schedule 1 to arrive at your ending cash balance. Hint-your change in inventory values before the game and after the game would go into the determination of net cash flows provided from the game, just as its used to prepare a statement of cash flows.

Game 1

Schedule 1

Pre-Game purchases and preparation --------------------------------------------------------------------------

Inputs

Beginning cash $ 1,000

Beginning inventory in units 50

Expected attendance 4,200

Weather 75

Purchase price of units $ 1.10

Sales price of units $ 2.20

Outputs (internally generated)

Units Dollars

Quantity to purchase xxx $xxx

Total quantity on hand xxx $xxx

Cash after purchases but before game $xxx

Schedule 2

After Game Results ---------------------------------------------------------------------------------------

Inputs

Units sold (assuming you had sufficent inventory) 475

Outputs (internally generated)

Revenues

(limited to quantity on hand less Breakage) $ xxx

Cost of goods sold

Sold xxx

Breakage xxx

Percentage rents to WSU xxx

Taxable income xxx

Income taxes xxx

Net income xxx

Adjustments to net income to arrive at cash flows

List items xxx

Net cash provided by game xxx

Cash going into game xxx

Cash after game $xxx

=======