Can you solve this on an Excel spreadsheet?

Alex Carter$$was a regional sales manager for a chain of electronics stores

in Manchester. Due to a company$-$wide restructuring in May $2023,$ Alex was

informed that his role would be eliminated. He was offered $45,000$ in redundancy

pay or the opportunity to apply for a different role within the company. Given his

experience and a desire to control his own destiny, Alex decided to take the

redundancy pay and pursue his dream of starting his own tech repair and sales

business.

Alex plans to invest $60,000$ of his savings into the business. After consulting with

various banks, Barclays has offered a $5-$year loan of $25,000$ at a fixed interest rate

of $6\mathrm{.}5\%.$

Alex's former colleague, Sarah Johnson $,$ who worked as the inventory

manager with extensive connections with suppliers, is also facing redundancy. Alex

is considering bringing Sarah on board in his new venture.

Two potential business structures are being considered:

$1.$ Alex will operate as a sole trader, taking the loan from Barclays and hiring

Sarah as the inventory manager.

$2.$ A partnership will be established where Alex invests $60,000$ and holds $60\%$

ownership, while Sarah invests $40,000$ and holds $40\%$ ownership. No bank

loan will be required, and Sarah will receive a salary comparable to that in

option $1.$

Alex is unsure whether to operate as a sole trader or establish a partnership.

Based on market research, the tech repair and sales industry is expected to grow,

with the following projections:

$1.$ Projected sales in the first year, assuming the business operates six days a

week, are estimated as:

i$.300,000$ with a probability of $25\%$

ii$.400,000$ with a probability of $50\%$

iii. $500,000$ with a probability of $25\%$

The gross profit margin is expected to be $60\%$ across the five years.

Sales are expected to fluctuate throughout the year, with the following quarterly

distribution: $20\%$ in Q$1,25\%$ in Q$2,25\%$ in Q$3,$ and $30\%$ in Q$4.$

$2.$ Sales growth is expected to be $10\%$ in year $2,15\%$ in year $3,12\%$ in year $4,$

and $5\%$ in year $5.$ Sales forecasts beyond year $5$ are not available.

$3.$ Approximately $70\%$ of sales will be to individual customers for cash, and the

remaining $30\%$ to businesses on a $30-$day credit basis on the first six months.

The plan after that is to collect $100\%$ of sales cash on the month of sales

$4.$ Thanks to Sarah's negotiating skills, suppliers have agreed to provide goods

on a $30-$day credit period for the first six months and then paid for material on

the month of purchase. Material costs are expected to rise by $4\%$ annually

from year two onwards.

$5.$ Fixed operating costs, including staff salaries $($excluding Alex$$s salary$),$ are

estimated at $180,000$ in the first year, with an annual increase of $4\%$ over the

next four years. Fixed costs are spread evenly throughout the year and are

paid monthly.

$6.$ Premises will be rented for $70,000$ annually, payable quarterly in advance,

with the first quarter's rent due on $1$st May $2023.$ Rent is expected to increase

by $6\%$ annually for the next four years.

$7.$ Equipment and software costing $28,000$ will be purchased upfront in May

and paid immediately. These assets are expected to have no resale value at

the end of year $5$ and depreciated using straight line method.

$8.$ Working capital of $15,000$ will be required at the start of the business, which

will be fully recoverable at the end of year $5.$

Required: Write a report to Alex Carter, covering the following topics

a$)$ i$.$ Create a cash budget for the first year assuming Alex operates as a sole trader

invests $60,000$ saving and secures the $25,000$ loan from Barclays at $6\mathrm{.}5\%$ interest,

with the loan principal to be repaid in full at the end of the five$-$year period.

ii$.$ Explain your results and the reliability of the assumptions.

iii. Suggest five strategies Alex could use to address potential short$-$term cash flow

issues.

b$)$ Calculate the following for the first year of business: i$.$ Budgeted profit for the year

ii$.$ Break$-$even point $($BEP$)$ iii. Margin of safety

c$)$ Assuming the project lasts five years, and ignoring taxation, calculate: i$.$ Payback

Period ii$.$ Net Present Value $($NPV$)$ using a $10\%$ discount rate.