First Quarter Forecast

 

Coursework Tasks

Task 1: 2010 Forecast

Part A. First Quarter Forecast
The owner of the Network Cafe wants you to create a forecast of estimated sales and expenses for the first quarter of 2010. The data for the forecast is below:-

Sales
JAN FEB MAR
Beverages £12,600 £12,900 £13,200
Food £7,100 £7,300 £7,400
Internet £4,000 £4,900 £5,800
Merchandise £3,100 £3,200 £3,300

Expenses
JAN FEB MAR
Cost of Goods £6,700 £6,875 £7,000
Payroll £7,500 £7,500 £7,500
Computers £6,400 £6,400 £6,400
Rent £5,500 £5,500 £5,500
Marketing £1,000 £1,000 £1,000
Miscellaneous £750 £750 £1,000

Enter this data onto a worksheet (named “First Quarter”).
? Add rows and columns that calculate total sales and total expenses for each month of the quarter and for the quarter as a whole (you must use the SUM function to calculate all totals).
? Add rows that calculate both the net income andthe profit margin for each month and for the quarter as a whole.

The profit margin should be expressed as a percentage. Use either conditional formatting or a custom number format to show any losses in brackets with a red font, and profits with a blue font.

In an appropriate location on the worksheet, enter the label “Income Year-To-Date”.The cell to the right of this label should calculate the quarterly income for the first quarter.

You must decide on a suitable layout for the worksheet. Apply appropriate formatting to create an attractive but usable worksheet. Pay attention to the alignment and indentation of text, number and currency formats, fonts, shading, and use of titles, labels and graphics, where appropriate. The layout and formatting chosen should make the forecast data easy to understand and analyse.

Part B. Solver Problem
The owner of the Network Café is not satisfied with the current profit margin for the First Quarter. He wants to increase the profit margin for the quarter as a whole to 2.25%. The owner proposes to do this by reducing expenses and increasing sales, as follows:-

Reducing expenses
? Reducing payroll expenses in both February and March to £7350
? Reducing marketing expenses in March to £750

Increasing sales
? Increasing January Merchandise sales by means of a post-Christmas Sale.

The owner believes he will have to spend an additional £250 on marketing in January in order to promote the Sale. He needs to know the answer to the following question:-

How much Merchandise does he need to sell in January in order to achieve a quarterly profit margin of 2.25%? (In other words, what sales are needed in January to achieve his target, given the above constraints?).

Use Solver to answer this question. Create an Answer Report comparing the original and final values for the Target and Adjustable. Reformat the answer report to create a usable and attractive report that is easy to understand.

Part C: Second Quarter Forecast
The owner now wants you to extend the forecast to the second quarter. Create an additional worksheet in the same workbook named “Second Quarter” using the following data.

Sales
APR MAY JUN
Beverage £13,400 £13,800 £14,300
Food £7,500 £7,600 £7,600
Internet £6,200 £7,200 £8,200
Merchandise £3,200 £3,200 £3,200
Expenses
APR MAY JUN
Cost of Goods £7,100 £7,600 £8,000
Payroll £7,280 £7,450 £7,600
Computers £6,400 £6,400 £6,400
Lease £5,500 £5,500 £5,500
Marketing £1,200 £1,300 £1,400
Miscellaneous £1,400 £1,800 £2,200

Recalculate all the new totals, net income and profit margins. Keep the same layout as the “First Quarter” sheet, but modify the title, and apply different shading and font colours.

Enter the label “Income Year-To-Date” in the same cell reference used in the “First Quarter” worksheet.The cell to the right of this label should calculate the total income for the first and second quarters. The formula in this cell must use the SUM function with a 3-D reference to the first and second quarter worksheets.

Create a third worksheet in the same workbook (named “6 Months”). This worksheet should show the sales and expenses for the two quarters (i.e. January to June).
JAN FEB MAR APR MAY JUN Total
Sales
Beverage ? ? ? ? ? ? ?
Food ? ? ? ? ? ? ?
Etc ? ? ? ? ? ? ?

 

 

 

 

