Excel Budgeting for the Year 2018: Monthly Sales - Production

Questions:

1.Using Excel, prepare the following budgets for the twelve-month period from January 2018 to December 2018. The first worksheet should contain your raw data and assumptions and all future worksheets should be linked to this data. Use a different worksheet for each budget. Show all calculations and use Excel functions where possible. While you should use examples in your text as a guide, the key to using spreadsheets is that they are structured so that others can use them and follow the flow of information without difficulty.

a.Monthly Sales Revenue and Cash Collection Budget

b.Production Budget in Units

c.Direct Materials and Cash Purchases Budget

d.Direct Labour Budget

e.Manufacturing Overhead Budget (break into variable and fixed components)

f.Monthly Operating Cost Budget

g.Ending Inventory budget for Finished Goods

h.Cost of Sales Budget

i.Budgeted Income Statement for the year ended 31 December 2018

j.Monthly Cash Budget

2.Write a report for your manager to present to the upcoming Board meeting.

an overview of the expected results for the 2018 year;
an analysis of the Contribution Margin for each product and recommendations regarding the products Sun Worship Leisure Wear manufacture and sell;
recommendations regarding the cash position of the business; and
an analysis of current market conditions and future predictions that you believe should be considered when the 2019 budget is prepared. You should use broader industry data and trends from sources such as IBISWorld and the Australian Bureau of Statistics to support your recommendations.
Use visual aids such as tables and graphs to enhance your report presentation.

Answers:

Answer to Question 1

Statement Showing Monthly Sales Revenue
Months	Bikini	Board short	Towel	Beach Bag	Total Sales
Jan-18	$150,000	$90,000	$18,000	$13,000	$271,000
Feb-18	$120,000	$82,500	$13,500	$11,700	$227,700
Mar-18	$130,000	$87,000	$11,700	$10,400	$239,100
Apr-18	$100,000	$75,000	$6,300	$7,800	$189,100
May-18	$40,000	$45,000	$3,600	$3,900	$92,500
Jun-18	$50,000	$52,500	$1,800	$2,600	$106,900
Jul-18	$40,000	$37,500	$1,800	$2,600	$81,900
Aug-18	$20,000	$30,000	$1,800	$2,600	$54,400
Sep-18	$130,000	$87,000	$11,700	$10,400	$239,100
Oct-18	$120,000	$82,500	$13,500	$11,700	$227,700
Nov-18	$120,000	$82,500	$13,500	$11,700	$227,700
Dec-18	$150,000	$90,000	$18,000	$13,000	$271,000

Statement showing cash collection budget
Months	Cash Sales	Cash collection from debtors		Total cash collection
Months	Cash Sales	60% collected after 1 month	40% collected after 2 month	Total cash collection
Jan-18	$54,200	$130,080	$123,650	$307,930
Feb-18	$45,540	$109,296	$86,720	$241,556
Mar-18	$47,820	$114,768	$72,864	$235,452
Apr-18	$37,820	$90,768	$76,512	$205,100
May-18	$18,500	$44,400	$60,512	$123,412
Jun-18	$21,380	$51,312	$29,600	$102,292
Jul-18	$16,380	$39,312	$34,208	$89,900
Aug-18	$10,880	$26,112	$26,208	$63,200
Sep-18	$47,820	$114,768	$17,408	$179,996
Oct-18	$45,540	$109,296	$76,512	$231,348
Nov-18	$45,540	$109,296	$72,864	$227,700
Dec-18	$54,200	$130,080	$72,864	$257,144

Statement showing Production budget for Bikini (units)
Months	Opening (A)	Sales (B)	Closing (C )	Total Production (B+C-A)
Jan-18	750	1,500	600	1,570
Feb-18	600	1,200	650	1,250
Mar-18	650	1,300	500	1,150
Apr-18	500	1,000	200	700
May-18	200	400	250	450
Jun-18	250	500	200	450
Jul-18	200	400	100	300
Aug-18	100	200	650	750
Sep-18	650	1,300	600	1,250
Oct-18	600	1,200	600	1,200
Nov-18	600	1,200	750	1,350
Dec-18	750	1,500	800	1,550

