Question

Create a budget spreadsheet for a hypothetical organization in excel

Answer 1

Answer)

The budget for XYZ ltd for month of July/2018 is given below.

					BUSINESS BUDGET
				Month/Year:	Jul-18
SUMMARY	ACTUAL	BUDGETED	OVER BUDGET	UNDER BUDGET
Total income	1,432,500.00	1,318,080.00	114,420.00
Total expenses	339,760.00	314,910.00	24,850.00
Income less expenses:	1,092,740.00	1,003,170.00	89,570.00
INCOME DETAILS	ACTUAL	BUDGETED	OVER BUDGET	UNDER BUDGET	NOTES
Sales	1,400,000.00	1,200,000.00	200,000.00		Increase advertising next year.
Interest earned	5,000.00	4,500.00	500.00
Fees	1,000.00	980.00	20.00
Commissions	10,000.00	98,000.00		-88,000.00
Rent	9,000.00	8,000.00	1,000.00
Royalties	2,500.00	2,600.00		-100.00
Other	5,000.00	4,000.00	1,000.00
Total income:	1,432,500.00	1,318,080.00	114,420.00
EXPENSE DETAILS	ACTUAL	BUDGETED	OVER BUDGET	UNDER BUDGET	NOTES
SELLING
Salaries and wages	246,000.00	248,000.00		-2,000.00
Commissions	10,000.00	12,000.00		-2,000.00
Advertising	6,000.00	8,000.00		-2,000.00	Increase Here 3%.
Delivery	0.00	0.00
Shipping	0.00	0.00
Travel	4,600.00	5,600.00		-1,000.00
Other	1,000.00	1,200.00		-200.00
Total sales expenses:	267,600.00	274,800.00		-7,200.00
Percent of total:	78.76%	87.26%
ADMINISTRATIVE
Salaries and wages	12,000.00	10,000.00	2,000.00
Employee benefits	5,000.00	6,000.00		-1,000.00
Payroll taxes	500.00	500.00
Insurance	14,000.00	14,000.00
Loans	6,000.00	5,000.00	1,000.00
Office supplies	4,000.00	4,100.00		-100.00
Travel & entertainment	200.00	190.00	10.00
Postage	300.00	320.00		-20.00
Furnishings	0.00	0.00
Contributions	0.00	0.00
Dues	0.00	0.00
Other	0.00	0.00
Total admin. expenses:	42,000.00	40,110.00	1,890.00
Percent of total:	12.36%	12.74%
SERVICE & EQUIPMENT
Accounting	1,200.00	1,500.00		-300.00
Legal	5,000.00	6,000.00		-1,000.00
Utilities	15,000.00	15,789.00		-789.00	Too high.
Telephone	5,000.00	4,800.00	200.00
Equipment purchases	3,400.00	3,000.00	400.00
Rent & maintenance	560.00	600.00		-40.00
Other	0.00	0.00
Total S&E expenses:	30,160.00	31,689.00		-1,529.00
Percent of total:	0.09	0.10