The worksheet should have running totals for both the quarterly income to date and the quarterly
profit margin, as shown below. These should be positioned in appropriate rows and columns in the worksheet.

 

Note: All cells on the “6 months” worksheet (including all formulas and functions) must use 3D cell references.

Save the workbook as Forecast

Part D: Scenarios
The owner is confident that the new forecast for the first quarter is realistic and reasonably accurate. The second forecast, however, is less reliable. This is due to 2 factors:-

1. Increase in the summer tourist trade in May and June (boosting sales of Internet connections and beverages)
2. Possible increase in the rental of Network Café premises.

The owner believes the most likely scenario is the one reflected in the present figures for the second quarter, but he would like you to create 2 additional scenarios that show the best and worst case scenarios for the second-quarter forecast.

The owner thinks the best-case scenario is as follows:-
? Internet sales increase to £9,000 in May and £10,250 in June
? Beverage sales increase to £16,445 in June alone
? All other values remain unchanged

The owner’s worst-case scenario is as follows:-
? Internet sales decrease 10% below the current estimates in May and June
? Beverage sales remain the same
? Rent in April, May and June increases from £5500 to £6000

Use the Scenario tool to show the 3 alternative scenarios on the Second Quarter sheet.
Add 3 command buttons to the right of the Second Quarter worksheet data. Create 3 macros and assign them to the each of the appropriately named buttons.

Clicking on each button should accomplish the following:-
? Load the correct values for each of the corresponding scenarios into the correct cells
? Load the name of each scenario into an appropriate cell of the worksheet
? Change the cell shading and font colour for each of the scenario names

Create a Scenario Summary Report on a separate worksheet. Reformat and edit the report (including all labels and cell references) so that it clearly shows the effects of each scenario on the relevant cells.
Part E: Charts
Having looked at the figures for the first quarter forecast, the owner notices that sales of Internet connections show the greatest growthpotential. He believes that these sales will in turn drive sales of beverages, food and merchandise. He would like to launch an advertising campaign to increase Internet sales, but needs to convince his investors. In order to illustrate these trends the owner wants you to chart the sales forecast data for the First Quarter.

Create 2 charts from the First Quarter data on the same worksheet, underneath the source data.
Chart 1 must be a column chart. Chart 2 must be a line chart.

Both charts should enable sales in all categories to be compared for each month of the first quarter. Apply appropriate formatting to the charts, paying attention to titles, legends, gridlines, font sizes, X and Y axis values and labels. You should also find a way of drawing attention to forecasted growth in Internet sales by means of arrows, text boxes or other annotations.

On the First Quarter worksheet create an additional chart, to the right of the source data, similar to the one below.

 

Note: the sample data above is for illustration purposes. You must use the correct data from Task 1.
On the Second Quarter worksheet create another chart, to the right of the worksheet data, similar to the one below.

 

Note: the sample data above is for illustration purposes. You must use the correct data from Task 1.
Task 2: Loan Analysis
The owner of the Network Café is purchasing a property that will house the company’s new head offices. It is a major investment and a substantial loan will be needed in order to finance it. The owner wants to see an analysis of the monthly repayments for such a loan, depending on the purchase price, the size of deposit, the loan amount and varying interest rates. He also wants to see the effects of varying interest rates and terms on the monthly payments.

You decide to create a loan analysis spreadsheet that uses the PMT function, command buttons and macros. The worksheet should include 2 tables, as shown below. These should be on the same worksheet (named “Loan Analysis”).

Table 1
Network Café: Loan Analysis
Property Postcode N22 1AJ
Purchase Price £???
Deposit £???
Loan Amount £???
Interest Rate ??.?%
Terms in Years ??
Monthly Payment £??
Term increments ??
Interest increments ??.?%