Statement showing Production budget for Board Short (units)
Months	Opening (A)	Sales (B)	Closing (C )	Total Production
Jan-18	600	1,200	550	1,150
Feb-18	550	1,100	580	1,130
Mar-18	580	1,160	500	1,080
Apr-18	500	1,000	300	800
May-18	300	600	350	650
Jun-18	350	700	250	600
Jul-18	250	500	200	450
Aug-18	200	400	580	780
Sep-18	580	1,160	550	1,130
Oct-18	550	1,100	550	1,100
Nov-18	550	1,100	600	1,150
Dec-18	600	1,200	700	1,300

Statement showing Production budget for Towel (units)
Years	Opening (A)	Sales (B)	Closing (C )	Total Production
Jan-18	200	400	150	350
Feb-18	150	300	130	280
Mar-18	130	260	70	200
Apr-18	70	140	40	110
May-18	40	80	20	60
Jun-18	20	40	20	40
Jul-18	20	40	20	40
Aug-18	20	40	130	150
Sep-18	130	260	150	280
Oct-18	150	300	150	300
Nov-18	150	300	200	350
Dec-18	200	400	220	420

Statement showing Production budget for Beach Bag (units)
Years	Opening (A)	Sales (B)	Closing (C )	Total Production
Jan-18	100	200	90	190
Feb-18	90	180	80	170
Mar-18	80	160	60	140
Apr-18	60	120	30	90
May-18	30	60	20	50
Jun-18	20	40	20	40
Jul-18	20	40	20	40
Aug-18	20	40	80	100
Sep-18	80	160	90	170
Oct-18	90	180	90	180
Nov-18	90	180	100	190
Dec-18	100	200	110	210

Statement showing Direct Material and cash Purchase Budget
For Bikini
Months	Opening	Production	Closing	Total Purchase (units)	Direct material	Accounts Paid in the same month	Accounts Paid in the following month
Jan-18	1178	1,570	938	1330	$ 42,892.50	$12,868	$43,875
Feb-18	938	1,250	863	1175	$ 37,893.75	$11,368	$30,025
Mar-18	863	1,150	525	813	$ 26,203.13	$7,861	$26,526
Apr-18	525	700	338	513	$ 16,528.13	$4,958	$18,342
May-18	338	450	338	450	$ 14,512.50	$4,354	$11,570
Jun-18	338	450	225	338	$ 10,884.38	$3,265	$10,159
Jul-18	225	300	563	638	$ 20,559.38	$6,168	$7,619
Aug-18	563	750	938	1125	$ 36,281.25	$10,884	$14,392
Sep-18	938	1,250	900	1213	$ 39,103.13	$11,731	$25,397
Oct-18	900	1,200	1013	1313	$ 42,328.13	$12,698	$27,372
Nov-18	1013	1,350	1163	1500	$ 48,375.00	$14,513	$29,630
Dec-18	1163	1,550	1125	1513	$ 48,778.13	$14,633	$33,863

Statement showing Direct Material and cash Purchase Budget
For Broad short
Months	Opening	Production	Closing	Total Purchase (units)	Direct material	Accounts Paid in the same month	Accounts Paid in the following month
Jan-18	900	1,200	825	1125	$ 36,281.25	$10,884	$33,535
Feb-18	825	1,100	870	1145	$ 36,926.25	$11,078	$25,397
Mar-18	870	1,160	750	1040	$ 33,540.00	$10,062	$25,848
Apr-18	750	1,000	450	700	$ 22,575.00	$6,773	$23,478
May-18	450	600	525	675	$ 21,768.75	$6,531	$15,803
Jun-18	525	700	375	550	$ 17,737.50	$5,321	$15,238
Jul-18	375	500	300	425	$ 13,706.25	$4,112	$12,416
Aug-18	300	400	870	970	$ 31,282.50	$9,385	$9,594
Sep-18	870	1,160	825	1115	$ 35,958.75	$10,788	$21,898
Oct-18	825	1,100	825	1100	$ 35,475.00	$10,643	$25,171
Nov-18	825	1,100	900	1175	$ 37,893.75	$11,368	$24,833
Dec-18	900	1,200	975	1275	$ 41,118.75	$12,336	$26,526

