So this is the question: I just need all the calculations in an Excel spreadsheet,

Alex Carter $($he$/$him$)$ 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 $($she$/$her$),$ 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:

$1.$ 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.

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

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

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

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