The cells labelled Property Postcode, PurchasePrice, Deposit, Interest Rate and Term in Years are for data entry. The cell labelled Loan Amount should contain a formula that calculates the loan amount automatically. The formula in the MonthlyPayment cell must use the PMT function.

 

 

 

 
Table 2
Using the PMT function, and a combination of mixed and absolute cell references, enter formulas that calculate the monthly payments in the main body of Table 2. Use a layout similar to one below.

Term (in years)
5 7 9 11 13 15 17 19 21 23
Interest Rates 5.00%
5.25%
5.50%
5.75%
6.00%
6.25%
6.50%
6.75%
7.00%
7.25%

Table 2 must be linked to Table 1 by means of appropriate formulas. In other words, all values in Table 2 should automatically update when data is entered in Table 1, including the “Term in years” and the “Interest Rates.”

For example, if a user enters “5.0%” as the Interest Rate in Table 1, this value will appear in the shaded cell in Table 2 (see above). The interest rates in this column will increment by the amount entered in Interest Increments in Table 1 (.25% in the example above). Similarly if a user enters “5” as the Term in Years in Table 1, this value will appear in the other shaded cell in Table 2. The Term (in years) will increment by the amount specified in Term Increments in Table 1 (1 year increments in this example).

The owner wants the spreadsheet to be designed in such a way that anyone can use it to enter loan information for other major purchases. You decide to make the data entry process easier by creating a command button that runs a macro. The button should have the label “New Loan”, and should be positioned to the right of Table 1.

The macro, together with the PMT function, should perform the following:-
• Make the “Loan Analysis” sheet active
• Clear the data in the cells labelled Property Postcode, Purchase Price, Deposit and Interest Rate
• Clear the monthly payment figures in Table 2 and display “0” values with a currency symbol (as shown below)

 