Statement showing Direct Material and cash Purchase Budget
For Towel
Months	Opening	Production	Closing	Total Purchase (units)	Direct material	Accounts Paid in the same month		Accounts Paid in the following month
Jan-18	263	350	210	298	$ 9,594.38	$2,878	$9,781
Feb-18	210	280	150	220	$ 7,095.00	$2,129	$6,716
Mar-18	150	200	83	133	$ 4,273.13	$1,282	$4,967
Apr-18	83	110	45	73	$ 2,338.13	$701	$2,991
May-18	45	60	30	45	$ 1,451.25	$435	$1,637
Jun-18	30	40	30	40	$ 1,290.00	$387	$1,016
Jul-18	30	40	113	123	$ 3,950.63	$1,185	$903
Aug-18	113	150	210	248	$ 7,981.88	$2,395	$2,765
Sep-18	210	280	225	295	$ 9,513.75	$2,854	$5,587
Oct-18	225	300	263	338	$ 10,884.38	$3,265	$6,660
Nov-18	263	350	315	403	$ 12,980.63	$3,894	$7,619
Dec-18	315	420	315	420	$ 13,545.00	$4,064	$9,086

Statement showing Direct Material and cash Purchase Budget
For Beach bag
Months	Opening	Production	Closing	Total Purchase (units)	Direct material	Accounts Paid in the same month	Accounts Paid in the following month
Jan-18	143	190	128	175	$ 5,643.75	$1,693	$5,310
Feb-18	128	170	105	148	$ 4,756.88	$1,427	$3,951
Mar-18	105	140	68	103	$ 3,305.63	$992	$3,330
Apr-18	68	90	38	60	$ 1,935.00	$581	$2,314
May-18	38	50	30	43	$ 1,370.63	$411	$1,355
Jun-18	30	40	30	40	$ 1,290.00	$387	$959
Jul-18	30	40	75	85	$ 2,741.25	$822	$903
Aug-18	75	100	128	153	$ 4,918.13	$1,475	$1,919
Sep-18	128	170	135	178	$ 5,724.38	$1,717	$3,443
Oct-18	135	180	143	188	$ 6,046.88	$1,814	$4,007
Nov-18	143	190	158	205	$ 6,611.25	$1,983	$4,233
Dec-18	158	210	158	210	$ 6,772.50	$2,032	$4,628

Statement showing Direct Labor Budget
Month	Bikini		Board Short		Towel		Beach bag
Month	Labor Hour	labor Amount	Labor Hour	labor Amount	Labor Hour	labor Amount	Labor Hour	labor Amount
Jan-18	1570	$ 43,960.00	863	$ 24,150.00	140	$ 3,920.00	114	$ 3,192.00
Feb-18	1250	$ 35,000.00	848	$ 23,730.00	112	$ 3,136.00	102	$ 2,856.00
Mar-18	1150	$ 32,200.00	810	$ 22,680.00	80	$ 2,240.00	84	$ 2,352.00
Apr-18	700	$ 19,600.00	600	$ 16,800.00	44	$ 1,232.00	54	$ 1,512.00
May-18	450	$ 12,600.00	488	$ 13,650.00	24	$ 672.00	30	$ 840.00
Jun-18	450	$ 12,600.00	450	$ 12,600.00	16	$ 448.00	24	$ 672.00
Jul-18	300	$ 8,400.00	338	$ 9,450.00	16	$ 448.00	24	$ 672.00
Aug-18	750	$ 21,000.00	585	$ 16,380.00	60	$ 1,680.00	60	$ 1,680.00
Sep-18	1250	$ 35,000.00	848	$ 23,730.00	112	$ 3,136.00	102	$ 2,856.00
Oct-18	1200	$ 33,600.00	825	$ 23,100.00	120	$ 3,360.00	108	$ 3,024.00
Nov-18	1350	$ 37,800.00	863	$ 24,150.00	140	$ 3,920.00	114	$ 3,192.00
Dec-18	1550	$ 43,400.00	975	$ 27,300.00	168	$ 4,704.00	126	$ 3,528.00

