Based off of my calculations can you create an income statement, balance sheet and cash flow statement?

Project 1: Building a Financial Statement Instructions: Read through the scenario and complete items 1-15 below. Build your financial statement in Excel, using formulas to show your work. Your instructor should be able to change a few values and see the new resulting totals and margins. Answer the remaining questions in this document and submit. Note: 5 points are for presentation clarity/organization. Be sure your financial statement is organized and easy to view and manipulate. Juan O'Sullivan graduated in May 2015 from UIC and looked everywhere for a job. He searched and searched for a decent job, and after a while he decided the only way to get ahead was to start his own business. In order to support himself, Juan decided to pursue a life-long dream of owning a hot dog cart in downtown Chicago. Without any substantial savings of his own, Juan borrowed $3,600 for a hot dog cart from the local bank at an interest rate of 2.0% per year, with the loan duration of three years. Computed using simple interest formulas, his annual interest payment is $72. To fund start-up costs, Juan sold his car to buy hot dogs, buns, wrappers, and all the condiments (e.g., mustard, relish, onions, etc.) to make a great hot dog. For accounting purposes, Juan considers the \"supply\" cost per hot dog sold is $0.65. Juan always buys supplies in quantities of 1,000 to ensure an adequate inventory supply. Juan hired his best friend from school, Stassi Chen, to help work the hot dog cart. Stassi works 40 hours per week (2080 hours per year) at the rate of $8.00 per hour. In order to be a good employer, Juan provides benefits to Stassi. The cost of these benefits is 29% of Stassi total salary. Note: Juan's own compensation is based on the bottom line (i.e., profit or loss) of the business. The price of each hot dog is $3.00. Condiments are free. In addition to selling to the public, Juan has a contract with the Chicago police department --- this contract provides the local police with a $1.75 discount on the purchase of each hot dog. Further, Juan also provides free hot dogs to needy families that can't afford to pay. Sometimes, when a good customer forgets their wallet, Juan will give them a hot dog based on their promise to pay the next day (Juan truly is a good guy). As part of owning a business, Juan also has the need to purchase insurance to protect his business against the threat of fire, flood, or theft. Insurance is $160 per year. According to generally accepted accounting principles (GAAP), Juan should depreciate the hot dog cart in equal amounts over five years. Assume Juan uses Cash-based Accounting and, due to his charitable nature, was able to be considered as a tax exempt business. Project 1: Building a Financial Statement 1 If Juan (and Stassi) sold a total of 18,500 hot dogs, with 6,750 sold to policemen, what were the total charges (total billed charges) for the year? Answer Total Charges $43,687.50 ($35,250 + $8,437.5) 2 Assuming Juan provided an average of 45 free hot dogs per month to needy families, and adding the discount given to members of the police force, what is the total contractual and charity allowances for the year? Answer the total contractual and charity allowances for the year is $12,163.5 ($11,812.5 + $351) 3 Calculate the Net Revenue. [Net Revenue = Charges minus ???] Answer Net Revenue (Loss) $2870.6 4 What is the annual principal payment? The annual interest payment? Answer Annual Principal Payment: $1,200, Annual Interest Payment: $24 5 Construct a Statement of Revenues and Expenses for Juan's hot dog business. Use the information from the scenario and from the questions above. Answer 6 During the course of the year, customers \"forgot\" their wallets 375 times. Of these occasions, Juan expects to never receive payment for 350 of the hot dogs. The other 25 he is still holding out hope and believes payment will be received. Include these items on your financial statement. Answer 7 What was the Operating Margin? Answer Operating Margin Ratio: 0.233 8 What is the amount of total fixed assets (equipment) at the end of the year? Answer --- $2,880 9 Assuming no more equipment purchases, what will be the value of total fixed assets at the end of the second year? Answer --- $2,160 1 0 What is the value of inventory at the end of the year? Answer --- $325 1 1 What is the value of Accounts Receivable at the end of the year? Answer --- $1,125 Project 1: Building a Financial Statement 1 2 Assuming that the Balance Sheet shows Total Assets of $5,180, and Total Liabilities of $3,400, what is the value of Net Assets? Answer --- Net Assets $1,780 1 3 Why is an increase in Current Assets considered an use of Cash? Answer --- An increase in Current Assets hurts cash flow and considered as use of cash. For example: The accounts receivable asset shows how much money customers who bought products on credit still owe the business; this asset is a promise of cash that the business will receive. Cash doesn't increase until the business collects money from its customers. 1 4 Why is an increase in Current Liabilities considered a source of Cash? Answer ------ An increase in Current Liabilities is a source of cash. For example: The accounts payable liability shows how much money we have to pay for products we brought on credit still owe to creditors; this liability is a promise of cash that the business will pay. It's a source till the business pays to its creditors. 1 5 Calculate the \"Cash Flow\" using EBITDA as a proxy. Why is Cash considered \"King?\" Answer --\"Cash is king\" is an age-old saying often used to explain the failure of both businesses and consumer households. Without the proper amount of cash on hand, both of these entities can run into major trouble, and even be forced into bankruptcy. Businesses must have cash on hand for various reasons, such as investing in new infrastructure and dealing with unexpected expenses. Moreover, a business's cash flow is often cited as a key factor in its potential for long-term success. A company may have all the revenue in the world, but without the ability to generate cash, it can easily fail