Please provide all information in Excel format with 3 different worksheets. Please provide formulas as necessary.
Financial information for the project: 1. You decide to open a small business in Charleston, WV that will cater primarily to busy office workers downtown. You are going to offer a gourmet box lunch to be delivered to any office with a minimum number of 5 orders. This lunch will include a gourmet sandwich, a bag of chips, a cookie, and a bottle of water. Because you are using only the finest ingredients, you will charge $9.00 per lunch. 2. The cost of the food items (cost of goods sold) in each lunch is budgeted at $3.15. These are primarily perishable items, so inventory is negligible. You pay for virtually all of your purchases by check at the time they occur. 3. You are planning on drawing out a monthly salary of $1400. 4. You are planning on hiring an assistant to help you with packing the box lunches and delivering them 20 hours per week at $9.25 per hour. Assume four weeks in the month. Assume you will pay your assistant regardless of the hours they are actually needed. Treat this as a fixed cost. 5. You believe you must heavily advertise to get your business established. The amount you are willing to spend is 5% of your monthly revenue. Even though you are using sales revenue to determine the dollar amount of your advertising, treat this as a fixed cost. Advertising costs are paid in the month incurred. 6. You have to pay rent on a commercial kitchen where you will store your items and prepare your box lunches. The rent is $450 per month. Rent is payable on the 1st day of the month. 7. The cost of the phone line is $103 per month. Payment for the current month will occur in the next month when the bill is received. 8. You received an electric bill on the last day of the month payable on the 20th of the following month. It was for $265. Your electric usage is very stable; therefore you treat it as a fixed cost. 9. Sales are primarily cash. There are a few corporate clients you have extended credit to. You estimate 15% of total sales will be collected in the next month. Because this is the first month of operations, there are no outstanding accounts receivables. 10. In order to deliver the lunches you have leased a delivery truck. The lease is a two-year lease at $350 per month. The variable cost of operating the delivery truck is $0.45 per lunch delivered. The lease payment is paid at the beginning of the month and all variable costs are paid as incurred 11. Based on your business plan you drew up, you decided to invest $6000 of your own money to get your business started. Prior to beginning business operations, you spent some of this $6000 as start-up costs (in prior months) leaving you with a cash balance at the beginning of the first month of operations of $2900. REQUIREMENTS: 1. Income Statement (Accrual Basis) Based on the data above, prepare an accrual basis income statement based on the budgeted sales level of_ gourmet box lunches. This income statement should be similar to one you would have prepared in BA 215. 2. Contribution format income statement Based on the data above, prepare an accrual basis income statement using the same quantity of gourmet box lunches as in requirement 1. HINT: Each item should be identified as variable, fixed, and mixed. A mixed cost will appear twice in the statement-once as a variable cost item and once as a fixed cost item. You must clearly identify each of the items you include in this income statement. You should have three columns: total, per unit, and percentage. 3. Cash Budget with supporting schedules (Use same number of box lunches as part 1) A. Prepare a schedule of expected cash collections for the month. There are no accounts receivables outstanding since this is the first month of operation. B. Prepare a schedule of expected cash disbursements for merchandise purchases (food items) for the month. C. Prepare a cash budget for one month. i. The beginning cash balance is $2900. ii. The company must maintain a cash balance of $2500. An open line of credit has been arranged with the bank Financial information for the project: 1. You decide to open a small business in Charleston, WV that will cater primarily to busy office workers downtown. You are going to offer a gourmet box lunch to be delivered to any office with a minimum number of 5 orders. This lunch will include a gourmet sandwich, a bag of chips, a cookie, and a bottle of water. Because you are using only the finest ingredients, you will charge $9.00 per lunch. 2. The cost of the food items (cost of goods sold) in each lunch is budgeted at $3.15. These are primarily perishable items, so inventory is negligible. You pay for virtually all of your purchases by check at the time they occur. 3. You are planning on drawing out a monthly salary of $1400. 4. You are planning on hiring an assistant to help you with packing the box lunches and delivering them 20 hours per week at $9.25 per hour. Assume four weeks in the month. Assume you will pay your assistant regardless of the hours they are actually needed. Treat this as a fixed cost. 5. You believe you must heavily advertise to get your business established. The amount you are willing to spend is 5% of your monthly revenue. Even though you are using sales revenue to determine the dollar amount of your advertising, treat this as a fixed cost. Advertising costs are paid in the month incurred. 6. You have to pay rent on a commercial kitchen where you will store your items and prepare your box lunches. The rent is $450 per month. Rent is payable on the 1st day of the month. 7. The cost of the phone line is $103 per month. Payment for the current month will occur in the next month when the bill is received. 8. You received an electric bill on the last day of the month payable on the 20th of the following month. It was for $265. Your electric usage is very stable; therefore you treat it as a fixed cost. 9. Sales are primarily cash. There are a few corporate clients you have extended credit to. You estimate 15% of total sales will be collected in the next month. Because this is the first month of operations, there are no outstanding accounts receivables. 10. In order to deliver the lunches you have leased a delivery truck. The lease is a two-year lease at $350 per month. The variable cost of operating the delivery truck is $0.45 per lunch delivered. The lease payment is paid at the beginning of the month and all variable costs are paid as incurred 11. Based on your business plan you drew up, you decided to invest $6000 of your own money to get your business started. Prior to beginning business operations, you spent some of this $6000 as start-up costs (in prior months) leaving you with a cash balance at the beginning of the first month of operations of $2900. REQUIREMENTS: 1. Income Statement (Accrual Basis) Based on the data above, prepare an accrual basis income statement based on the budgeted sales level of_ gourmet box lunches. This income statement should be similar to one you would have prepared in BA 215. 2. Contribution format income statement Based on the data above, prepare an accrual basis income statement using the same quantity of gourmet box lunches as in requirement 1. HINT: Each item should be identified as variable, fixed, and mixed. A mixed cost will appear twice in the statement-once as a variable cost item and once as a fixed cost item. You must clearly identify each of the items you include in this income statement. You should have three columns: total, per unit, and percentage. 3. Cash Budget with supporting schedules (Use same number of box lunches as part 1) A. Prepare a schedule of expected cash collections for the month. There are no accounts receivables outstanding since this is the first month of operation. B. Prepare a schedule of expected cash disbursements for merchandise purchases (food items) for the month. C. Prepare a cash budget for one month. i. The beginning cash balance is $2900. ii. The company must maintain a cash balance of $2500. An open line of credit has been arranged with the bank