Statement showing Variable Manufacturing Overhead
Months	Bikini		Board Short		Towel		Beach bag		Total
Months	Indirect Material	Indirect Labor	Indirect Material	Indirect Labor	Indirect Material	Indirect Labor	Indirect Material	Indirect Labor	Indirect Material	Indirect Labor
Jan-18	$ 3,140.00	$ 1,570.00	$ 2,300.00	$ 1,150.00	$ 700.00	$ 350.00	$ 380.00	$ 190.00	$ 6,520.00	$ 3,260.00
Feb-18	$ 2,500.00	$ 1,250.00	$ 2,260.00	$ 1,130.00	$ 560.00	$ 280.00	$ 340.00	$ 170.00	$ 5,660.00	$ 2,830.00
Mar-18	$ 2,300.00	$ 1,150.00	$ 2,160.00	$ 1,080.00	$ 400.00	$ 200.00	$ 280.00	$ 140.00	$ 5,140.00	$ 2,570.00
Apr-18	$ 1,400.00	$ 700.00	$ 1,600.00	$ 800.00	$ 220.00	$ 110.00	$ 180.00	$ 90.00	$ 3,400.00	$ 1,700.00
May-18	$ 900.00	$ 450.00	$ 1,300.00	$ 650.00	$ 120.00	$ 60.00	$ 100.00	$ 50.00	$ 2,420.00	$ 1,210.00
Jun-18	$ 900.00	$ 450.00	$ 1,200.00	$ 600.00	$ 80.00	$ 40.00	$ 80.00	$ 40.00	$ 2,260.00	$ 1,130.00
Jul-18	$ 600.00	$ 300.00	$ 900.00	$ 450.00	$ 80.00	$ 40.00	$ 80.00	$ 40.00	$ 1,660.00	$ 830.00
Aug-18	$ 1,500.00	$ 750.00	$ 1,560.00	$ 780.00	$ 300.00	$ 150.00	$ 200.00	$ 100.00	$ 3,560.00	$ 1,780.00
Sep-18	$ 2,500.00	$ 1,250.00	$ 2,260.00	$ 1,130.00	$ 560.00	$ 280.00	$ 340.00	$ 170.00	$ 5,660.00	$ 2,830.00
Oct-18	$ 2,400.00	$ 1,200.00	$ 2,200.00	$ 1,100.00	$ 600.00	$ 300.00	$ 360.00	$ 180.00	$ 5,560.00	$ 2,780.00
Nov-18	$ 2,700.00	$ 1,350.00	$ 2,300.00	$ 1,150.00	$ 700.00	$ 350.00	$ 380.00	$ 190.00	$ 6,080.00	$ 3,040.00
Dec-18	$ 3,100.00	$ 1,550.00	$ 2,600.00	$ 1,300.00	$ 840.00	$ 420.00	$ 420.00	$ 210.00	$ 6,960.00	$ 3,480.00
Total	$ 23,940.00	$ 11,970.00	$ 22,640.00	$ 11,320.00	$ 5,160.00	$ 2,580.00	$ 3,140.00	$ 1,570.00	$ 54,880.00	$ 27,440.00

Statement showing Fixed Manufacturing overhead
Particulars	Total	Bikini	Broad short	Towel	Beach Bag
Utilities	$ 6,600.00	$ 2,879	$ 2,723	$ 621	$ 378
Insurance	$ 2,400.00	$ 1,047	$ 990	$ 226	$ 137
Supervisors salary	$ 54,000.00	$ 23,556	$ 22,277	$ 5,077	$ 3,090
Rent	$ 67,200.00	$ 29,314	$ 27,722	$ 6,318	$ 3,845
Repair and Maintenance	$ 10,000.00	$ 4,362	$ 4,125	$ 940	$ 572
Depreciation	$ 9,000.00	$ 3,926	$ 3,713	$ 846	$ 515
Total Fixed Manufacturing Overhead	$ 149,200.00	$ 65,084.69	$ 61,550.44	$ 14,028.28	$ 8,536.59

Statement showing monthly operating cost budget
Months	Utilities	Insurance	Administrative Wages	Office Expenses	Rent
Jan-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Feb-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Mar-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Apr-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
May-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Jun-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Jul-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Aug-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Sep-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Oct-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Nov-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Dec-18	$ 50.00	$ 500.00	$ 2,500.00	$ 1,500.00	$ 1,400.00
Total Expenses	$ 600.00	$ 6,000.00	$ 30,000.00	$ 18,000.00	$ 16,800.00

