Question

Lu Lu Bell McAllister is a Christmas tree grower. She currently grows 75,000 Douglas Fir, 56,000 Nobel Fir and 56,000 Grand Fir trees ranging from first to seventh year of growth. She has decided she needs to do this year’s budget, but needs your help. Develop an enterprise budget for the Christmas Tree Connection for 2019. Make sure to formulate cells with monetary value ($) with the Accounting Number Format in the Home tab. Remember when solving equations, please do not do the math by hand. Write or create the appropriate equations in the formula bar as I will be grading your assignment based on the use of formulas.

Lu Lu Bell predicts that she will be able to sell 15,000 Douglas Fir trees at $5.50 each, 8,000 Nobel Fir trees at $9.50 each and 8,000 Grand Fir trees at $8.00 each, the same as 2018. Calculate Lu Lu Bell’s predicted income for the upcoming year. Totals for each tree type sold and total income of all trees sold.

Lu Lu Bell is using the past year’s variable costs plus a 7.5% “buffer” to plan for the upcoming year. Use the following costs from 2016 to plan the variable costs for 2017. Remember she has to take care of every tree and not just the trees she is selling. Fertilizer costs $0.04 per tree. Herbicides cost $0.06 per tree. Fungicides cost $0.05 per tree. A good tree shearer can average one tree every two minutes. Calculate that 3/5 of the total trees grown will need to be sheared. She pays the shearers $10.00 per hour. The trees she sells can be harvested and baled in about 3 minutes. She paid the harvesters $9.85 per hour. She predicts to sell the same number of trees in 2019 as she did in 2018.

Her fixed costs included:

· Fuel for machinery $435

· Replacement parts and purchases $13,000

· 1 full time employee annual salary of $25,000

· She paid herself an annual salary of $40,000

· $8,000 in overhead

· Annual payment on her operating loan

The loan was in the amount of $10,000 with a fixed annual interest rate of 5.75% over 10 years. She makes one payment per year and this is the final year of her loan. Calculate the payment amount (principle and interest) for the operating loan for this year. Do not forget to add 7.5% to the final estimated total costs.

Complete the budget statement, using equations to fill in the yellow cells in the Excel Spreadsheet.

After the budget statement is complete create a table illustrating the expenses, both fixed and variable. You do not need to include the 7.5% “buffer” amount in your expense table. Once you have created a table, create a 2-D clustered column chart or bar graph to illustrate how the expenses are proportioned. Make sure to include a chart title and label the axis titles appropriately.

Answer 1

Lu Lu Bell McAllister
Particulars		Douglas Fir	Nobel Fir	Grand Fir	Total
No of Trees (Grows)	A	$           75,000.00	$             56,000.00	$             56,000.00	$    1,87,000.00
No of Trees (Sell)	B	$           15,000.00	$               8,000.00	$               8,000.00	$        31,000.00
Sale Price	C	$                      5.50	$                       9.50	$                       8.00
Total Sales Prediction	D = BxC	$           82,500.00	$             76,000.00	$             64,000.00	$    2,22,500.00
Fertilizer costs per tree	E	$              3,000.00	$               2,240.00	$               2,240.00	$          7,480.00
		(75000*.04)	(56000*.04)	(56000*.04)
Herbicides cost per tree	F	$              4,500.00	$               3,360.00	$               3,360.00	$        11,220.00
		(75000*.06)	(56000*.06)	(56000*.06)
Fungicide cost per tree	G	$                 750.00	$                  400.00	$                  400.00	$          1,550.00
		(75000*.05)	(56000*.05)	(56000*.05)
Total Varaible Cost	H =E+F+G	$              8,250.00	$               6,000.00	$               6,000.00	$        20,250.00
Buffer Cost	I = Hx7.5%	$                 618.75	$                  450.00	$                  450.00	$          1,518.75
No of Trees that can been Sheer	J =A x 3/5	$           45,000.00	$             33,600.00	$             33,600.00	$    1,12,200.00
Time Requied for Sheering (Hrs)	K	1500	1120	1120	3740
		(45000*2/60min)	(33600*2/60min)	(33600*2/60min)
Cost of Sheering	L = K*10	$           15,000.00	$             11,200.00	$             11,200.00	$        37,400.00
Harvesting Time (Hrs)	M	750	400	400	1550
		(15000*3/60min)	(8000*3/60min)	(8000*3/60min)
Harvesting Cost	N = M x 9.85	$              7,387.50	$               3,940.00	$               3,940.00	$        15,267.50
Fixed Cost	O				$             435.00
Purchase and reaplacement	P				$        13,000.00
Employee Salary	Q				$        25,000.00
Self Salary	R				$        40,000.00
Overhead Cost	S				$          8,000.00
Loan Repayment	T = 10000/10				$          1,000.00
Loan Repayment Interest	U = 1000*5.75% p.a				$                57.50
Total Cost	V = (O+P+Q+R+S+T+U+V)				$        87,492.50
Toal Variable Cost	W = H+L+N	$           30,637.50	$             21,140.00	$             21,140.00	$    1,61,550.00
Total Fixed Cost	X = V				$        87,492.50

$2,50,000.00 $2,00,000.00 Total Sales Prediction D = BXC $1,50,000.00 Toal Variable Cost W = H+L+N $1,00,000.00 Total Fixed Cost X = V $50,000.00 S. 1 2 3 4