• Present a series of input boxes that prompt the user to enter data into the cells labelled Property Postcode, Purchase Price, Deposit, Interest Rate and Terms in Years.
• Enable the user to enter the interest rate without needing to type a “%” symbol or a decimal fraction (e.g. allowing 4.5 to be entered for 4.5%)
• Move to the cell labelled Monthly Payment once data entry is complete
• Note: if the user does not enter data in the input boxes, Tables 1 and 2 should not return any errors (such as “#DIV/0!”).

Modify your formulas and/or your Visual Basic macro code in order to achieve the above actions.

Use appropriate formatting to create an attractive but usable spreadsheet. Save the workbook as LoanAnalysis.

Task 3: Bonus Awards
The Network Café has a scheme whereby customers are awarded bonuses based on their monthly Internet usage. Bonuses can be redeemed for more connection time or merchandise at the café. They are an incentive for customers to extend their stay at the café and spend more money.

Part A: Connection Time Bonus Scheme
A standard bonus is applied to every customer at a rate of 10p per minute of connection time. An additional super bonus is awarded at 2 different levels for those customers who stay connected for more than 100 minutes. The bonus scheme is explained in the table below.

Bonus Criteria Award
Standard Bonus Total monthly connection time is less than 100 minutes. Total connection time multiplied by 10p.
No Super Bonus.
Super Bonus, Level 1 Total monthly connection time is 100 minutes or more, but less than 200 minutes. Standard Bonus, plus an additional 5% of the standard bonus amount.
Super Bonus, Level 2 Total monthly connection time is 200 minutes or more. Standard Bonus, plus an additional 20% of the standard bonus amount.

For example, a customer who has 316 minutes of connection time for the month, would not only get the standard bonus of £31.60, they would also get the super bonus level 2 because their connection time is over 200 minutes. They would thus get an extra 20% of their standard bonus (i.e. an additional £6.32). Their total bonus would therefore be £37.92.

When customers log in to the computers at the NetworkCafe, they enter their names, and the system tracks the time between their signing-in and signing-out. Network Café staff then record the connection time.

Currently, the bonuses are calculated manually on paper. The owner wants to improve this system, and he would like you to create a form that his employees can use to record and calculate customers’ bonus awards. The bonus form needs to be designed to accept input of customer connection times and to calculate the bonuses they have earned.

The basic layout of the form should be similar to that below.Apply your own formatting to create an attractive but usable spreadsheet.

 
Enter the data for the Super Bonuses and the bonus rates in a suitable area of the worksheet.
Enter formulas to calculate all Totals and Standard Bonuses (using absolute cell referencing where appropriate). Use the IF function to calculate the Super Bonuses, using the bonus scheme and criteria outlined above.

Part B: Combo Box
The owner wants to be able to raise the bonus rate during some months from 10p to 20p or 30p per minute. The owner wants to make it easy for his employees to adjust the bonus rate in a way that ensures the validity and accuracy of the data entered.

You decide to add a combo box to the form, and to use data validation to enable one of the three bonus rates (10p, 20p or 30p) to be selected from a drop-down list.

 

Modify the formulas so that when a bonus rate is selected, customer bonuses are automatically recalculated.

Part C: Template
The owner wants you to save the form as a template so that it can used to record connection times of different customers in different months. You decide to lock certain cells on the worksheet so they cannot be changed, while allowing data entry in other cells. The cells to be locked include all cells containing formulas and all labels (except customer names and the month). All other cells should be unlocked (therefore allowing data entry)

Having locked and unlocked the appropriate cells, protect the worksheet, but do not use a password.
Select a bonus rate of 20p from the combo box, then enter the following data exactly as shown.

 

Save the workbook as an “Excel Template” using the name BonusAwards.

Task 4: PowerPoint presentation
The owner wants you to make a presentation to potential investors in the Network Café, and he would like you to prepare some PowerPoint slides that show sales forecasts and profit margins for 2009. The presentation should be saved as Investors. The owner wants you to include the following slides:-

Slide 1: Title slide, with an appropriate title (which must feature the Network Café name), a suitable logo, and the name of your group.

Slide 2: Brief introduction to the Network Café with 6 bullet points. Key facts you might want to highlight are as follows: The Network Café was founded and launched in September, 2008. It consists of 8 Internet cafes (4 in London, 2 in Birmingham, 2 in Manchester). The Network Café has 150 employees. Income is primarily from beverage sales (mainly coffee), Internet connections, food sales (mainly snacks and sandwiches) and merchandise. The Network Café is hoping to open another 6 locations in 2010.

Slide 3: An embedded line chart showing the projected increase in sales for the first half of 2010 (i.e. the 6 month period of January-June). The chart should have appropriate formatting and chart elements (titles, legend, X and Y axis values, etc). The chart must clearly show the sales trends for each item.

Include 2 explanatory bullet points beneath the chart.

Slide 4: An embedded pie chart created from the Second Quarter worksheet data showing the proportion of total quarterly sales for Beverages, Food, Internet and Merchandise in the Best Case Scenario. The pie chart should include suitable titles, labels and formatting.
Underneath the pie chart, paste the following data from the Scenario Summary report comparing monthly Profit Margins for Best and Worst Case scenarios.

 
Slide 5: Include the bar chart from Task 1, showing First Quarter profit margins. The chart must be linked to the Forecast worksheet. Add 2 two explanatory bullet points beneath the chart.
Note: the sample data above is for illustration purposes. You must use the correct data from Task 1.
Slide 6: Include the bar chart from Task 1, showing Second Quarter profit margins (from the Most Likely scenario). The chart must be linked to the Forecast worksheet. Add 2 two explanatory bullet points beneath the chart.

 

Note: the sample data above is for illustration purposes. You must use the correct data from Task 1.

Slide 7: Slide with the title Network Café: The way forward
The owner wants to present the case for more investment in the Network Café.
Create a slide, using no more than 6 bullet points, outlining your vision for the company, e.g. areas of business to be explored, future opportunities, areas of investment. Based on the data in the spreadsheets, and the information that you have been given, what should the company’s strategy be for the next five years?

Slide 8: Conclusion slide
________________________________________
Create the slides describe above using appropriate design templates, colour schemes, formatting and layout, transitions and animations, logos, and any other appropriate features. The presentation should conform to the principles of good presentation design outlined in the module.
Task 5: Word-Processed Report
The final task is to document and consolidate all the work you have done in Tasks 1-3 into a report. The report should be saved as Report and must include the following sections:-

Cover Page
The cover page should include:-
• Title of the module (BIS1212: Introduction to Business Computing)
• The name of your lab tutor
• Names and student IDs of all group members

Title Page
• An appropriate title
• The name of your fictional consultancy company
• The names of all your group members
• A suitable logo for your group

Table of Contents
• Automatically generated in Word.

Introduction
• Briefly explain the purpose of the report, and summarise its contents (half a page).

1. 2009 Forecast
• Include the worksheet for the 6 month forecast (from Task 1) displayed in landscape orientation.
The worksheet should be linked to the source data.
• Add half a page of text, explaining your design and formatting of the spreadsheet, and interpreting the worksheet data. What does that data show?
• Paste theFirst Quarter column and line charts (from Task 1) as linked Excel Chart objects. Add half a page of text, explaining each chart, what it shows and why you chose to design and format the chart this way.
• Include a screenshot of the Scenario summary report (from Task 1) with a full explanation of the 3 scenarios and what they show.

2. Loan Analysis
• Include screenshots of the underlying formulas for Table 1 and Table 2.
• Explain in full your design of the Loan Analysis worksheet. Explain also the user input, the purpose of the spreadsheet and how it works.
• Include the Visual Basic code for the command button.

3. Bonus Award Template
• Include screenshots of a) your bonus awards template, and b) the underlying formulas for the “Bonuses” section of the worksheet
• Include a full explanation of how the template works in terms of user input, and a justification of your design.