Statement showing closing inventory of Finished goods
Months	Bikini	Board Short	Towel	Beach Bag	Total
Jan-18	$ 60,000.00	$ 41,250.00	$ 6,750.00	$ 5,850.00	$ 113,850.00
Feb-18	$ 65,000.00	$ 43,500.00	$ 5,850.00	$ 5,200.00	$ 119,550.00
Mar-18	$ 50,000.00	$ 37,500.00	$ 3,150.00	$ 3,900.00	$ 94,550.00
Apr-18	$ 20,000.00	$ 22,500.00	$ 1,800.00	$ 1,950.00	$ 46,250.00
May-18	$ 25,000.00	$ 26,250.00	$ 900.00	$ 1,300.00	$ 53,450.00
Jun-18	$ 20,000.00	$ 18,750.00	$ 900.00	$ 1,300.00	$ 40,950.00
Jul-18	$ 10,000.00	$ 15,000.00	$ 900.00	$ 1,300.00	$ 27,200.00
Aug-18	$ 65,000.00	$ 43,500.00	$ 5,850.00	$ 5,200.00	$ 119,550.00
Sep-18	$ 60,000.00	$ 41,250.00	$ 6,750.00	$ 5,850.00	$ 113,850.00
Oct-18	$ 60,000.00	$ 41,250.00	$ 6,750.00	$ 5,850.00	$ 113,850.00
Nov-18	$ 75,000.00	$ 45,000.00	$ 9,000.00	$ 6,500.00	$ 135,500.00
Dec-18	$ 80,000.00	$ 52,500.00	$ 9,900.00	$ 7,150.00	$ 149,550.00

Statement showing Cost of sales Budget
Particulars	Bikini	Board Short	Towel	Beach Bag	Total
Direct Material	$386,032.50	$387,710.00	$81,270.00	$41,605.00	$896,617.50
Direct Labor	$335,160.00	$237,720.00	$28,896.00	$26,376.00	$628,152.00
Variable Manufacturing Overhead
Indirect Material	$23,940.00	$22,640.00	$5,160.00	$3,140.00	$54,880.00
Indirect Labor	$11,970.00	$11,320.00	$2,580.00	$1,570.00	$27,440.00
Fixed Manufacturing Overhead
Utilities	$2,879.08	$2,722.74	$620.55	$377.62	$6,600.00
Insurance	$1,046.94	$990.09	$225.66	$137.32	$2,400.00
Supervisors salary	$23,556.12	$22,276.97	$5,077.26	$3,089.65	$54,000.00
Rent	$29,314.29	$27,722.45	$6,318.37	$3,844.90	$67,200.00
Repair and Maintenance	$4,362.24	$4,125.36	$940.23	$572.16	$10,000.00
Depreciation	$3,926.02	$3,712.83	$846.21	$514.94	$9,000.00
Total manufacturing Cost	$822,187.19	$720,940.44	$131,934.28	$81,227.59	$1,756,289.50
Opening Inventory of FG	$ 75,000.00	$ 45,000.00	$ 9,000.00	$ 6,500.00	$135,500.00
Closing Inventory of FG	$ (80,000.00)	$ (52,500.00)	$ (9,900.00)	$ (7,150.00)	($149,550.00)
Cost of Sales	$817,187.19	$713,440.44	$131,034.28	$80,577.59	$1,742,239.50

Statement showing budgeted Income Statement
Particulars	Bikini	Board Short	Towel	Beach Bag	Total
Sales	$1,170,000	$841,500	$115,200	$101,400	$2,228,100
Less:
Cost of sales	$817,187.19	$713,440.44	$131,034.28	$80,577.59	$1,742,239.50
Gross Profit	$352,812.81	$128,059.56	($15,834.28)	$20,822.41	$485,860.50
Operating Cost
Utilities					$600.00
Insurance					$6,000.00
Administrative Wages					$30,000.00
Office Expenses					$18,000.00
Rent					$16,800.00
Total Operating Expenses					$71,400.00
Operating Income					$414,460.50
Interest					$7,500.00
Net Profit					$406,960.50

