This project is designed to reinforce the concepts of managerial accounting and cash flows that are presented in ACC 3020 and to help refine your Excel skills. 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 and determine how much product you will need to purchase 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,500. You purchase each bottle of Pepsi for $.75 each and your selling price is $2.50 each. You must pay WSU rent of $150 and 10% of sales for your stand at the game. Your labor costs are $100 per game and 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 2 bottles of Pepsi for every 15 people in attendance. When the outside temperature is below 45 degrees, that ratio goes to one Pepsi for every 20 individuals in attendance, if its over 90 degrees, the ratio goes to 1 bottle for every 4 individuals. c. Theft/breakage (breakage) is an issue at the game, on average; one bottle is missing for every 45 bottles on hand at the start of the game. 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 75 bottles of Pepsi . iii. Expected attendance for the game is 9,500 and the outside temperature is expected to be 82 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 600 units at the game. I should be able to change this number and your schedule should be able to compute new amounts for all of 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 costs may be reflected as a separate line item, 4. less- the rent paid to WSU ($150 plus revenues X 10%), 5. Less- Labor costs of $100, 6. to arrive at taxable income, 7. less Federal income taxes (taxable income x 35%), 8. To arrive at net income (taxable income less Federal income taxes). 9. 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 (operating activites). 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-you may assume you have no liabilities before or after the game (all inventory purchases and expenses are paid in cash when incurred) and your change in inventory values before the game and after the game would go into the determination of net cash flows provided from operating activities, just as its used to prepare a statement of cash flows.