In this exercise, you are the owner/manager of a small business which has the right to operate a concession stand at 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.

a. You have an initial cash balance of $1,000. You purchase each bottle of Pepsi for $1.10 each and your selling price is $2.20 each. You must pay a 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.

b. 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.

c. 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.

d. 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:

1. beginning cash,

2. beginning inventory in units,

3. expected attendance, and

4. weather (expected temperature)

Your output should be:

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

2. Quantity on hand (in units and cash), and

3. 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:

1. 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:

2. revenues for the game (units X sales price),

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

4. less- the percentage fee paid to rent stand(revenues X 25%),

5. to arrive at taxable income,

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

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

8. 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.

***Can you please show excel formulas

***Can you make the if statement in excel for the quantity to purchase cover all three possibilities, not just one.

The revenues and everything else in the income statement that is dependent on units, can you make the means to limit the amounts by the units available as the assignment requires.

Breakage can not be a partial unit. There is a roundup function in excel.

The adjustments to net income are not the decrease in inventory.

Also, cash going into the game should be the amount after purchases and not the beginning balance.

Game 1 Schedule 1 Pre-Game purchases and preparation Inputs Beginning cash Beginning inventory in units Expected attendance Weather 1,000 50 4,200 75 Purchase price of units Sales price of units $ 1.10 $ 2.20 Outputs (internally generated) Units Dollars Quantity to purchase Total quantity on hand Cash after purchases but before game 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) Cost of goods sold Sold Breakage Percentage rents to WSU Taxable income Income taxes Net income Adjustments to net income to arrive at cash flows List items Net cash provided by game Cash going into game Cash after game