Statement showing Monthly Cash Budget
Particulars	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
Cash Receipts
Cash collection	$307,930	$241,556	$235,452	$205,100	$123,412	$102,292	$89,900	$63,200	$179,996	$231,348	$227,700	$257,144
Cash Payments
Accounts Payable paid in the same month	$28,324	$26,002	$20,197	$13,013	$11,731	$9,361	$12,287	$24,139	$27,090	$28,420	$31,758	$33,064
Accounts Payable paid in the next month	$92,500	$66,088	$60,670	$47,125	$30,363	$27,372	$21,841	$28,670	$56,325	$63,210	$66,314	$74,102
Direct Labor	$75,222	$64,722	$59,472	$39,144	$27,762	$26,320	$18,970	$40,740	$64,722	$63,084	$69,062	$78,932
Indirect Material	$6,520	$5,660	$5,140	$3,400	$2,420	$2,260	$1,660	$3,560	$5,660	$5,560	$6,080	$6,960
Indirect Labor	$3,260	$2,830	$2,570	$1,700	$1,210	$1,130	$830	$1,780	$2,830	$2,780	$3,040	$3,480
Repair and Maintenance			$2,500			$2,500			$2,500			$2,500
Utilities	$600	$600	$600	$600	$600	$600	$600	$600	$600	$600	$600	$600
Insurance premium	$700	$700	$700	$700	$700	$700	$700	$700	$700	$700	$700	$700
Factory Supervisors salary	$4,500	$4,500	$4,500	$4,500	$4,500	$4,500	$4,500	$4,500	$4,500	$4,500	$4,500	$4,500
Administrative Wages	$2,500	$2,500	$2,500	$2,500	$2,500	$2,500	$2,500	$2,500	$2,500	$2,500	$2,500	$2,500
General Office Expenses	$1,500	$1,500	$1,500	$1,500	$1,500	$1,500	$1,500	$1,500	$1,500	$1,500	$1,500	$1,500
Rent	$7,000	$7,000	$7,000	$7,000	$7,000	$7,000	$7,000	$7,000	$7,000	$7,000	$7,000	$7,000
Interest	$625	$625	$625	$625	$625	$625	$625	$625	$625	$625	$625	$625
Total cash Payment	$223,251	$182,727	$167,974	$121,807	$90,911	$86,368	$73,014	$116,314	$176,552	$180,479	$193,679	$216,464
Net Cash generated	$84,679	$58,829	$67,478	$83,293	$32,501	$15,924	$16,886	($53,114)	$3,444	$50,869	$34,021	$40,680
Opening Cash Balance	$48,250	$132,929	$191,759	$259,237	$342,530	$375,030	$390,954	$407,841	$354,727	$358,171	$409,040	$443,060
Closing Cash Balance	$132,929	$191,759	$259,237	$342,530	$375,030	$390,954	$407,841	$354,727	$358,171	$409,040	$443,060	$483,741

Answer to Question 2

Introduction

In this report, an attempt is made to analyze and present the budgeted result to the director of the company. The company is engaged in the manufacturing of the swimwear and accessories for the men and women (Crowell et al., 2013). The company manufactures four types of products. The main aim of this report is to discuss the estimated and the budgeted resulted of the company.

Over view of the result

The overall analysis of the budget indicates that the company is estimated to make a net profit of $7500. The total gross profit of the company is $485860.50. It can be seen that only product that has reported gross loss is Towel. The analysis of the cash flow statement shows that the company has generated cash inflow in all the months except in the month of August (Macnab & Mitchell, 2014). The figure showing sales of different products in the 12 months are given below.

Figure 1: Sales

(Source: created by Author)

The figure above shows that sales declined in the middle of the year then it recovered at the end of the month. Therefore, on overall analysis it can be seen that the company has improved its performance.

Analysis of contribution

Statement showing Contribution per unit
Particulars	Bikini	Board Short	Towel	Beach Bag
Sales	$100	$75	$45	$65
Less: Variable cost
Fabric	$18.75	$0.00	$31.25	$26.25
Elastic/Trim	$13.50	$0.00	$3.00	$5.25
Direct labor	$28.00	$21.00	$28.40	$16.80
Indirect material	$2	$2	$2	$2
Indirect labor	$1	$1	$1	$1
Total variable Cost	$63.25	$24.00	$65.65	$51.30
Contribution	$36.75	$51.00	($20.65)	$13.70

Table 1: Contribution per unit

(Source: created by Author)

