Detailed Question: Determine Break-even / Profit analysis
You are starting your own retail business. The ABC Bank has given you a $500,000 loan for your building purchase and other miscellaneous startup costs. The loan is on a 15-year repayment period plan with an APR of 4.125%. As a new business owner you have the following monthly fixed expenses related to your business:
Loan Repayment: $2,892.36
Utilities: $1,500
Office Supplies: $950
Payroll: $25,700
The sales of your company have an initial increase of $125,000 in the first month and increase monthly at a rate of 1.35% in the following months. You will create a spreadsheet to determine your company's break-even point and first 3 years profit forecast.
You have to come up with 5 variable (flexible) monthly expenses which will increase by 0.05% each month. Each variable expense has to realistic as to what a real business owner will incur. For example, the wholesale cost to purchase your inventory. This may require some research, and any resources used must be cited.
You will also create a Word document explaining your company's background ( I attached my company back ground in word document) and what type of merchandise you are selling. Your document must have your company name at the top of the document along with your name, student ID, and the assignment number. Your explanation should only be a paragraph in length and you should also place all your references under your explanation under a References header.
For each month your spreadsheet will compute costs and profits of your new business. The analysis should include 3 years of figures (Sales, Expenses, Profit/Loss).
Do not hardcode data values in the body of the spreadsheet; use formulas with cell references only.
Create a spreadsheet that will allow your manager to play a "what if" scenario by changing the values. The manager should be able to easily determine the total profit/loss for the entire time and at the end of any month.
You will determine the layout and formatting of the spreadsheet, along with the correct formulas to solve the problem. Be sure to apply the heuristics of good spreadsheet design. Be sure the user can quickly and easily identify the input and output area. Be sure that the spreadsheet is formatted well.
Use conditional formatting along with the IF function to display a message in the output error indicating "GO" (in green) when income exceeds cost and "STOP" in red when costs exceed income.
Your job is to determine a meaningful presentation of this problem using an Excel workbook. There are many different layouts and designs that can solve this problem well.
Attachment:- company-background.rar