Please use excel and show formulas used. Explain decisions/calculations.

Background/Start-up Data You are starting a fresh, made to order, lemonade stand at the mall. The mall is open from 10:00 a.m. to 9:00 p.m. on Monday through Saturday and noon to 6:00 p.m. on Sunday. The mall is closed on Thanksgiving, Christmas, and New Year's Day. You stand is set to open on January 1st The weekly mall traffic is 100,000 patrons, including children. The market research analysis concludes the stand location can sell 15 to 25 cups of lemonade per hour. Initially you estimate your stand can sell an average of 20 cups per hour. Each cup holds 12 fluid Ounces and will be filled with 10 ounces of lemonade. All sales will be for cash. The monthly rent for the space at the mall is $4500 plus 3.5% of the total sales. The rent includes all utilities (water, gas, electric), garbage collection, custodial services, mall security, and liability insurance. The rental agreement is month-to- month and must be personally guaranteed. A one month deposit of $4500 is required. All material purchases will be paid for in cash; no trade credit will be used. The cost of lemons is $0.30 per lemon. A cup of lemonade requires three lemons. Maximum lemon storage is one week, refrigerated. The sugar cost is $0.25 per pound for 50 pounds. One cup requires one tablespoon of sugar or 5 grams of sugar per cup. One pound equals 454 grams. Paper cups with plastic lids cost $0.10 each for a quantity of 1000 cups. Straws cost $0.01 each for a quantity of 5000 straws. Napkins cost $0.003 each for a quantity of 10,000 napkins. It is anticipated that each customer will utilize at least two napkins. Bottled water is $1.00 per gallon delivered on site to the lemonade stand. Water must be purchased in a minimum of 50 gallon increments. There will be two attendants at the stand from 9:30 am to 10:00 pm Monday through Saturday. There will be one additional attendant after 3:00 p.m. There will be three attendants on Sunday from 11:30 a.m. to 7:00p.m. One of the attendants will be a supervisor. The supervisor will be present at all hours. You may elect to be the supervisor on site at all hours or part of the hours. You may also elect to have a family member or partner to be a supervisor. As the owner of the business, you intend to take a salary of $60,000 per year (including benefits). The hourly rate for each regular attendant is the minimum wage in Ohio plus 40% for benefits (note: you will need to calculate the hourly wage rate as a single amount: minimum wage plus benefits costs). The supervisor rate is $20.00 per hour (this includes benefits). The cost of the capital equipment for setting up the stand is $125,000 which includes all equipment, counters, signage, refrigerator, ice-machine, and cash register. This equipment can be purchased with $25,000 cash and a bank loan for the remainder. You will need to determine the bank loan terms for the remainder- the annual interest and monthly payments for 5 years. You are to assume that the monthly loan payment will be $XXXX amount, broken down as $X interest each month and $X on the loan principal balance. The equipment is to be depreciated on a straight-line basis (no salvage value) over the 7 years of estimated life. Starting (Opening) Balance Sheet (time zero) Create a Balance Sheet showing starting Assets, Liabilities, and Shareholder's Equity. The assets will include cash, equipment, inventory, and $4500 rental deposit to the mall. The long-term liabilities will include the bank loan. The shareholder's equity will include starting capital (cash) which must be sufficient to purchase starting inventory, pay employees and rent for the first 2 months, pay the $4500 rental deposit, pay $25,000 for the equipment, and make payments on the equipment loan for the first year. The equity is your cash investment in the business and may come from your personal savings, personal investments, from your family and friends, a second mortgage on your home, or your kids' piggy banks. It may also partially come from a partner or partners. The cash investment will be shown as Shareholder's Equity on the Balance Sheet. Some of this cash will be spent on equipment ($25,000), starting inventory, rent deposit ($4500), and the first month's rent ($4500), etc. The remainder will appear as cash on Starting (Opening) Balance Sheet (time zero) Create a Balance Sheet showing starting Assets, Liabilities, and Shareholder's Equity. The assets will include cash, equipment, inventory, and $4500 rental deposit to the mall. The long-term liabilities will include the bank loan. The shareholder's equity will include starting capital (cash) which must be sufficient to purchase starting inventory, pay employees and rent for the first 2 months, pay the $4500 rental deposit, pay $25,000 for the equipment, and make payments on the equipment loan for the first year. The equity is your cash investment in the business and may come from your personal savings, personal investments, from your family and friends, a second mortgage on your home, or your kids' piggy banks. It may also partially come from a partner or partners. The cash investment will be shown as Shareholder's Equity on the Balance Sheet. Some of this cash will be spent on equipment ($25,000), starting inventory, rent deposit ($4500), and the first month's rent ($4500), etc. The remainder will appear as cash on hand on the Balance Sheet to help cover monthly expenses going forward. Projected Income Statement for Years 1-2 Based on your sales estimates, calculate the cost of operating the business each month for one year including materials costs, labor, accessories (straws and napkins), rent payments, interest payments, and depreciation. Note: Estimated sales are cyclical. For example, you will need to estimate a higher sales volume (greater than 20 cups per hour) during the summer season, etc. Calculate the direct materials costs to make one cup of lemonade. Make sure to include the costs for the cup, lid, straw, and napkins. Based on the above calculated cost and the initial sales estimates, establish a projected selling price per cup with a minimum 20% profit. Prepare a Projected Income Statement for months 1 and 2 in the first year of operations. Then prepare a Pro Forma Income Statement as of the close of year 1. Cash Flow Analysis Calculate cash flows at start-up and then for each month. Cash flow is the most important financial data in a business, especially a start-up. Business happiness is positive cash flow. Projected Balance Sheet for each month in Year 1 Calculate a projected Balance Sheet at the close of months 1 and 2 using the starting Balance Sheet, the Projected Income Statement data, and the Cash Flow Analysis. Then prepare a projected Balance Sheet as of the close of year 1. Background/Start-up Data You are starting a fresh, made to order, lemonade stand at the mall. The mall is open from 10:00 a.m. to 9:00 p.m. on Monday through Saturday and noon to 6:00 p.m. on Sunday. The mall is closed on Thanksgiving, Christmas, and New Year's Day. You stand is set to open on January 1st The weekly mall traffic is 100,000 patrons, including children. The market research analysis concludes the stand location can sell 15 to 25 cups of lemonade per hour. Initially you estimate your stand can sell an average of 20 cups per hour. Each cup holds 12 fluid Ounces and will be filled with 10 ounces of lemonade. All sales will be for cash. The monthly rent for the space at the mall is $4500 plus 3.5% of the total sales. The rent includes all utilities (water, gas, electric), garbage collection, custodial services, mall security, and liability insurance. The rental agreement is month-to- month and must be personally guaranteed. A one month deposit of $4500 is required. All material purchases will be paid for in cash; no trade credit will be used. The cost of lemons is $0.30 per lemon. A cup of lemonade requires three lemons. Maximum lemon storage is one week, refrigerated. The sugar cost is $0.25 per pound for 50 pounds. One cup requires one tablespoon of sugar or 5 grams of sugar per cup. One pound equals 454 grams. Paper cups with plastic lids cost $0.10 each for a quantity of 1000 cups. Straws cost $0.01 each for a quantity of 5000 straws. Napkins cost $0.003 each for a quantity of 10,000 napkins. It is anticipated that each customer will utilize at least two napkins. Bottled water is $1.00 per gallon delivered on site to the lemonade stand. Water must be purchased in a minimum of 50 gallon increments. There will be two attendants at the stand from 9:30 am to 10:00 pm Monday through Saturday. There will be one additional attendant after 3:00 p.m. There will be three attendants on Sunday from 11:30 a.m. to 7:00p.m. One of the attendants will be a supervisor. The supervisor will be present at all hours. You may elect to be the supervisor on site at all hours or part of the hours. You may also elect to have a family member or partner to be a supervisor. As the owner of the business, you intend to take a salary of $60,000 per year (including benefits). The hourly rate for each regular attendant is the minimum wage in Ohio plus 40% for benefits (note: you will need to calculate the hourly wage rate as a single amount: minimum wage plus benefits costs). The supervisor rate is $20.00 per hour (this includes benefits). The cost of the capital equipment for setting up the stand is $125,000 which includes all equipment, counters, signage, refrigerator, ice-machine, and cash register. This equipment can be purchased with $25,000 cash and a bank loan for the remainder. You will need to determine the bank loan terms for the remainder- the annual interest and monthly payments for 5 years. You are to assume that the monthly loan payment will be $XXXX amount, broken down as $X interest each month and $X on the loan principal balance. The equipment is to be depreciated on a straight-line basis (no salvage value) over the 7 years of estimated life. Starting (Opening) Balance Sheet (time zero) Create a Balance Sheet showing starting Assets, Liabilities, and Shareholder's Equity. The assets will include cash, equipment, inventory, and $4500 rental deposit to the mall. The long-term liabilities will include the bank loan. The shareholder's equity will include starting capital (cash) which must be sufficient to purchase starting inventory, pay employees and rent for the first 2 months, pay the $4500 rental deposit, pay $25,000 for the equipment, and make payments on the equipment loan for the first year. The equity is your cash investment in the business and may come from your personal savings, personal investments, from your family and friends, a second mortgage on your home, or your kids' piggy banks. It may also partially come from a partner or partners. The cash investment will be shown as Shareholder's Equity on the Balance Sheet. Some of this cash will be spent on equipment ($25,000), starting inventory, rent deposit ($4500), and the first month's rent ($4500), etc. The remainder will appear as cash on Starting (Opening) Balance Sheet (time zero) Create a Balance Sheet showing starting Assets, Liabilities, and Shareholder's Equity. The assets will include cash, equipment, inventory, and $4500 rental deposit to the mall. The long-term liabilities will include the bank loan. The shareholder's equity will include starting capital (cash) which must be sufficient to purchase starting inventory, pay employees and rent for the first 2 months, pay the $4500 rental deposit, pay $25,000 for the equipment, and make payments on the equipment loan for the first year. The equity is your cash investment in the business and may come from your personal savings, personal investments, from your family and friends, a second mortgage on your home, or your kids' piggy banks. It may also partially come from a partner or partners. The cash investment will be shown as Shareholder's Equity on the Balance Sheet. Some of this cash will be spent on equipment ($25,000), starting inventory, rent deposit ($4500), and the first month's rent ($4500), etc. The remainder will appear as cash on hand on the Balance Sheet to help cover monthly expenses going forward. Projected Income Statement for Years 1-2 Based on your sales estimates, calculate the cost of operating the business each month for one year including materials costs, labor, accessories (straws and napkins), rent payments, interest payments, and depreciation. Note: Estimated sales are cyclical. For example, you will need to estimate a higher sales volume (greater than 20 cups per hour) during the summer season, etc. Calculate the direct materials costs to make one cup of lemonade. Make sure to include the costs for the cup, lid, straw, and napkins. Based on the above calculated cost and the initial sales estimates, establish a projected selling price per cup with a minimum 20% profit. Prepare a Projected Income Statement for months 1 and 2 in the first year of operations. Then prepare a Pro Forma Income Statement as of the close of year 1. Cash Flow Analysis Calculate cash flows at start-up and then for each month. Cash flow is the most important financial data in a business, especially a start-up. Business happiness is positive cash flow. Projected Balance Sheet for each month in Year 1 Calculate a projected Balance Sheet at the close of months 1 and 2 using the starting Balance Sheet, the Projected Income Statement data, and the Cash Flow Analysis. Then prepare a projected Balance Sheet as of the close of year 1