Follow the instructions-2 to complete the assignment on excel sheet.
MBA 708 Week 7 Case Study Operational and Capital Forecasting & Decision Analysis Landry Elsher owns and operates a tattoo parlor under the name INK Inc. There are currently seven artists providing services, including Landry. The other six artists are paid as independent contractors* and provide both tattoo and body piercing services. In addition to providing services, the business sells body jewelry, tattoo & piercing care products, and tanning bed time. A full-time receptionist is employed to arrange appointments and perform other clerical duties such as counter sales and record keeping. Landry has eight private rooms for providing services, each equipped with a sink, cabinet, and autoclave. Each artist provides their own instruments. Each artist, excluding Landry, “rents’ a room at a cost of $400 per month. Charges for all client services are billed and collected by the receptionist who accumulates total revenues for each artist. The outside artists are paid a commission of 75% of their gross revenues for both tattoo and piercing services. Commissions for services are paid to the artist on a weekly basis with the cost of room rent & cost of supplies deducted from the commission revenue. Any supplies used by the six outside artists must be purchased through the business. Landry does not have to purchase supplies and simply takes what is necessary from the supply inventory. Landry draws a monthly salary from the business as compensation. The tanning bed is owned by the parlor and is housed in the currently vacant eighth room. All net billings received from the bed goes to the parlor. Variable parlor costs are incurred based on the number of outside artists employed at any time (currently six). In addition to the information provided in the above narrative, Landry has assembled the following information on monthly activity and costs to operating INK Inc.: Monthly revenue sources for the business: • Average monthly tattoo billings per artist $4,000 • Average monthly body piercing billings per artist 1,000 • Commission paid to artists for both tattoo & piercing services 75% • Average monthly sales revenue for supplies sold to outside artists per artist 300 • Average gross profit % on supply sales 15% • Average monthly product sales revenue generated per artist 100 • Average gross profit % on product sales revenue 55% • Average gross tanning bed billings 3,000 • Monthly room rental charged per outside artists 400 Monthly operating costs incurred by the business: • Cost of receptionist 1,500 • Landry salary 3,000 • Building rent 1,500 • Depreciation on furniture & fixtures 70 • Cost of supplies used by Landry each month 100 • Advertising 150 • Maintenance & utilities 1,400 • Other 100 • 28% average tax rate * it is not necessary to consider payroll taxes and other employee fringe benefits in the context of this case study A. Calculate the current average net monthly income of the parlor. Provide calculation support in good form on tab A - Current Net Income. B. Landry is considering eliminating the tanning bed services and renting the room to a new artist, bringing the number of an outside artists employed to seven. The sale of the tanning bed would bring in $1,000 for the sale and would reduce depreciation expense by $40 per month. Assume that fixed and variable revenues & costs per artist would remain constant per artist, with product sales averages for revenues and costs remain constant with the addition of a new artist, would Landry be better off under financially under this new arrangement? Provide calculation support in good form at tab B – Eliminate Tanning Bed C. Landry is considering a move to a new location. The details of the opportunity: o The purchase of a new building on the same block. The cost of the buildings would be $210,000 with a $10,000 down payment required and 15-year annual mortgage payments of $19,268 per payment. For simplicity use the annualized payment of $19,268 for your calculation**. Use the PV of an ordinary annuity table for this calculation. o Extra space in the building could be rented out to another business for $3,000 per year. o Purchase of the building would eliminate the need of renting the current space. For simplicity, use the annualized amount of $1,500 x 12 = $18,000 per year paid at the end of the year for your calculations**. Assume that Landry’s current lease would extend without cost increase for the next 15 years. Use the PV of an ordinary annuity table for this calculation. From a cost point of view, which option (continue renting or buy) would provide the lowest cost based on the time value of money assuming a period of 15 years and a cost of capital (interest rate) of 5% APR. Provide calculation support in good form at tab C- Rent or Buy. **although the mortgage and rents would require monthly payments, we are simplifying this problem. The difference in time value of money between12 monthly versus one annual payment for both the mortgage and rent is considered immaterial in the context in the decision. D. While the considerations and calculations in A-C above provide quantitative support for the decisions requested, the numbers won’t stand on their own. A manager or business owner should consider qualitative factors as part of their analysis of a decision. The qualitative factors are those factors that are relevant to a decision but are difficult to measure in terms of money. Qualitative factors may include: (1) effect on employee morale, schedules and other internal elements; (2) relationships with and commitments to suppliers; (3) effect on present and future customers; and (4) long-term future effect on profitability. o Within the discussion area accompanying this case study, provide a post that lists and explains those qualitative factors that should be considered for items B and C above. While you are only required to create an original post to fulfill the requirements of the case study, I would encourage you to read and respond to the posts of your classmates to enhance this learning outcome.