Question

Excel assignment: Here are the instructions Please help with the formulas needed thank you. #28-30 are missing in the excel screenshot. They are

#28 Tax

#29 Net Income

#30 Cash Flow

Product Selling Prices: Oysters on half shell will sell for $8.25 each (dozen) Fried Shrimp will sell for $10.25 (dozen) Calamari will sell for $4.95 an order Catfish Sandwich will sell for $5.95 Salads will sell for $4.50 each Fries sell for $1.25 per order Sodas sell for $1.25 a bottle Cost of Goods Sold: The Catfish sandwich ingredients (fried catfish, cheese, sauce, bun etc.) cost $3.15 per sandwich. Calamari cost per order $1.90. The fresh Shrimp cost $7.19 per order from the supplier. Oysters (sauce, lemon, oysters) cost $5.19 per order. Salad ingredients (lettuce, tomatoes, cheese, etc.) cost $1.23 per salad serving Frozen fries and oil average to $.67 per order Sodas cost about $.7 per 16 oz. bottle The building rent is $2775 per month. Phone will cost about $220 per month. Electricity should cost about $775 a month. Insurance will be $850 a month. Advertising and promotion will be $900 a month. Operating Hours: The diner will be open seven days a week. The diner will serve lunch and dinner and will be open from 11am – 7pm on weekdays (Monday – Friday). It will need two hourly employees and an assistant manager (or manager) during these hours that the diner is open. On Saturdays and Sundays the store will be open 11am – 11pm and will need three hourly employees and an assistant manager (or manger). Your client will be the manager and draw a salary of $48,500 per year (includes benefits). He will also work in the store during the busiest times, and fill in for the assistant manager on days off and sick days. The assistant manager will receive a 3 salary of $37,500 per year (includes benefits). The hourly workers will be paid $7.25 an hour. Monday through Fridays the owner expects an average of 17 customers an hour. Saturdays and Sundays the owner expects an average of 45 customers an hour. Demand Rate: On average, 1/4 of all customer will buy shrimp, 1/4 of the customers will buy oysters, 1/4 will buy Calamari, and 1/2 customers will buy Catfish, 3/4 of them will buy a salad, all of them buy french fries, and every customer will purchase a soda. Start-up costs for the diner includes: Kitchen equipment: $16,250 Cash register and sales equipment: $1,250 Initial inventory: $5,500 Pre-opening marketing: $3,500 Diner fixtures (chairs, tables etc.): $4,500 Oil painting of your client’s momma to hang on the wall: $350 Licenses: $1,025 Security deposit: $6,500 First Insurance Payment: $850 Your client has $10,000 and plans to borrow the rest from the bank with a five-year loan at 5.1% interest. You are to calculate the monthly loan payment using the appropriate financial function. Assume a tax rate of 24% if Income Before Taxes (IBT) is equal to or is greater than $23,500. Assume a tax rate of 15% if IBT is less than $23,500. You are to calculate the monthly tax payment using the appropriate logical function. Assume that sales will grow at an average of 2.55% per month. Assume that each month contains 4.2 weeks.

ponen B Month 1 weten merina Marina marina Marina Marina Marina Marina Marino namin no mentione non D Month 3 F Month 5 L MA Month 9 Month 10 Month 11 Month Month 2 Month 4 Month 6 Month 7 Month 8 00 voor 5 Revenue: 6 Oysters Fried Shrimp 8 Calamari 9 Catfish Sandwich 10 Salads 11 Fries 12 Sodas 13 Monthly Revenue 14 15 Expenses: 16 COGS 17 Rent 18 Phone 19 Electricity 20 Insurance 21 Advertising 22 Hourly Wages 23 Salaries 24 Loan Payment 25 Total Expenses 26 | 27 Income Before Tax

Answer 1

Please find below the answer (Formulas)

Tax = Income before tax * Tax rate (24% or 15% based on the income)

Net income: Revenue - Expenses - Tax

Cash flow = Initial Cash available + Loan - the cost incurred for set up - Expenses

I have shared the calculations for the question not able to attach the file, please let me know if you need any clarifications.

Particulars	M1	M2	M3	M4	M5	M6	M7	M8	M9	M10	M11	M12	Total
Revenue
Oysters	15246	15627	16018	16418	16829	17249	17681	18123	18576	19040	19516	20004	210327
Fried Shrimp	18942	19416	19901	20398	20908	21431	21967	22516	23079	23656	24247	24854	261315
Calamari	9148	9376	9611	9851	10097	10350	10608	10874	11145	11424	11710	12002	126196
Catfish Sandwich	21991	22541	23105	23682	24274	24881	25503	26141	26794	27464	28151	28854	303381
Salads	24948	25572	26211	26866	27538	28226	28932	29655	30397	31157	31936	32734	344171
Fries	9240	9471	9708	9950	10199	10454	10716	10983	11258	11539	11828	12124	127471
Sodas	9240	9471	9708	9950	10199	10454	10716	10983	11258	11539	11828	12124	127471
Bitmap Bitmap Bitmap Monthly Revenue	108755	111474	114261	117117	120045	123046	126122	129275	132507	135820	139215	142696	1500333
Expenses
Start up cost	39725												39725
COGS	54978	56352	57761	59205	60685	62203	63758	65352	66985	68660	70376	72136	758452
Rent	2775	2775	2775	2775	2775	2775	2775	2775	2775	2775	2775	2775	33300
Phone	220	220	220	220	220	220	220	220	220	220	220	220	2640
Electricity	775	775	775	775	775	775	775	775	775	775	775	775	9300
Insurance	850	850	850	850	850	850	850	850	850	850	850	850	10200
Advertising	900	900	900	900	900	900	900	900	900	900	900	900	10800
Hourly Wages	4628	4628	4628	4628	4628	4628	4628	4628	4628	4628	4628	4628	55541
Salaries	7167	7167	7167	7167	7167	7167	7167	7167	7167	7167	7167	7167	86000
Loan Payment	43	43	43	43	43	43	43	43	43	43	43	43	510
Total Expenses	112061	73710	75119	76563	78043	79560	81115	82709	84343	86018	87734	89493	1006468
Income Before Tax	-3306	37764	39142	40554	42002	43486	45007	46566	48164	49802	51481	53202	493865
Tax		9063	9394	9733	10080	10437	10802	11176	11559	11953	12356	12769	119321