The table above indicates the contribution per unit of different products of the company. It can be said that the company should not produce Towel as the contribution is negative and the company will make loss if the company engages in the production.

Cash Position of the company

Figure 2: Cash position

(Source: created by Author)

The figure above shows that the company has maintained high cash flow throughout the period. It can be seen that the cash position of the business have slowly increased and have been maintained at high level. The main reason for such increase in cash balance is increased sales and controlled expenditure (Dudin et al. 2015).

Analysis and Recommendation

The data of the manufacturing sector of Australia is provided below:

Figure 3: Industry data

(Source: created by Author)

The figure above shows the manufacturing industry is showing a decline of 1.7% in growth. It is expected that the manufacturing industry will see a decline of 0.7% from 2012 to 2017. However, the revenue of the industry is expected to increase by 1.7%. Therefore, it is expected that the business will make profit so the company should continue in the manufacturing but should stop producing towel, as it is not profitable (Gebreiter, 2016).

Conclusion

Based on the above discussion it can be said that it is expected that the company should make profit and will be able to maintain good cash position. However, the business should producing towel, as it is not profitable.

Reference

Ashfaq, K., Younas, S., Usman, M., & Hanif, Z. (2014). Traditional Vs. Contemporary Management Accounting Practices and its Role and Usage across Business Life Cycle Stages: Evidence from Pakistani Financial Sector. International Journal of Academic Research in Accounting, Finance and Management Sciences, 4(4), 104-125.

Brine, A. (Ed.). (2016). Handbook of Library Training Practice and Development: Volume Three. Routledge.

Crowell, V., Levin, A., Galactionova, K., & Tediosi, F. (2013). Costing and budgeting for malaria service delivery.

Dudin, M. N., Kucuri, G. N., Fedorova, I. J. E., Dzusova, S. S., & Namitulina, A. Z. (2015). The innovative business model canvas in the system of effective budgeting.

Gebreiter, F. (2016). “Comparing the incomparable”: Hospital costing and the art of medicine in post-war Britain. The British Accounting Review, 48(2), 257-268.

Houben, R. M. G. J., Lalli, M., Sumner, T., Hamilton, M., Pedrazzoli, D., Bonsu, F., ... & Pretorius, C. (2016). TIME Impact–a new user-friendly tuberculosis (TB) model to inform TB policy decisions. BMC medicine, 14(1), 56.

Jindrichovska, I., & Kubickova, D. (2014). The development of accounting and application of IFRS in the Czech Republic. Accounting and Management Information Systems, 13(2), 198.

Macnab, A. J., & Mitchell, F. (2014). Outcome budgeting in the public sector: challenges and solutions. An exploratory empirical study. In World Congress of Accountants 2014.

Moldogaziev, T. T., Kioko, S. N., & Hildreth, W. B. (2016). Impact of Bankruptcy Eligibility Requirements and Statutory Liens on Borrowing Costs. Public Budgeting & Finance.

Segun, A., & Olamide, F. T. (2015). The global debate on budgeting: Empirical evidence from Nigeria. Business Management Review, 13(1).

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2021). Excel Budgeting For The Year 2018. Retrieved from https://myassignmenthelp.com/free-samples/acc00146-management-accounting/sales-revenue-and-cash-collection-budget.html.

"Excel Budgeting For The Year 2018." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/acc00146-management-accounting/sales-revenue-and-cash-collection-budget.html.

My Assignment Help (2021) Excel Budgeting For The Year 2018 [Online]. Available from: https://myassignmenthelp.com/free-samples/acc00146-management-accounting/sales-revenue-and-cash-collection-budget.html
[Accessed 19 April 2024].

My Assignment Help. 'Excel Budgeting For The Year 2018' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/acc00146-management-accounting/sales-revenue-and-cash-collection-budget.html> accessed 19 April 2024.

My Assignment Help. Excel Budgeting For The Year 2018 [Internet]. My Assignment Help. 2021 [cited 19 April 2024]. Available from: https://myassignmenthelp.com/free-samples/acc00146-management-accounting/sales-revenue-and-cash-collection-budget.html.

Get instant help from 5000+ experts for

Writing Rewriting Editing

Subject/course code

❮ ❯

Pages

250 words

Order description (write/attach)

Attach file

I accept the T&C, agree to receive offers & updates

Have a coupon code?