Conclusion
Summarise the work that you have done, then explain how these spreadsheets will benefit the Network Café. How might they help decision-making and enhance the development of this business? How do they fit into the overall goals of the company? [The conclusion must be at least ½ page in length].
Appendix
Completed self-evaluation forms by each member of the group, answering the following questions:-
Name: Student ID:
My contribution to the group coursework was as follows:-
Things that I did well:-
Areas where I could have done better:-
3 most important things I learnt from doing the coursework:

Report format and layout requirements
• The report should be in font size 12, with 1.5 line spacing
• Styles should be used for different heading levels
• Sections, and different page orientations should be used where appropriate
? The report must include a Header (with the names of your group members). The Footer area should include the name of your group on the left and page numbers on the right. The Header and Footer should be suppressed on the cover and title pages, and should begin after the Table of Contents.
• The report should be fully checked for grammatical and spelling mistakes.

Deadline for Submission of Coursework

May 4th, 2012, 4:00pm

How to submit your Coursework
Coursework is to be submitted online, in electronic form, via OASISPlus. You should keep an electronic copy of your coursework for your own records. Do not hand in or email assessed coursework to your lab tutor or to the module leader.

You need to submit 5 files, as follows
? Forecast.xls
? LoanAnalysis.xls
? BonusAwards.xlt
? Investors.ppt
? Report.doc

Files should be submitted by 1 MEMBER OF THE GROUP on behalf of the other members.
You only need to submit one copy of each file per group.

Uploading your files on OASISPlus
Upload the files by clicking on “Assignments” in the left-hand menu bar on OASISPlus

Click on “Group Coursework”

Click on “Add Attachments” (you can upload multiple files).

Enter the names and student IDs of all group members in the “Comments” text box below.

When you are sure you have completed all the above steps, and are ready to submit your coursework, click on the “Submit” button. [Important: You can only submit your files once.]

BIS1212: Group Coursework Marking Criteria

The Group coursework is marked out of 100, with 20 points awarded for each of the 5 tasks. The tables below show how the marks are broken down for each task.

