Budgeting and Control
Individual task
Students should prepare a Master Budget on Excel and provide an analysis of the financial situation of the company, that will demonstrate a clear understanding of management accounting and the learning objectives discussed in this course.
Key contextual elements should include management accounting, when it comes to operational budgets, cash budgets, pro forma financial statements and also excel processing.
Title page, Written Body, along with a Bibliography of any written references, images, or diagrams used (if applicable). NOTE: Formal Written Reports must include a Title Page, Table of Contents, and Appendix of Financial Statements.
Formal Written Report saved and uploaded to Moodle in PDF format. More information are in the files.
BCO322 Budgeting and Control Task brief Final Exam assignment
Preparation of a Master Budget on Excel
Individual task
Students should prepare a Master Budget on Excel and provide an analysis of the financial situation of the company, that will demonstrate a clear understanding of management accounting and the learning objectives discussed in this course.
Key contextual elements should include management accounting, when it comes to operational budgets, cash budgets, pro forma financial statements and also excel processing.
Title page, Written Body, along with a Bibliography of any written references, images, or diagrams used (if applicable). NOTE: Formal Written Reports must include a Title Page, Table of Contents, and Appendix of Financial Statements.
Formal Written Report saved and uploaded to Moodle in PDF format.
Formalities:
Word count: N/A
Cover, Table of Contents, References and Appendix are excluded of the total word count.
Font: Arial 12,5 pts.
Text alignment: Justified.
The in-text References and the Bibliography have to be in Harvards citation style.
Submission: Week 8 Via Moodle (Turnitin). By no later than Sunday, September 20th at 23:59 (11:59pm) CEST.
Weight: This task represents 60% of your total grade for this subject.
It assesses the following learning outcomes:
Outcome 1: Examine on how to prepare a master budget based on various assumptions and predictions
Outcome 2: Demonstrate understanding on how to prepare cash budgets and pro forma income statement and proforma balance sheet
Outcome 3: Analyze the way in which costs can be managed
Outcome 4: Demonstrate working knowledge of real-world budget issues using excel
Rubrics
Exceptional 90-100
Good 80-89
Fair 70-79
Marginal fail 60-69
Knowledge & Understanding (20%)
Student demonstrates excellent understanding of key concepts and uses vocabulary in an entirely appropriate manner.
Student demonstrates good understanding of the task and mentions some relevant concepts and demonstrates use of the relevant vocabulary.
Student understands the task and provides minimum theory and/or some use of vocabulary.
Student understands the task and attempts to answer the question but does not mention key concepts or uses minimum amount of relevant vocabulary.
Application (30%)
Student applies fully relevant knowledge from the topics delivered in class.
Student applies mostly relevant knowledge from the topics delivered in class.
Student applies some relevant knowledge from the topics delivered in class. Misunderstanding may be evident.
Student applies little relevant knowledge from the topics delivered in class. Misunderstands are evident.
Critical Thinking (30%)
Student critically assesses in excellent ways, drawing outstanding conclusions from relevant authors.
Student critically assesses in good ways, drawing conclusions from relevant authors and references.
Student provides some insights but stays on the surface of the topic. References may not be relevant.
Student makes little or none critical thinking insights, does not quote appropriate authors, and does not provide valid sources.
Communication (20%)
Student communicates their ideas extremely clearly and concisely, respecting word count, grammar and spellcheck
Student communicates their ideas clearly and concisely, respecting word count, grammar and spellcheck
Student communicates their ideas with some clarity and concision. It may be slightly over or under the wordcount limit. Some misspelling errors may be evident.
Student communicates their ideas in a somewhat unclear and unconcise way. Does not reach or does exceed wordcount excessively and misspelling errors are evident. Course: Budgeting and Control
Issue: Instructions for Final Exam, The Master Budget
Due date: Week 8
Instructions for the Final Exam, The Master Budget for Hera Manufacturing
Complete the Master Budget on Excel for Hera Manufacturing
Use the IF Functions
Provide a brief analysis of the financial forecasted situation of the company. This should be approximately of a page.
If observed similar analysis with similar wordings between students answers the question will be receiving zero points
Make sure that 1 budget = 1 page. Otherwise it cannot be corrected! Format each page!
Save as a pdf file before uploading on Moodle
The weight for the exam paper:
30% for Sales budget/ S&A exp budget/ Direct Materials budget/
Mfg. OH budget/Production budget
40% for cash budget
10% for Budgeted Income Statement
10% for Budgeted Balance Sheet
10% for brief analysis of companys forecasted financial situation
During week 8 I will be available during class to assist with some help for the budgets 1 -5. No help will be given for Cash Budget and the forecasted financial statements.
Plan a head in life! Hera sales
HERA Manufacturing Company
Month Sales
Year 1 Jan 13,114
Feb 10,793
Mar 12,765
Apr 13,573
May 20,930
Jun 22,830
Jul 25,206
Aug 18,360
Sep 10,199
Oct 12,672
Nov 12,851
Dec 16,246
Year 2 Jan 15,716
Feb 17,341
Mar 20,032
Apr 14,853
May 24,324
Jun 27,890
Jul 29,185
Aug 20,494
Sep 15,011
Oct 17,786
Nov 12,485
Dec 19,547
Year 3 Jan 17,160
Feb 21,596
Mar 23,569
Apr 15,779
May 25,183
Jun 30,255
Jul 30,999
Aug 25,407
Sep 15,542
Oct 20,495
Nov 19,115
Dec 20,520
Year 4 Jan 21,242
Feb 19,968
Mar 24,674
Apr 21,972
May 23,949
Jun 35,586
Jul 40,042
Aug 32,246
Sep 17,480
Oct 21,599
Nov 16,875
Dec 21,242
Background:
These are the sales data of Hera, a winter sportwear manufacturer which manufacturers skis. You are Hera sales manager.
You have to prepare a 12-month forecast for Year 5 in units and euros.
You have the monthly unit sales data for the last 4 years; your average unit price to distributors has been 105 last year and you do not think it is possible to increase it.
Cash collection is as follows:
80% of sales of month M are collected in M+1
19% of sales of month M are collected in M+2
1% of sales is never collected
Forecast
Hera Manufacturing Forecast in Units
Month Sales MA-12 RSt DSt
1 Jan 13,114 Year 1 Year 2 Year 3 Year 4 Average 14,057
2 Feb 10,793 January 0.98 0.87 0.95 0.93 10,724
3 Mar 12,765 February 1.05 1.08 0.89 1.01 11,175
4 Apr 13,573 March 1.17 1.16 1.10 1.14 15,716
5 May 20,930 April 0.86 0.77 0.96 0.86 17,136
6 Jun 22,830 May 1.39 1.23 1.05 1.22 15,075
7 Jul 25,206 June 1.55 1.46 1.53 1.51 15,924
8 Aug 18,360 July 1.59 1.49 1.67 1.58 15,928
9 Sep 10,199 August 1.11 1.20 1.15 13,372
10 Oct 12,672 September 0.79 0.73 0.76 13,522
11 Nov 12,851 October 0.92 0.95 0.94 16,973
12 Dec 16,246 15,795 1.03 November 0.65 0.87 0.76 16,492
13 Jan 15,716 16,012 0.98 December 1.03 1.00 0.93 0.99 16,846
14 Feb 17,341 16,557 1.05 12.86 17,230
15 Mar 20,032 17,163 1.17 17,537
16 Apr 14,853 17,270 0.86 17,198
17 May 24,324 17,552 1.39 19,915
18 Jun 27,890 17,974 1.55 18,416
19 Jul 29,185 18,306 1.59 18,438
20 Aug 20,494 18,484 1.11 17,778
21 Sep 15,011 18,885 0.79 19,682
22 Oct 17,786 19,311 0.92 18,979
23 Nov 12,485 19,280 0.65 16,490
24 Dec 19,547 19,555 1.00 19,844
25 Jan 17,160 19,676 0.87 18,394
26 Feb 21,596 20,030 1.08 21,457
27 Mar 23,569 20,325 1.16 20,634
28 Apr 15,779 20,402 0.77 18,270
29 May 25,183 20,474 1.23 20,618
30 Jun 30,255 20,671 1.46 19,978
31 Jul 30,999 20,822 1.49 19,584
32 Aug 25,407 21,231 1.20 22,041
33 Sep 15,542 21,276 0.73 20,378
34 Oct 20,495 21,501 0.95 21,870
35 Nov 19,115 22,054 0.87 25,246
36 Dec 20,520 22,135 0.93 20,831
37 Jan 21,242 22,475 0.95 22,769
38 Feb 19,968 22,340 0.89 19,840
39 Mar 24,674 22,432 1.10 21,601
40 Apr 21,972 22,948 0.96 25,440
41 May 23,949 22,845 1.05 19,609
42 Jun 35,586 23,289 1.53 23,498
43 Jul 40,042 24,043 1.67 25,298
44 Aug 32,246 24,613 1.31 27,974
45 Sep 17,480 24,774 0.71 22,918
46 Oct 21,599 24,866 0.87 23,048
47 Nov 16,875 24,679 0.68 22,288
48 Dec 21,242 24,740 0.86 21,564
Slope = 228
Intercept = 13,564
Year 5 forecast in units
48 Tt Ft
Jan 49 24,753 23,093
Feb 50 24,981 25,142
Mar 51 25,209 28,796
Apr 52 25,438 21,969
May 53 25,666 31,348
Jun 54 25,894 39,216
Jul 55 26,123 41,349
Aug 56 26,351 30,376
Sep 57 26,580 20,272
Oct 58 26,808 25,122
Nov 59 27,036 20,471
Dec 60 27,265 26,857
Jan 61 27,493 25,649
Sales budget
Hera Manufacturing – Sales and Cash Collection Budget
Year 4 Year 5 Year 6 Year 5
Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Total Sales
Units [pairs of skis] 21,242 23,093 25,142 28,796 21,969 31,348 39,216 41,349 30,376 20,272 25,122 20,471 26,857 25,649 334,009
ASP [] 105 105 105 105 105 105 105 105 105 105 105 105 105 105
Amount [] 2,230,374 2,424,761 2,639,906 3,023,537 2,306,778 3,291,526 4,117,639 4,341,623 3,189,436 2,128,570 2,637,802 2,149,430 2,819,978 2,693,185 35,070,984
80% of sales 2,111,924 2,418,829 1,845,422 2,633,221 3,294,111 3,473,298 2,551,549 1,702,856 2,110,241 1,719,544 2,255,982
19% of sales 460,705 501,582 574,472 438,288 625,390 782,351 824,908 605,993 404,428 501,182 408,392
Cash inflow – 0 – 0 2,572,629 2,920,411 2,419,894 3,071,509 3,919,501 4,255,650 3,376,457 2,308,849 2,514,670 2,220,726 2,664,374
Accounts receivable 4,021,285 3,200,170
Cash Collection Expectations
80% of sales of month M are collected in M+1
19% of sales of month M are collected in M+2
1% of sales is never collected
S&A Exp Budget
Hera Manufacturing – Selling & Administrative Expense Budget
Year 5
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Units [pairs of skis] – 0
ASP []
Amount [] – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Selling and Admin Expenses [] Total
Sales salaries &benefits F – 0
Sales commissions V – 0
Selling expenses (TML) F – 0
Advertising expenses F – 0
Promotions V – 0
Not collected sales V – 0
Administrative salaries F – 0
General Management F – 0
Shipping expenses V – 0
Office rent F – 0
Office utilities (elect, water) F – 0
Telecom expenses SV – 0
IT expenses F – 0
Insurance F – 0
Total – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Cash outflow (1) – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Selling and Administrative Expenses
Sales salaries & benefits: 20 sales persons at average 100’000/yr .
Sales commissions: 10% of salary & benefits paid quarterly if quarter sales > 7.5M (Starting in March.)
Selling expenses: Travel, meals and lodging of the 20 sales persons (Historical data indicate about 1K/month.sales person)
Advertising expenses: None in May to Aug. 100K in Jan, Mar, Apr & Sept; 300K in Feb and Oct; 500K in Nov and Dec
Promotions: Sell-in promotions of 50K in Jun and Jul, of 60K in Aug and Sell-out promotions of 500K in Mar and Dec.
Administrative salaries: 15 persons with average salary and benefits of 60K/yr
General management: Salaries &n benefits of the CEO (200K), CMO (180K), CFO (180K) and a secretary (80K)
Shipping expenses: 150 per 100 pairs of skis
Office rent: 240K/yr
Office utilities: Historical data indicate average monthly expenses of 1K
Telecom expenses: Fixed monthly expenses: 1K; Variable expenses : 0.003 /Sales amount
IT expenses: 65 per person employed (exluding CEO; CMO; CFO and secretary)
Insurance: 80K paid quarterly at the begining of quarter
Direct Mat Budget
Hera Manufacturing – Direct Material Budget
Year 5 Year 6
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan
Units [pairs of skis]
ASP []
Amount [] – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Desired ending inventory – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Needed quantity – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Begining Inventory – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Quantity to be produced – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Direct Material Budget []
Fiber glass 30/ski pair – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Polymers 5/ski pair – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Steel 7/ski pair – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Plastic 1/ski pair – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Glue 1/ski pair – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Total – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Accounts payable 910,000 – 0
Cash outflow (2) 910,000 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Inventory
Desired ending inventory = 20% of following month units forecast to be sold
Direct Material Budget:
Fiber glass 30 per pair of skis
Polymers 5 per pair of skis
Steel 7 per pair of skis
Plastic 1 per pair of skis
Glue 1 per pair of skis
Cash disbursement:
Suppliers are paid the amount of material used in month M in M+1
Closing inventory on Dec. 31, Year 4 was 5’027 pairs of skis
Accounts payable on Dec. 31, Year 4 were 910’000
Direct Labor Budget
Hera Manufacturing – Direct Labor Budget
Year 5 Year 6
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan
Units [pairs of skis]
ASP []
Amount [] – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Desired ending inventory – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Needed quantity – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Begining Inventory – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Quantity to be produced – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Direct Labour expenses []
Direct salaries & benefits F – 0
Direct labor overtime cost V – 0
Direct supervision s&b F – 0
Supervision overtime costs: V – 0
Engineers’ salaries & benefits F – 0
Total – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Cash outflow (3) – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Direct Labour Expenses:
Direct salaries & benefits: 56 workers with a fixed salary & benefits of 25K/yr on average. A worker produces on average 25 pairs of skis per labor day.
Direct labor overtime costs: 15 per overtime hour. 3 pairs of skis can be produced per hour.
Direct supervision s&b: 8 foremen lead the 8 7-member teams. Each foreman costs 35K/yr on average.
Supervision overtime costs: 21 per overtime hour.
Engineers’ salaries & benefits: 2 engineers at 50K/yr each. They maintain the 8 production lines, supervise the ski test process and ensure the material incoming inspection
Mfg OVH Budget
Hera Manufacturing – Manufacturing Overhead Budget
Year 5 Year 6
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan
Units [pairs of skis]
ASP []
Amount [] – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Desired ending inventory – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Needed quantity – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Begining Inventory – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Quantity to be produced – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Manufacturing Overhead Expenses []
Depreciation of equipment F – 0
General Production Management s&b F – 0
Insurance F – 0
Plant Utilities (electricity, energy, water) SV – 0
External services: – 0
Maintenance expenses: – 0
IT expenses – 0
Total – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Cash outflow (4): – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Manufacturing Overhead Expenses:
Depreciation of plant & equipment: 10% per year on plant and machinery worth 22M (not part of the cash outflow)
General Production Management s&b: Salaries & benefits of COO (170K), manufacturing accountant (90K), purchaser (90K), secretary (60K)
Insurances: 200K/yr paid in advance quarterly, starting in January
Plant Utilities: Historical data indicate a monthly average of 50K for electricity, energy (fuel, gas) and water plus 10K when production runs over 29’400 pairs/month
External services: Miscellaneous services from plumbing to recruiting agency. Historical data put these services at 5K/month
Maintenance expenses: Maintenance contracts are worth 250K/yr plus extras for special maintenance and repairs for 110K/yr (historical data)
IT expenses: Updates and upgrades of software and hardware of IT equipment. Contracts and historical data indicate a monthly average of 10K.
Production Budget
Hera Manufacturing – Production Budget
Year 5 Year 6
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan
Units [pairs of skis]
ASP []
Amount [] – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Desired ending inventory – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Needed quantity – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Begining Inventory – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Quantity to be produced – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Direct Material Budget [] – 0
Direct Labor Budget [] – 0
Manufacturing Overhead Budget [] – 0 Unit cost ERROR:#DIV/0!
Total – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
COGS = ERROR:#DIV/0!
Inventory= ERROR:#DIV/0!
Cash outflow (2)
Cash outflow (3)
Cash outflow (4)
Cash outflow (5) – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Cash Budget
Hera Manufacturing – Cash Budget
Year 4 Year 5 Year 5
Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Units [pairs of skis] 21,242
ASP [] 105
Amount [] 2,230,374 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Opening cash balance
Cash collection Cash inflow (1) – 0
Cash available – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
S&A cash outflow Cash outflow (1) – 0
Production cash outflow Cash outflow (5) – 0
Equipment purchase – 0
Taxes – 0
Dividend – 0
Excess (deficiency) – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Minimum cash balance
Interests on ST debt – 0
Interests on LT debt
Borrowing – 0
Repayment – 0
Accumulated ST Borrowing – 0
Closing cash balance
Cash budget elements:
Minimum closing cash balance: 100’000
Opening cash balance: 889’113
Equipment purchases: 1’480’000 in March and 1’800’000 in October
Taxes: 980’800 in April and 1’300’000 in October
Dividend: 500K at each quarter starting in February
Interests on ST debt: 12% per annum payable monthly
Interests in LT debt: 6% per annum on 8M paid at each quarter end, starting in March
Proforma IS
Hera Manufacturing – Year 5 Pro Forma Income Statement
Year 5
[]
Sales revenues
COGS ERROR:#DIV/0!
S&A expenses
Operating profit
Interests
Earnings before tax
Taxes
Net Earnings
Proforma BS
Hera Manufacturing – Year 5 Pro Forma Balance Sheet
Balance sheet as of Dec. 31 of Year 5
Assets Liabilities
Cash Accounts payable – 0
Accounts receivable
Inventory
Current assets – 0 Current liabilities – 0
Plant & machinery 22,000,000 Long term debt Plant & machinery = 22,000,000
Depreciation – 0 Equity 22,000,000
Land Net earnings – 0
Dividend – 0
Total assets 22,000,000 22,000,000
Plant & machinery as of Dec. 31 of Year 4: 22’000’000
Land value: 2’000’000
Equity as of Dec. 31 of Year 4: 17’143’079