Task 1 (2009Forecast) (20%)
Criteria Value
Correct formulas & functions, including net income, profit margin, use of 3D references where required, and correct values. 3
Formatting & design, including alignment, indentation, currency/number formats, shading, labels, titles, graphics, sheet names, conditional formatting. 3
Charts 1 & 2 – Correct values charted, chart formatting (including titles, legends, axis labels & values, text boxes to highlight data, etc) 3
Solver – Correctly implemented with solved values (e.g. Jan merchandise, quarterly profit margin) correct constraints and incluson of Solver Report. 5
Scenarios – Correctly implemented with correct results, working buttons,VBA code and inclusion of edited and formatted Scenario Summary Report 6
Total 20
Task 2 (Loan Analysis) (20%)
Formatting & design, including labels, layout, usability 3
PMT function – Correct use of PMT function in Tables 1 and 2 5
Linked Tables – Tables correctly implemented with formulas linking all cells in Table 2 to Table 1 4
Command button and VBA code – Correct input box functions and error-free procedures with tables 1 and 2 not returning errors (such as “#DIV/0!”). 8
Total 20
Task 3 (BonusAward) (20%)
Formatting & design, including use of colour, shading, labels, layout and usability 3
Formulas & functions – Correctly implementednested IF function and use of absolute references to return correct values for bonuses 6
Combo-box control – Correct implementation of combo-box linked to bonus formulas, correct use of data validation. 6
Template – Correct implementation of template, including locking/unlocking of correct cells, use of worksheet protection and file saved as Excel template. 5
Total 20
Task 4 (Investor.ppt) (20%)
Correct Slides
? Slide 1 (Title, logo, group name)
? Slide 2 (Introduction, bullet points)
? Slide 3 (Line chart with correct formatting, values, labels, titles) 2 bullet points
? Slide 4 (Pie chart, data from Scenario Summary Report)
? Slides 5-6 (Bar charts with 1st& 2nd Qtr profit margins, 2 bullet points)
? Slide 7 (The Way Forward, bullet points)
? Slide 8 (Conclusion slide) 10
Formatting & Design (including colour schemes, graphics & logos, font size transitions & animations, concise bullet points, principles of Presentation design) 7
Grammar and spelling 3
Total 20
Task 5 (report.doc) (20%)
Preliminary pages
? Cover page, title page, with report title, name of group, group members
? Table of contents (correctly formatted)
? Introduction, with purpose of report, and summary of contents 3
Format & layout
? Correct font size and line spacing, use of Styles for heading levels
? Use of sections and page orientation
? Correct header and footer.
? Grammar and spelling
? Document design and layout 4
Correct screenshots, charts and worksheet data, including:-
? 6 month forecast worksheet in landscape orientation
? Column and line charts from Task 1
? Screenshot of Scenario summary
? Formulas for Tables 1 and 2 (Task 2)
? Visual Basic code for command button.
? Screenshot of bonus awards template and formulas for bonuses (e.g. IF function) 4
Descriptions & explanations
? Full descriptions and justifications of all screenshots, charts and worksheet data
? Conclusion (with summary of work, and explanation of benefits to client). 5
Appendices
? Completed self-evaluation forms by each group member, including quality & evidence of individual contributions. 4
Total 20
Group Coursework Marksheet

Task 1 Task 2 Task 3 Task 4 Task 5 Total
BIS1212 COURSEWORK MARKING SHEET Formulas & Functions Formatting & Design Charts Solver Scenarios, Buttons, VBA Formatting PMT Function Tables Linked Macro Button Code Formatting & Design Formulas (IF function) Combo Box Control Template Correct Slides Formatting & Design Grammar & Spelling Prelim Pages Format and Layout Screenshots, Charts… Descriptions…. Appendices TOTAL
Name Student ID 3 3 3 5 6 3 5 4 8 3 6 6 5 10 7 3 3 4 4 5 4 100
0
ORDER THIS ESSAY HERE NOW AND GET A DISCOUNT !!!